All information is below. Can you please include formulas used? Thank you.

CVP Model and Budget DIRECTIONS: The purpose of this project is twofold: Part B. Preparation of sales and puchase budget: It will give you experience: Part A. CVP analysis: 1. Undertaking profitability analysis and understanding the link between the cost structure You are in the process of preparing budget for 2020 for El Paso Division. and profitability of a company. 1. Complete the green cells in the Assumption box in the Data File CVP worksheet by - Based on the forecasts of the Sales Department, the sales are expected to increase by 2. Preparing a sales budget and purchase budget for its merchandising division. linking the appropriate cells in the Projected Data Table in the Project Description 10 per cent in each quarter. worksheet. Your goal will be to use Excel in such a way that any changes to the assumptions will Do not hardcode in the Assumption box, or else, this spreadsheet cannot be used over According to the company's policy, the desired ending inventory is expected to be 20 correctly ripple through the entire profitability analysis and budget preparation. and over if projections changed. per cent of the budgeted sales of the current quarter. If executed properly, the company should be able to use this spreadsheet over and over, using different "what if" assumptions. 2. Prepare projected Contribution margin-based Income Statement for the fourth quarter Ending inventory at the end of the Fourth quarter of 2019 is 2,500 units. of 2019 for Denver and El Paso divisions based on the projected data transferred in the Description of the business: Assumption box. 1. Complete Budget assumption box in the Data file Budget. (See the comments.) Realm Inc. is specialized in selling pet toys. Currently, they are launching a new item - Use formula as needed. Do not hardcode. 2. Prepare a Sales budget and Purchase budget for El Paso Division (Use appropriate "strike and run" which throws ball every time a paddle in the toy is pressed. The company Check figure: Net Income should be $320,000 for both divisions. formula. Use Roundup function to calculate the number of units as a full unit in each has two divisions - one at Denver and the other at El Paso. row of the budget tables) The Denver division manufactures the toy in house and the El Paso division import the toy 3. Calculate the following for both divisions using appropriate formula: Check figures: from Mexico. - Variable costs per unit (Total budget sales: 102,102 units) The following projected data is provided for the Fourth quarter of 2019: -Contribution margin per unit (Total budgeted purchase: 105,459 units) - Break-even units Projected data Table: (Use Roundup formula in Excel to round up to full unit. The genral roundup formula for Denver El Paso break-even will be: =ROUNDUP(Total Fixed costs/Contribution margin per unit,0). Quarterly volume of units sold in the fourth quarter 2019 20,000 20,000 Check figures: Denver: 5,117 units; El Paso: 1,715 units Break-even sales Revenue per unit $25 $25 - Margin of safety in percentage Total Variable Costs of Goods Sold $50,000 $130,000 Operating leverage Total Fixed Costs of Goods Sold $80,000 $0 Total Variable Selling and Administration Expenses $20,000 $20,000 4. Undertake a sensitivity analysis assessing the impact of operating leverage on net Total Fixed Selling and Administration Expenses $30,000 $30,000 income based on the following two what-if scenarios for both the divisions. Use appropriate formula and format the cells as 'currency'): - Conservative scenario: What is the total yearly net income if sales decrease by 40%? Check figure: Denver division: $148,000 Optimistic scenario: What is the total yearly net income if sales increase by 40%? Check figure: El Paso division: $460,000 5. Assume that, you are in the position of management accountant in Realm Inc. and the management seeks an explanation from you regarding the disparity among the comparative profit data of the sensitivity analysis of the two divisions. How do you explain this difference? (Write your answer in the Explanation box.)ASSUMPTIONS Product: Strike and Run: Denver El Paso Revenue per unit Total Variable Costs of Goods Sold Total Fixed Costs of Goods Sold Total Variable Selling and Administration Expenses Total Fixed Selling and Administration Expenses Quarterly volume of units sold in the fourth quarter 2019 2 Projected Contribution Margin based Income Statement For Fourth quarter 2019 Denver San Antonio S S Answer check Sales Total Variable costs Contribution margin Total Fixed costs Net income 3 Variable Cost per unit Contribution Margin per unit Break Even units Break Even sales Margin of Safety percentage Operating Leverage |Sensitivity Analyis Conservative scenario: Sales decrease by 30% than the projected sales -40% Net Income under conservative scenario: Optimistic scenario: Sales increase by 30% than the projected sales 40% Net Income under optimistic scenario: Explanation:Budget Assumptions for El Paso Division Projected/Budgeted sales in unit for the Fourth Quarter of 2019 20,000' The percentage of sales increase from one quarter to next quarter 1096' Desired Ending Inventory at the end of each quarter is 20% of the Budgeted sales unit of the same quarter 20% Budgeted Ending inventory units for the Fourth quarter of 2019 2,500 |:1 Sales Budget for the 2020 lst quarter 2nd quarter 3rd quarter 4th quarter Total sale units Sales unit Answer check Purchase Budget for the 2020 lst quarter 2nd quarter 3rd quarter 4th quarter Total sales Budgeted Sales unit Plus Desired Ending Inventory Equal to Total need Less Beginning inventory Equal to Budgeted Purchase Answer check