You have been asked to prepare a 3-year budget forecast for the manufacturing of an organic lipstick.Royal Paints utilises a traditional manufacturing cost flow inventory and accounting system and has provided you with the following information for 2019.

2019 Year data

Sales (units)

750,000

Price (average 2015 price received)

$15.95

Prime Costs (per unit)

Ingredients & Packing

$4.50

Direct Labour

$3.50

Manufacturing Costs (per unit)

$4.00

Factory Management Salaries (per annum)

$250,000

Factory Plant & Equipment Depreciation (per annum)

$350,000

Sales and Marketing Costs (per annum)

$700,000

Finance Costs (per annum)

$888,000

Non-Factory Administration Costs (per annum)

$685,000

Inventory on Hand (at valuation):

Ingredients & Packaging (250,000 units)

$1,125,000

Finished Goods (175,000 units)

$2,790,000

Royal Paints maintains a target safety stock of raw materials inventory and finished goods inventory amounting to the equivalent of three(3) weeksof the current year's budgeted unitsales.At the end of the 2019 calendar year, there were 175,000 completed units of 'Organic Lipstick' in the warehouse as Finished Goods. There are enough raw materials on hand to manufacture a further 250,000 units of organic lipstick.

The Marketing Department at Royal Paint predicts that unit sales of the organic lipstick will continue to grow for the next 3 years at a rate of 5%, however after those 3 years sales will drop off significantly. The company is budgeting to achieve a year on year price increase of 3.5%. All other costs including direct labour, material costs, and other overhead and administration costs are expected to increase annually at the same rate of 3.5%. The company pays tax at the Australian corporate tax rate which is expected to hold at 30%.

Royal Paint recently had to modify an old Dulux paint factory to be able to manufacture organic lipstick. However, due to the expected growth in sales, the factory is nearing its practical manufacturing capacity of 825,000 lipsticks.

Required:

1. Using Excel develop a Sales, Production and Purchase budget, as well as a budgeted Schedule of Cost of Goods Manufactured, Schedule of Cost of Goods Sold, and an Income Statement for each of the 3 years in the budget period, commencingJanuary 1, 2020. (Advice on the form of these budgets is linked through the online topic modules and in the Interact Resources folder and is also available in the Appendix to Chapter 9 of the textbook).This budget must also take into account the manufacturing facility's practical capacity production constraint. Your spreadsheet must include a data section which enables inputs (such as the budgeted cost and sales increases, and the production limit) to be simply altered and 'what if' analysis to be undertaken. (Excel resources are provided on your Interact site to guide you on the use of the 'IF' formula which can be used for the budget production constraint).(15 marks)

Hint: All 3 years of each budget should be shown side by side (1 column per year) for ease of comparison by management. All of the budgets should be presented on one worksheet together, working down the page commencing with the Sales and then Production budgets.You should be able to drag the formula across for the whole of the budget if the first years are properly constructed with a data input section and using absolute referencing. This makes the process much quicker and easier. An Excel help file and video which deals with the formula required has been placed in the Resources folder on the Interact site to assist students (linked through topic module 3).

2. If sales continue to grow beyond the expected 3 years, the practical production capacity of 825,000 units will constrain Royal Paint in a couple of years. The CEO of Royal Paint has the option of investing in new plant equipment for $10 million dollars which will be ready to start manufacturing by 2021. The new equipment will increase production capacity to 1.1 million units per annum and be depreciated on a straight-line basis over its 15-year useful life.

Using the Excel model developed in part 1. calculate the impact on sales and profit if the option of upgrading the manufacturing facility is exercised and the practical production capacity of the factory is increased to 1.1 million (Include the additional factory depreciation expense as a manufacturing cost. Submit results as a separate worksheet).