Question 4 Budgeting (16 marks)

Lionel Ltd manufactures and sells Product X for $38 each. The sales forecast of this product for January, February and March and April 2021 is 12,000 units, 16,000 units, 22,000 units and 25,000 units respectively.

Each unit of Product X requires 0.5 kg of material and 2.5 labour hours to produce. The material is bought from a local supplier at $4 per kg. The expected labour rate is $8 per hour. Factory overhead cost is allocated based on $0.6 per direct labour dollar.

Estimated finished goods inventory and materials inventory as at 1 January 2021 is 800 units and 500 kg respectively. Estimated materials inventory as at 31 March 2021 is 631.25 kgs.

The desired ending material inventory is equal to 5% of next months production needs. The desired ending finished goods inventory is equal to 10% of next month's sales in units.

Selling and administrative expenses:

Fixed $18,000 per month

Variable $0.80 per unit sold

Required:

Prepare the following budgets for January, February and March 2021. All budgets should include a total column for the 3 months. Use Excel to present your answers incorporating formulae to compute the numbers and cell addressing to link the different budgets where appropriate. Submit your Excel soft copy for both requirements.

(a) Provide the Excel worksheet showing the following detailed budgets: (11 marks)

1. Sales Budget

2. Production Budget

3. Materials Purchases Budget

4. Cost of Goods Sold Budget

5. Budgeted Profit & Loss Statement

(b) Assume that top management is not satisfied with the initial budgeted net profit and has requested you to make new projections with the following changes:

• Increase selling price by $0.50 with the sales units unchanged.

• Decrease purchase price of materials by $0.20

• Decrease fixed selling & administrative expenses by $2,000 per month.

Generate the revised budgets and provide the new Excel worksheet. This worksheet can be on another tab of the same Excel file. Explain which budgets will be affected and provide the new total amount for each of the budgets affected. Would you recommend this new budget? Explain briefly.