Question

Projection Assumptions Starting first month volumes: Coffee Pastry 500 Units 200 Units Monthly sales growth: Coffee Pastry 10% Each moth 5% Each moth Sales prices: Coffee Pastry $ $ 3.00 Per unit 2.00 Per unit Cost of sales: Coffee Pastry 0.25 1.00 Per unit Per unit $ Other non-fixed expenses: Starting hours Month 1 Growth in monthly hours Hourly pay Paper/supplies 200 Hours 2% Each moth 8.50 Per hour 5% % of total sales value $ $ Fixed expenses: Rent/utilities Insurance Advertising Accounting/tax 1,000 per month 200 per month 200 Per month 100 per month $ $ Using Goal Seek determine the selling price of coffee needed to break even for the year Type answer here

Answer 1

We shall Calculate details required to do Goal seek in following Tables:

Table 1	Total Sales Units
Row/Columns	A	B	C	D
1	Month	Coffee	Pastry	Formula used
2	1	500	200	Given Values
3	2	550	210	=(B2*1.10) and =(C2*1.05)
4	3	605	221	Drag values for rest rows
5	4	666	232
6	5	732	243
7	6	805	255
8	7	886	268
9	8	974	281
10	9	1072	295
11	10	1179	310
12	11	1297	326
13	12	1427	342
14	Total Sales Units	10692	3183	=sum(B2:B13) and =sum(C2:C13)

Table 2	Other Non Fixed Expense Hours
Row/Columns	A	B	C	D
1	Month	Hours		Formula used
2	1	200		Given Values
3	2	204		=(B2*1.02)
4	3	208		Drag values for rest rows
5	4	212
6	5	216
7	6	221
8	7	225
9	8	230
10	9	234
11	10	239
12	11	244
13	12	249
14	Total Hours	2682		=sum(B2:B13)

Table 3	Break Even Calculation (Before Goal Seek)
Row/Columns	A	B	C	D	E	F
1	Revenue
2
3	Sales	Units	Sales Price
4	Coffee	10692	3.00	32076	= B4*C4
5	Pastry	3183	2.00	6367
6		Total Revenue		38443	=D4+D5
7
8	Costs
9	Variable Cost
10	Coffee	10692	0.25	2673
11	Pastry	3183	1.00	3183
12
13
14	Non Fixed Expenses	2682	8.50	22801
15	Paper Supplies	5% of Sales Revenue		1922	=D6*0.05
16
17	Fixed Expense
18	Rent	1000	12.00	12000
19	Insurance	200	12.00	2400
20	Advertising	200	12.00	2400
21	Accounting	100	12.00	1200
22		Total Costs		48579
23
24	Break Even Point	(Sales - Cost)		-10136	=D6-D22

Now, We shall perform Goal seek in excel, for which we need to Given Value of Break Even Point to Excel and ask it to Change Value of Sales Price of Coffee in such a manner that value of break even point is Zero. You can see it's application here,

S Goal Seek Set cell: $D$24 To value: By changing cell: SC54|| | OK Cancel

Once Goal seek is completed following table will be available.

Table 3	Break Even Calculation (Before Goal Seek)
Row/Columns	A	B	C	D	E	F
1	Revenue
2
3	Sales	Units	Sales Price
4	Coffee	10692	4.00	42746	= B4*C4
5	Pastry	3183	2.00	6367
6		Total Revenue		49113	=D4+D5
7
8	Costs
9	Variable Cost
10	Coffee	10692	0.25	2673
11	Pastry	3183	1.00	3183
12
13
14	Non Fixed Expenses	2682	8.50	22801
15	Paper Supplies	5% of Sales Revenue		2456	=D6*0.05
16
17	Fixed Expense
18	Rent	1000	12.00	12000
19	Insurance	200	12.00	2400
20	Advertising	200	12.00	2400
21	Accounting	100	12.00	1200
22		Total Costs		49113
23
24	Break Even Point	(Sales - Cost)		0	=D6-D22

Hence, We can conclude that Sales Price of Coffee need to be revised to $4 per unit as described by Goal seek analysis.

Answer is $4.