Question

Topic:  Budgeting

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 month’s 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:

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.

Answer 1

Beacuse of change in the material price , the material purchases budget and cost of goods sold budget will change.

Because of change in the sale price the sales budget will change .

I would not recommend this new budget, since to increase the profit, sales price has been increased, while purchase price per kg has been reduced. The quality of the materials will decrease and also the sales price has increased. To increase the profit management should have reduced cost of goods sold by increasing efficiency of use of resources..or reduced labour cost by motivation and increasing their efficiency.