Question 1Manufacturing Budget(25 marks)

In the acquisition of Scenery Props, Worldwide Pictures will also gain ownership of their manufacturing plant that makes various figurines and paraphernalia to help promote the movie.Worldwide Pictures as part of their review would like you to draw up a 5-year budget to determine if the acquisition should go ahead.

Scenery Props manufacturing cost flow and accounting systems are prepared in a traditional manner.

On June, 30^th2020 Scenery Props provided the following information:

Units sales

500,000

Price per unit

$8.50

PRIME COSTS

Fabric and filling

$4.20

Labour

$1.70

Manufacturing overhead

$0.80

INVENTORY

$ UNITS

Fabric and filling

140,000 40,000

Finished goods

187,500 27,500

Marketing and sales costs

$865,300

Administration and management cost

$654,500

Scenery Props will become a division of Worldwide Pictures.Worldwide Pictures however want to change a few things with inventory management.They want to maintain a target safety stock of raw materials sufficient to supply four (4) weeks based on the current years budgeted production and finished goods inventory levels to be kept at two (2) weeks.At June 30, 2020, there was enough raw materials available to manufacture 40,000 units and 27,500 completed units in the finished goods warehouse.

Scenery Props has the practical capacity to generate 750,000 units per annum.Currently it produces enough to satisfy the market demand by operating at 70% of its practical production capacity.With the launch of the new movie, Worldwide Pictures expects that sales over the five years will increase by 12% pa. Consideration also needs to be made for price increases of 3% pa above the annual increasing inflation rate of 2.25% over the 5 years.Manufacturing costs of raw materials, labour and overhead are expected to increase at the same rate as the inflation rate.

Worldwide Pictures believes that there is an opportunity to increase production capacity to 900,000 units by leasing an additional production machine.Due to the size of the machine once an order is placed it takes one year before the machine can be commissioned.The lease of the machine is $100,000 per year and will be ready to be included in the production process on 30 June 2022.

Required:

1. Using Excel develop asales,production and purchases budgetas well as abudgeted schedule of Cost of Goods Manufactured, Schedule of Cost of goods soldand anIncome Statementfor each of the 5 years in the budget period (commencing 2021).The budget must take into consideration thepractical production capacityconstraint. The Excel spreadsheet must function from a data entry section which allows for "what if" analysis to be explored.

NOTE: The yearly budgets need to be presented side by side to allow for comparison, beginning with the sales budget and all the budgets must be presented on one worksheet.(10 marks)

2. Using a separate worksheet, build into your model what would happen to sales and production capacity if the additional machine were purchased.(5 marks)

3. Given the finding in part (i) and (ii) write a report to Worldwide Pictures management recommending what strategy the business should take over the next 5 years. The report needs to consider both financial and strategic implications. (max 400 words)(10 marks)