hello! please help me with this question, is based on Managerial Accounting.

Midas Manufacturing produces a variety of carbonated drinks for the domestic market. Non-carbonated drinks and their link to healthier living has been identified by management of Midas as one of the possible reasons for a loss of sales in recent years.

The management of the company is looking to introduce a fruit juice line to appeal to the health-conscious consumers. It is believed that revenue from sales of carbonated drinks will decrease by 10% in the next year, but management wants to increase profits during the remainder of the year. Management has asked the accounting team to include the budget value of the new opportunity to manufacture and sell the fruit juices in the next budget.

The most recent profit and loss statement, before incorporating the new product line, is given below:

Additional information for existing production:

A major distributor filed for bankruptcy which will cause sales and related variable costs of goods sold to decrease by 20%. The fixed manufacturing overhead will not be affected by this reduction in sales.

Management plans to increase the price of the existing product by 5% in the coming year.

The direct material is 30% of the sales revenue. There will be a 6% reduction in material cost due to removal of trade barriers.

Direct labour which is 30% of cost of goods sold will decrease by 10%.

The manufacturing overhead is 40% variable and 60% fixed. A new automated system will result in a 12% decrease in the variable manufacturing overheads due two efficiency improvements

Quality, testing and assurance is a key cost and will increase by 20% due to the additional production.

Marketing cost and interest expense will also increase by 12% and 6% respectively due to the new product line.

The operating expense will be reduced as follows:

Customer service cost by 2.5%

Distribution cost by 3.5%

Administrative cost by 4%.

The projected information for the new product line is as follows:

Budgeted sales units (cases)	3,000
Budgeted selling price per unit	$100
Average cost of fruits per case	$35
Labour cost per case of fruits	$22
Fixed manufacturing costs per batch	$3,400
Number of cases per batch	200
Miscellaneous shipping, brokerage and transportation costs	15%

REQUIRED:

Using Microsoft Excel and the format given in the question, prepare the budgeted profit and loss statement for Midas Manufacturing, considering the costing adjustments for the existing products and estimates for the new product line. (30 Marks)