Applying 3 instructions fiels in one Excel sheet

EXAM 1 DEVELOPMENT - PART D 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 Fil e from Exam Development – Part C . Sparklines 1) Create a new column to the right of the “5 -Year Projected” column and label it “Projected Trend”. As with the “5 -Year Projected” label, the cells J15:J16 should be merged and formatted properly to match other cells in the model. 2) Add a line Sparkline depicting the rev enue in row 20 from the current year through Year 5 . Set the Sparkline so that the individual points are shown on the line. 3) Create a Sparkline column chart to show the profit over the current year through Year 5. Show values that are below zero in red. Graph 1) Create a chart that shows the revenue from the current year through Year 5 in a column chart, with profit overlaid with a line chart. Make sure that year labels are appr opriate and a legend is visible in the midst of the chart as shown below. The titles should be two -layer, with the second line in a smaller font. The line should have a slight shadowing efect to offset it from the columns. While matching the coloring in t his graph is not critical , it should be close. There are very pale horizontal gridlines but not vertical gridlines in the chart, and there is a more prominent base line at zero. The scale on the left should match exactly, running from ($50,000) to $300, 000 in $50,000 increments. (If your zero shows as a dash, that is not a problem.) The profit line should be prominent enough to be clearly visibile. Some of these setting can be handled via the Style options, while some others will require that you explo re the knowledge presented in Lynda.com, such as how to change the vertical axis values. 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.