All information is below. Please include formulas used.

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