excel

Part 2 (40 Points): Excel Spreadsheet

Create a worksheet for Jake’s Gym that shows the total revenues for the second quarter of the year (April-June) for the North, South, and East regions. You must use functions and formulas in the spreadsheet where required or no credit will be given. The final output screenshot is on page 3.

Spreadsheet Step-by-Step Directions:

1. Type in the following headings and data in the worksheet.

2. Using a function, display the sum total revenue for each month and sum total revenue for each region. Compare your output to the screenshot below.

3. Using a function, display the average for each region. Compare your output to the screenshot below.

4. Write a formula to show the percentage increase or decrease between this quarter and the first quarter.

Look at the screenshot below as you read this explanation:

The “First Quarter Total” represents the revenue from January to March for each region. The second quarter is from April to June. You want to compare the revenue from the first quarter to this quarter. So for each region, subtract the revenue total from “First Quarter Total” from the “Total” for the second quarter (this quarter) and then divide that result by the first quarter total revenue to get the percentage change. Show the results as a percentage. Here is the formula for what was just explained for the “Increase/Decrease” Column cells:

(Second Quarter Total – First Quarter Total) / First Quarter Total

Here is a further explanation of the above formula using actual data. Looking at the screenshot below, for the North region, the “First Quarter Total” is 31,000. The “Total” for the second quarter is 49,650. So you will subtract the cell that contains 31,000 from the cell that contains 49,650 to get 18,650. Then divide that result by the cell that contains 31,000 to get 60.16%. This means that the second quarter revenue total has increased from the first quarter revenue total by 60.16%. Be sure to change the cell format for the final results in the cell to a percentage.

5. Create a 3D Clustered Column Chart to show the revenue totals for each month in each region. See the chart in the screenshot below.

6. Format the worksheet like the screenshot below. Be sure to merge the title and subtitle row cells. Edit the numeric data. Include appropriate cell styles for the titles, subtitle, heading columns, and the total row. You may use any background fill and font colors you wish.

FINAL OUTPUT SCREENSHOT

Your resulting worksheet and chart should look like the screenshot below. Remember that you must use functions and formulas where required or no credit will be given.

Jake’s Gym

Second Quarter Membership Sales

By Region

Page 1