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. 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. 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: Projected data Table: Quarterly volume of units sold in the fourth quarter 2019 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 Denver 20,000 $20 $45,000 $80,000 $20,000 $30,000 Fresno 22,500 $25 $130,000 $0 $25,000 $30,000 DIRECTIONS: Part A. CVP analysis: 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. 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 $225,000 for Denver Divisions. 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: El Paso: 1,657 units - Break-even sales - Margin of safety in percentage Operating leverage 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: $124,500 Optimistic scenario: What is the total yearly net income if sales increase by 40%? Check figure: El Paso division: $225,250 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 2. Projected Contribution Margin based Income Statement For Fourth quarter 2019 Denver San Antonio $ 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: 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 Sales Budget for the 2020 1st quarter 2nd quarter | 3rd quarter 4th quarter Tot Sales unit Answer check Purchase Budget for the 2020 1st quarter 2nd quarter |3rd quarter 4th quarter Tot Budgeted Sales unit Plus Desired Ending Inventory Equal to Total need Less Beginning inventory Equal to Budgeted Purchase Answer check