Applying 3 instructions fiels in one Excel sheet

EXAM 1 DEVELOPMENT - PART B BIA 3621 - Introduction to Business Analytics Lab

Scenario The first five exam development parts (and Exam 1) are concerned with the following situation:

Java Beans Coffee Shop operates 156 locations across certain regions of the United States. Although

currently running a deficit, the 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 is 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 management 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, calculate 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 difference 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 absolute 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 referencing as necessary. Copy 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 location (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) Gross 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 location (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 does 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 through Year 5.

5-Year Projected Total 1) We need a total column for the 5 year projections. Create 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 will 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.