Reference no: EM131373771
Assignment
You work for a lady who owns four country style eateries here in Gwinnett County. Sales for July have been estimated and she has decided to forecast monthly sales for each store through December. The monthly sales are expected to increase or decrease by the percentages listed below.
PART A: Put your name, today's date, and a title for your spreadsheet at the top of the worksheet. Display all data and assumptions given below in one area, or table, of your worksheet. :
|
Restaurant
|
July
|
Aug
|
Sept
|
Oct
|
Nov
|
Dec
|
|
Duluth
|
$777,666
|
1.4%
|
1.4%
|
1.4%
|
1.4%
|
1.4%
|
|
Lawrenceville
|
$444,222
|
3.2%
|
3.2%
|
3.2%
|
3.2%
|
3.2%
|
|
Lilburn
|
$678,321
|
2.5%
|
2.5%
|
2.5%
|
2.5%
|
-1.7%
|
|
Snellville
|
$765,567
|
2.8%
|
2.8%
|
2.8%
|
1.0%
|
-2.5%
|
Set up a second area, or table, in your worksheet to calculate the actual dollar amounts of sales projections for each of the months for each individual restaurant. Only formula and functions are to be in this area, or table, which refer to the data above so if any given value /number above is changed, these values will be re-calculated. July sales are already estimated but include them in your projections so the July figures will be reflected in your totals and averages. Sales projections for the remaining five months are to use the percentage increase or decrease for each store using the previous month's sales as the base figure. For example, when calculating the projected sales for August, the percentage increase or decrease is based on the July sales projection; September projections are based on August projections, etc. For each individual restaurant for August sales, the formula would be:
August Sales = July Sales +(July Sales* August Percentage Change)
Use appropriate function to calculate totals and averages for each restaurant.
Use appropriate function to calculate totals and average for each month for all restaurants.
Provide grand totals of all restaurants for the entire six-month period.
Provide an average of all restaurants' averages for the entire six-month period.
Align your column headings, adjust the column widths, and format all values to make a pleasing presentation. Use options such as fonts, bolding, italics, lines, etc. to further enhance your presentation.
PART B:
Create the following charts to graphically display the data in the worksheet:
1. A column chart that will allow you to compare projected sales amounts between each of the restaurants for each month (the months as the x-axis). Put an appropriate title on your chart and include your name in a footer.
2. A pie chart that compares the totals of the four restaurants. Put the actual values next to each slice of the pie and use a legend. Properly title your chart with two lines in the title.
Label your charts appropriately with chart titles and axis labels. You may display the charts on separate sheets. Make sure you label your worksheet tabs so their contents are obvious. For instance, just double-click on the Sheet 2 tab and type Column.
Send through the Dropbox tool:
1. Your worksheet displayed on a single sheet in landscape orientation.
2. Your charts.