When you carry figures that you have calculated in one output sheet forward to a subsequent output sheet, link these output sheets and do not link the subsequent output sheet again to the input sheet.
? Each budget (i.e. output sheet) must be in its own worksheet.
? Name each worksheet appropriately as follows: Budget number followed by a brief description or abbreviation, e.g. 1 Revenue, 3 DMP, and 5 OH.
? You may also use acronyms, e.g. Q1 (Quarter 1), Q2 (Quarter 2), etc.
? Your output sheets must only contain formulae and links to either the input sheet or previous output sheets if the figure was calculated in a previous budget. Do not type any figures in the cells of your output sheets.
? In essence, ensure you formulate the cells containing figures in all the output worksheets (budgets) in such a way that when you change a numeric value in the input sheet, then the calculations are done automatically in the output sheets.
? Use two (2) decimal places in calculations and show two decimal places in calculations where appropriate. You do not have to show decimal places when there are no cents in figures. For example, show the figure 0.08 with 2 decimal places but do not show the figure 1 as 1.00.
? High professional presentation standards that you will apply when presenting budgets to your employer in the workplace
Projected Sales |
||||
Product |
Q1 |
Q2 |
Q3 |
Q4 |
Azu |
6000 |
7000 |
8000 |
9000 |
Ranna |
5000 |
5500 |
6200 |
6800 |
Selling price per unit |
||||
Azu |
$ 125 |
$ 125 |
$ 135 |
$ 135 |
Ranna |
$ 160 |
$ 160 |
$ 170 |
$ 170 |
2018 Inventories in sales |
||||
Product |
1st Jan 2018 |
31st Dec 2018 |
||
Azu |
4000 |
5500 |
||
Ranna |
3000 |
4500 |
||
Uses of Direct Material |
||||
Amount used per unit |
||||
Direct Material |
Unit Usage |
Azu |
Ranna |
|
A |
KG |
4 |
5 |
|
B |
KG |
2 |
3 |
|
C |
Each |
0 |
1 |
|
Projected data of material |
||||
Direct Material |
Anticipated pur price |
Expected opening inventory |
Target closing inventory |
|
A |
$ 8.00 |
23000 |
25500 |
|
B |
$ 4.25 |
1900 |
1800 |
|
C |
$ 2.80 |
4000 |
4600 |
|
Direct manufacturing labour requirement |
||||
Product |
Hours per unit |
Rate per hour |
||
Azu |
2.4 |
$ 14.55 |
||
Ranna |
3.5 |
$ 16.75 |
Output sheet:
Sales budget:
Sales Budget |
|||||||
Product Azu |
Reference calculations |
Quarter 1 |
Quarter 2 |
Quarter 3 |
Quarter 4 |
Total |
Marks |
Sales |
6000 |
7000 |
8000 |
9000 |
21000 |
||
Selling price |
$ 125 |
$ 125 |
$ 135 |
$ 135 |
$ 385 |
||
Total sales for AZU |
a |
$ 750,000 |
$ 875,000 |
$ 1,080,000 |
$ 1,215,000 |
$ 2,705,000 |
|
Product Ranna |
|||||||
Sales |
5000 |
5500 |
6200 |
6800 |
16700 |
||
Selling price |
$ 160 |
$ 160 |
$ 170 |
$ 170 |
$ 490 |
||
Total sales for Ranna |
b |
$ 800,000 |
$ 880,000 |
$ 1,054,000 |
$ 1,156,000 |
$ 2,734,000 |
|
|
|||||||
Total sales for both products |
|
$ 1,550,000 |
$ 1,755,000 |
$ 2,134,000 |
$ 2,371,000 |
$ 5,439,000 |
|
Reference for calculations |
|||||||
a. 6000*125 = 7,50,000 |
|||||||
b. 5000* $160 = $ 8,00,000 |
|||||||
C. 7,50,000+8,00,000 = 15,50,000 |
Production budget:
Production Budget (in KG) |
|
|
|
|
|
|
Marks |
|
Reference calculations |
|
|
Product Azu |
|||
Sales units |
21,000 |
||
Less: Opening stock |
4,000 |
||
ADD: Closing stock |
5,500 |
||
Production units |
a |
22,500 |
|
|
|||
Product Ranna |
|||
Sales units |
16,700 |
||
Less: Opening stock |
3,000 |
||
ADD: Closing stock |
4,500 |
||
Production units |
b |
18,200 |
|
|
|||
Total Units |
c |
40,700 |
|
Reference for calculations |
|||
a. 21000-4000+5500 = 22,500 |
|||
b. 16700-3000+4500 = 18,200 |
|||
c. 22500+18200 = 40,700 |
Direct material purchase budget:
Direct Material purchase Budget (in KG) |
Marks |
||||
|
|
Direct Material |
|||
|
Reference calculations |
A |
B |
C |
|
Product Azu |
|||||
Production Units |
22,500 |
22,500 |
22,500 |
||
Material Required for each Unit |
4 |
2 |
- |
||
Total material required |
a |
90,000 |
45,000 |
- |
|
|
|||||
Product Ranna |
|||||
Production Units |
18,200 |
18,200 |
18,200 |
||
Material Required for each Unit |
5 |
3 |
1 |
||
Total material required |
b |
91,000 |
54,600 |
18,200 |
|
|
|||||
Total material for both products |
C |
181,000 |
99,600 |
18,200 |
|
|
|||||
Add: Closing stock |
25,500 |
1,800 |
4,600 |
||
Less: Opening stock |
23,000 |
1,900 |
4,000 |
||
Total Material purchase |
D |
183,500 |
99,500 |
18,800 |
|
Reference for calculations |
|||||
a. 22500*4 = 90,000 |
|||||
b. 18200*5 = 91,000 |
|||||
c. 90000+91000 = 1,81,000 |
|||||
d. 181000+25500-23000 = 1,83,500 |
Direct material purchase budget:
Direct Material purchase Budget (in dollar) |
Marks |
||||||||||
|
|
Direct Material |
Direct Material |
Direct Material |
|||||||
|
|
Anu |
Anu |
Total |
|||||||
|
Reference calculations |
A |
B |
C |
A |
B |
C |
A |
B |
C |
|
|
|||||||||||
Material purchase |
90,000 |
45,000 |
- |
91,000 |
54,600 |
18,200 |
|||||
Material price |
$ 8.00 |
$ 4.25 |
$ 2.80 |
$ 8.00 |
$ 4.25 |
$ 2.80 |
|||||
Total material purcase amount ($) |
a |
$ 720,000 |
$ 191,250 |
$ - |
$ 728,000 |
$ 232,050 |
$ 50,960 |
$ 1,448,000 |
$ 423,300 |
$ 50,960 |
|
Reference for calculations |
|||||||||||
a. 90000* 8 = $ 7,20,000 |
Direct manufacturing labour budget:
Direct manufacturing labour budget Budget (in dollar) |
Marks |
||
|
|
|
|
|
Reference calculations |
|
|
Product Azu |
|||
Production Units |
22,500 |
||
Labour hour required for each unit |
a |
2.4 |
|
Total labour hour |
54,000 |
||
Labour rate per hour |
$ 14.55 |
||
Total labour cost |
b |
$ 785,700 |
|
|
|||
Product Ranna |
|||
Production Units |
18,200 |
||
Labour hour required for each unit |
3.5 |
||
Total labour hour |
63,700 |
||
Labour rate per hour |
$ 16.75 |
||
Total labour cost |
$ 1,066,975 |
||
|
|||
Total labour hour for both products |
c |
$ 1,852,675 |
|
Reference for calculations |
|||
a. 22500*2.4 = 54,000 |
|||
b. 54000 * 14.55 = 7,85,700 |
|||
c. 785700 + 1066975 = 18,52,675 |
Budgeted finished goods inventory:
Budgted finished goods (in KG) |
|
|
Marks |
|
|
Direct Material |
|
|
Reference calculations |
A |
|
Product Azu |
|||
Closing inventory (units) |
5,500 |
||
Direct Material |
a |
$ 222,750 |
|
Direct Labour |
b |
$ 192,060 |
|
Value of closing stock unit |
a |
$ 414,810 |
|
|
|||
Product Ranna |
|||
Closing inventory (units) |
4,500 |
||
Direct Material |
$ 249,975 |
||
Direct Labour |
$ 263,813 |
||
Value of closing stock unit |
b |
$ 513,788 |
|
Value of closing stock unit |
|
||
Total closing inventory price |
c |
$ 928,598 |
|
Reference for calculations |
|||
a. (5500*4*8)+(5500*2*4.25) = 2,22,750 |
|||
b. 5500*2.4*14.5 = 1,92,060 |
To export a reference to this article please select a referencing stye below:
My Assignment Help. (2021). Projected Sales, Inventories In Sales, Direct Material, Direct Manufacturing And Sales Budget. Retrieved from https://myassignmenthelp.com/free-samples/acct19060-management-accounting/hour.html.
"Projected Sales, Inventories In Sales, Direct Material, Direct Manufacturing And Sales Budget." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/acct19060-management-accounting/hour.html.
My Assignment Help (2021) Projected Sales, Inventories In Sales, Direct Material, Direct Manufacturing And Sales Budget [Online]. Available from: https://myassignmenthelp.com/free-samples/acct19060-management-accounting/hour.html
[Accessed 26 April 2024].
My Assignment Help. 'Projected Sales, Inventories In Sales, Direct Material, Direct Manufacturing And Sales Budget' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/acct19060-management-accounting/hour.html> accessed 26 April 2024.
My Assignment Help. Projected Sales, Inventories In Sales, Direct Material, Direct Manufacturing And Sales Budget [Internet]. My Assignment Help. 2021 [cited 26 April 2024]. Available from: https://myassignmenthelp.com/free-samples/acct19060-management-accounting/hour.html.