Weekly Invoice Problem

**aboo** · 06-17-2011, 06:59 PM

Hi All,

Please can you help me on some ideas regarding a spreadsheet i need to create. Im pretty new to Excel so please bare with me.

I have a small business and keep written records, calculating and invoicing customers manually. I have started to use excel and wish to automate my invoices.

I record daily amounts in columns and rows like below. (note the vlaues are in Kg)

Monday
bananas apples oranges mangoes kiwi
Peter 10 7 9 6 1
John 5 7 6 4 7
Scott 6 5 7 3 2
kidd 7 2 6 2 3
holland 3 2 7 8 9

Then i use a price list to calculate the amounts by multiplying the weight amount against the price list below.

price list
bananas 1.59
apples 1.2
oranges 0.95
mangoes 2
kiwi 3

I want to know the best way to record these daily amounts and i want to generate an invoice at the end of the week and hold a record of 52 weeks in a work book.

Maybe in the end look at the best time of year that apples sell most or which month banans are best sellers.

Please can someone give me some ideas or point me in the right direction.

Thanks for your time.

Aboo

**Colin Legg** · 06-17-2011, 07:16 PM

Hi Aboo and welcome to the forum.

Im pretty new to Excel so please bare with me.

An unfortunate typo there...

Please can someone give me some ideas or point me in the right direction.

Sure, I'll tell you how I'd do it: using a pivot table.

I would change the layout of the data so that a column contains the date that each person bought the fruit, like this:

Date	    	Person	Bananas	Apples	Oranges	Mangoes	Kiwi
18/06/2011   	Peter	3	1	10	3	3
18/06/2011   	James	9	10	1	5	6
18/06/2011   	Paul	0	10	7	9	4
18/06/2011   	Simon	6	7	3	5	3
18/06/2011   	Ben	4	5	2	10	8
19/06/2011   	Josh	5	1	4	4	7
20/06/2011  	Mick	9	3	3	10	7
20/06/2011  	Peter	1	4	4	10	3
20/06/2011  	James	5	5	3	3	6
20/06/2011  	Paul	2	7	0	1	9
20/06/2011  	Simon	1	8	10	6	2
21/06/2011  	Ben	1	0	5	6	2
21/06/2011  	Josh	7	2	6	1	10
21/06/2011  	Mick	2	4	10	9	10
21/06/2011  	Peter	3	7	1	5	7
24/06/2011  	James	1	10	4	7	7
25/06/2011  	Paul	4	3	5	10	3
26/06/2011  	Simon	9	7	3	9	1
27/06/2011  	Ben	0	8	2	7	3
28/06/2011  	Josh	10	8	1	7	3
28/06/2011  	Mick	4	9	10	0	4
28/06/2011  	Peter	7	3	4	6	9
28/06/2011  	James	6	3	5	4	0
28/06/2011  	Paul	6	4	3	4	3
28/06/2011  	Simon	0	0	10	7	1
28/06/2011  	Ben	10	0	1	6	2
etc...

You just keep adding to the list as the weeks pass by.

I would then create a pivot table using this as a data source.

In the row fields I would put in the Date and then the Person.
In the column fields I would then put in each of the fruits and set each of the fields to return the sum (ie. the quantity).
On the Date row field, I would right click > group > and then I would set the Starting Date as the beginning of the earlist week and group by Days with a Day count of 7. This will group all of the numbers into weeks.

To include the weekly invoice amounts you could either create calculated fields within the pivot table or, for more flexibility, you could add additional columns to your table which calculate the invoice amounts and include those columns within your pivot table's source range.

Hope that gives you some ideas.

**aboo** · 06-18-2011, 06:20 AM

Hi Colin,

Thanks for that brilliant idea.

Originally Posted by Colin Legg

Hi Aboo and welcome to the forum.

An unfortunate typo there...

It was a MAJOR typo!

I have created pivot tables before and I dont know why i didnt think of it .

Another question regarding the same table is where would i put the cost for say peters bananas,apples, etc? would it be in the same row?

Also i have negotiated different prices for apples with josh so i take it that if i created a price table to run some kind of look up i should create seperate price lists for each customer. Hope i make sense. please could you throw soem more ideas back at me.

Thanks for making mefeel welcome and thanks for your reply.

Regards

Aboo