Question

1. The following information relates to a firm for preparing a flexible budget:

Column	I	II
Details	Amount ($)	Degree %
Repairs	1,300	60
Stores	1,000	100
Materials	16,179	100
Depreciation	5,000	nil
Wages	19,000	80
Advertising	2,349	75
Administration	1,450	25
Selling and distribution	3,000	70
Office Expenses	1,500	45

The amount for expenses is for production of 10,000 units. The amount changes with an increase or decrease of 20% above or below 10,000 units as indicated by the degree in column II above.

Required: show the budget statement

Answer 1

	Units			Degree of change
Expenses	8000	10000	12000	%
Repairs	1144	1300	1456	60
Stores	800	1000	1200	100
Materials	12943.2	16179	19414.8	100
Depreciation	5000	5000	5000	0
Wages	15960	19000	22040	80
Advertising	1996.65	2349	2701.35	75
Administration	1377.5	1450	1522.5	25
Selling and distribution	2580	3000	3420	70
Office Expenses	1365	1500	1635	45
Total Expenses	43166.35	50778	58389.65

Calculation Formulas in Excel :

	Units			Degree of change
Expenses	=C2*(1-20%)	10000	=C2*(1+20%)	%
Repairs	=C3-(E3%*(C3*($C$2-$B$2)/$C$2))	1300	=C3+(E3%*(C3*($D$2-$C$2)/$C$2))	60
Stores	=C4-(E4%*(C4*($C$2-$B$2)/$C$2))	1000	=C4+(E4%*(C4*($D$2-$C$2)/$C$2))	100
Materials	=C5-(E5%*(C5*($C$2-$B$2)/$C$2))	16179	=C5+(E5%*(C5*($D$2-$C$2)/$C$2))	100
Depreciation	=C6-(E6%*(C6*($C$2-$B$2)/$C$2))	5000	=C6+(E6%*(C6*($D$2-$C$2)/$C$2))	0
Wages	=C7-(E7%*(C7*($C$2-$B$2)/$C$2))	19000	=C7+(E7%*(C7*($D$2-$C$2)/$C$2))	80
Advertising	=C8-(E8%*(C8*($C$2-$B$2)/$C$2))	2349	=C8+(E8%*(C8*($D$2-$C$2)/$C$2))	75
Administration	=C9-(E9%*(C9*($C$2-$B$2)/$C$2))	1450	=C9+(E9%*(C9*($D$2-$C$2)/$C$2))	25
Selling and distribution	=C10-(E10%*(C10*($C$2-$B$2)/$C$2))	3000	=C10+(E10%*(C10*($D$2-$C$2)/$C$2))	70
Office Expenses	=C11-(E11%*(C11*($C$2-$B$2)/$C$2))	1500	=C11+(E11%*(C11*($D$2-$C$2)/$C$2))	45
Total Expenses	=SUM(B3:B12)	=SUM(C3:C12)	=SUM(D3:D12)