Part A
You are a junior management accountant in a manufacturing company. The senior management accountant has prepared a sales forecast for the year ending 31 December 2016. As an assistant to the senior management accountant, you have been asked to prepare the master budget for the year ending 31 December 2016 by using spreadsheet modelling based on the sales forecast, company policy, assumptions and other relevant data. The budgets MUST be completed on a spreadsheet using the capabilities of the spreadsheet in good form – in other words, use formulae wherever possible and not absolute numbers, and make sure that the information is comprehensible to the reader – guide your reader around the spread sheet. A spreadsheet packed with numbers and no guidance is difficult to follow.
You are strongly advised to study the concepts and applications of budgets before you begin working on this assignment. The sales forecast, company policy, assumptions and other relevant data are given in a spreadsheet in the assignment folder.
Required:
You are asked to prepare the following budgets for the year ended 31 December 2016:
I. Sales budget
II. Expected cash collection
III. Production budget
IV. Direct material cost budget
V. Expected cash disbursement
VI. Direct labour cost budget
VII. Manufacturing overhead budget
VIII. Ending finished inventory budget
IX. Selling and administration cost budget
X. Cash budget
1.
|
December 2015
|
40,000
|
Units
|
|
January 2016
|
25,000
|
Units
|
|
February 2016
|
30,000
|
Units
|
|
March 2016
|
50,000
|
Units
|
|
April 2016
|
30,000
|
Units
|
|
May 2016
|
25,000
|
Units
|
|
June 2016
|
15,000
|
Units
|
|
July 2016
|
30,000
|
Units
|
|
August 2016
|
25,000
|
Units
|
|
September 2016
|
15,000
|
Units
|
|
October 2016
|
30,000
|
Units
|
|
November 2016
|
25,000
|
Units
|
|
December 2016
|
15,000
|
Units
|
|
January 2017
|
25,000
|
Units
|
|
February 2017
|
30,000
|
Units
|
2. Selling price: $25 per unit
3. Company's collection pattern: 70% Collected in the month of sale, 30% Collected in the month following sale, and
4. Company policy on ending finished goods inventory: 20% of the following month's budgeted sales in units
5. Raw materials required for production: 5 kgs per unit of product
6. Company policy on ending raw materials inventory: 10% of the following month's material requirement
7. Raw material cost: $0.40 per kg
8. Company policy on payment of purchase: 50% in the month of purchase 50% in the following month
9. Direct labour requirement for production: 0.05 hours per unit
10. Direct labour rate: $10 per hour
11. Minimum hours paid per month: 1,500 hours
12. Variable manufacturing overhead rate: $20 per direct labour hour
13. Total fixed manufactruing overhead: $50,000 per month
14. Non-cash fixed manufacturing overhead: $20,000 per month
15. Variable selling and administrative expense: $0.50 per unit sold
16. Total fixed selling and administrative expense: $70,000 per month
17. Non-cash fixed selling and administrative expense $10,000 per month
18. Interest rate on borrowings: 16%
19. Cash dividend to be paid in July 2016: $45,000
20. Purchases of fixed assets to be paind in cash in the month of purchase: $150,000 March $80,000 June
21. Cash balance as at 1st January 2016 $45,000
22. All sales are on account.
23. Manufacturing overhead is appled to units of product on the basis of direct labour hours.
24. The company maintains a 16% open line of credit for $75,000.
25. The company wants to maintain a minimum cash balance of $30,000 at the end of any month.
26. The company borrows on the first day of the month and repays loans on the last of the month.
Part B
The preparation of budgets is a lengthy process, which requires great care if the ultimate master budget is to be useful for the purpose of management control within an organisation.
You are required to:
1. Identify and explain briefly the stages involved in the preparation of budgets clearly describing the roles of the managers and budget committees.
2. Explain how the use of spreadsheets may improve the efficiency and accuracy of the budget preparation process.