Applying 3 instructions fiels in one Excel sheet

EXAM 1 DEVELOPMENT - PART C 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 cl ear 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 B.

Titles 1)Clear A1, the cell with the Java Bean Coffee Shop company title. 2) Insert 3 rows at the top of the spreadsheet. 4) Change the font in cell A5 (contains “Sales, Revenue, and Income Projections”) to size 14.5) Change the font in A6 to italicized and size 9 font. Change the color to White, Background 1, Darker 50% 6) Format the “Parameter” and Profit Model Calculations” titles to be bold, italicized, and size 11. 3) Insert the Java Beans Coffee Shop logo from the Exam 1 Development - Part C - Java Beans Coffee Shop Logo file into cell A1. Adjust the size if necessary to fit in the first four rows. 7) Add a shading legend starting in cell A30 with a single entry for Input Cells. Shade the entry appropriately using the Excel style for input cells. Parameters 1)Shade the parameters as input cells. 2) Format the location growth rate as a number with zero digits to the right of the decimal (use Comma format). 3) Format the “Same Store Annual Sales Growth” and “COGS as Percent of Revenue” parameters as a percentage with one digit to the right of the decimal. 4) Format the “Fixed Cost per Location” using a n Accounting format with no digits to the right of the decimal. Profit Model Formatting 1)Merge and center the “Projected” header in cell D15 across D15:H15. Bold and border it on all four sides. 2) Merge and center the header “5 -Year Projected” in cell I15 across I15:I16. Bold the font and give it a bottom border. You will need to wrap the text to make it appear on both rows. 3) Bold all headers in cells C16:H16 and give them a bottom border. 4) Shade cells C17, C20:C21, and C24:C25 as input cells. 5) Rows 17, 21, and 25 should have a Comma format with zero decimal places to the right. 6) Rows 18, 20, 22, 24, 26, and 28 should have an Accounting format with zero decimal places to the right. 7) Put a bottom border in row 21 and row 25. Row 28 should have a top and double bottom border. 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.