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. 

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.