Please show excel formulas used.

Information given:

Question:

Part B. Preparation of sales and puchase budget: You are in the process of preparing budget for 2012 for Arizona Division. - Based on the forecasts of the Sales Department, the sales are expected to increase by 10 per cent in each quarter. - 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. Required: 1. Complete Budget assumption box in the Part B_Data file Budget. (See the comments.) 2. Prepare a Sales budget and Purchase budget for Arizona Division for 2012. (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 units: 255,255 units) (Total needs for inventory in 2012: 269,896 units) CVP Model and Budget The purpose of this project is twofold: It will give you experience of: 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: Fitness First Inc. is specialized in selling sports drink in boxes to major retailers. Currently, they are launching a new organic drink rich in essential electrolytes with minimal preservative. The Colorado division of the company manufactures the drink in-house and Arizona division custom orders the drink from a Mexican manufacturer. The following projected data is provided for the Fourth quarter of 2041:: Projected data Table: $70 Quarterly volume of units sold in the fourth quarter 20Y1 Selling price per unit Total Variable costs of Goods Sold Tot Fixed Costs of Goods Sold Total Variable Selling and Administration Expenses Total Fixed Selling and Administration Expenses Colorado Arizona 50,000 50,000 $70 $700,000 $1,900,000 $1,200,000 $0 $200,000 $100,000 $400,000 $500,000 Siddique, Salina: Do not hard code. Link the appropriate cell in Project Description worksheet to get the data. 10% Budget Assumptions for Arizona Division Projected/Budgeted sales in unit for the Fourth Quarter of 2041 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 20Y1 Siddique, Salina: Complete Cell B3, B4 and B6 from the data provided in the Part B in the Directions box in Project Description worksheet. These three are the only cells you are supposed to hard code. Sales Budget for the 20Y2 1st quarter 2nd quarter 3rd quarter 4th quarter Total sale units Sales unit Answer check Purchase Budget for the 20Y2 1st quarter 2nd quarter 3rd quarter 4th quarter 20Y2 Amounts Answer check Budgeted Sales unit - Plus Desired Ending Inventory Equal to Total need Less Beginning inventory Equal to Budgeted Purchase Answer check