unction 71 1 B31 fx A 1 2 3 4 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 cast 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 2011: Projected data Table: 6 7. 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Quarterly volume of units sold in the fourth quarter 2011 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 El Paso 20,000 $35 $60.000 $70,000 $40,000 $30,000 20,000 $35 $130 000 so $50.000 $20,000 42 43 44 45 46 47 48 49 50 51