Applying 3 instructions fiels in one Excel sheet

EXAM 1 DEVELOPMENT - PART B BIA 362 1 - Introduction to Business Analytics Lab Scenario The first five exam development part s (and Exam 1) are concerned with the following situation: Java Beans Coffee Shop operates 15 6 locations across certain regions of the United States. Although currently running a deficit, t he company has experienced solid growth over the past few years and is considering expanding operations. Management believes that they can grow the company by approximately 48 stores a year without compromising quality or distribution. To determine the effect of expansion, management has asked you to project future profitability over the next five years if recent performance remains constant while the company i s expanding. In the past year, the company earned $85,644,000 in revenue and has been experiencing sustained same store growth of 4.5% annually. The cost of goods sold at the locations has recently averaged approximately 48% of the revenue earned as is clear from this past year’s COGS (Cost of Goods Sold) of $41,115,000. Corporate figures show that the fixed costs of operating the 156 stores was $33,540,000 (an average of $215,000 per store per year ). The cost of the main company office and corporate man agement was $24,450,000 last year ; the company intends to do its best to keep that figure stable over the next five years. With this information, project the annual profitability of the company over the next five years along with supporting graphs. Final Result of this Activity When you have completed this part of the exam development , your Excel spreadsheet should look very similar to the following pic : Instructions Preparation 1) Open Your File from Exam Development – Part A . 2) Remove the gridlines from your model Current Year 1) In cell C15, c alculate the average sales per location for the current year. Total sales is available in cell C17 and the number of locations is in cell C14. 2) Calculate the gross margin for the current year. Gross margin is the di fference between revenue for the year (cell C17) and the cost of goods sold (cell C18). 3) Determine the total fixed costs for the current year by aggregating the location and corporate fixed costs in cells C21:C22. Use Excel’s SUM function. Locations and Average Sales 1) Each year, the company plans on expanding the number of locations by the Location Growth Rate parameter in cell C6. Create a formula in cell D14 that adds that number of locations to the prior year’s locations. Your formula must use ab solute referencing as necessary and be copied across through Year 5. 2) Since the company has projected growth in same store sales, the average location sales in cell C15 must be increased by the percentage in the parameter cell C7 (Same Store Annual Sales Growth) in each subsequent year. Think of it this way … if a year’s average store sales was 100 and a 10% increase was assumed , the projected average sales per location in the next year would be 110. In the subsequent year it would be 121 (110 + 10%) , 133 .1 (121+10%), and so on. Ultimately , you need a formula that increases a previous year’s sales by the percentage in cell C7. Create a formula in cell D15 that is copy -able using absolute refe rencing as necessary . C opy your formula across through Year 5. (Note: There is more than one way to do this mathematically.) Revenue and Gross Margin 1) Although the current year’s revenue is known, we will have to calculate subsequent years using the number of locations (row 14) and projected sales per locati on (row 15). Obviously, the revenue calculation uses the product of the two rows. Enter a formula in cell D17 for that year’s revenue and copy it across through year 5. 2) Cost of Goods Sold (row 18) is based on a percentage of revenue for that year . For years 1 through 5, we need a formula that takes that year’s revenue and multiplies it by the COGS as Percent of Revenue parameters in cell C8. Create a formula in cell D18 using absolute referencing as necessary and copy it across through Year 5. 3) Gros s Margin is simply the difference between revenue and cost of goods sold. Enter a formula in cell D19 and copy it across. Fixed Costs and Profit 1) The fixed costs for all of the locations (row 21) is based on the product of the expected fixed cost per loc ation (D9) and the number of locations (row 14). Create a copy -able formula in cell D21 using absolute referencing as necessary, and copy it across through Year 5. 2) Corporate fixed costs are expected to remain constant over the 5 projected years. That do es not mean that the number itself is typed or copied into cells D22:H22. Create a formula that references the corporate fixed costs in cell C22 and copy it across. Watch your referencing here. You always want to reference the original parameter in cell C22 – think absolute reference . (I know C22 is not in the parameter section, but it is not uncommon to have some parameters show up directly in the model to improve model flow .) 3) Total fixed costs is simply the sum of the location and corporate fixed costs for each year. Populate cell D23 with an appropriate formula and copy it across. 4) The profit for any year is the difference between the gross margin and the total fixed costs. Create a calculation in cell D25 and copy it t hrough Year 5. 5-Year Projected Total 1) We need a total column for the 5 year projections. Cr eate a title in cell I12 for the “5 -Year Projected Total”. 2) All of the values in this column are the totals for the Year 1 through Year 5 projections. They do not include the current year. Create a formula in cell I17 that totals the revenue for Year 1 through Year 5. Copy that formula so it can be used in rows 18, 19, 21, 22, 23, and 25. Save your Work You wil l be developing this model further in the future. Save a copy in a safe place. You are not required to submit this work for credit.