Illustrated Excel 365 | Module 4: SAM Project B Fitness Bands Inc.

GETTING STARTED

1. Save the file IL_EX365_4B_FirstLastName_1.xlsx as IL_EX365_4B_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file IL_EX365_4B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Jason Goetz is a financial analyst for Fitness Bands Inc., a manufacturer of smart watches in Seattle, Washington. He has been tracking the company's gross profit and loss in an Excel workbook, which includes charts to help him visualize the data. He has asked you to help him format the charts. Go to the Annual Revenue worksheet. The clustered column chart shows the combined sales revenue the company earned from five models of smart watches during the past year. Add Annual Revenue by Quarter as the chart title to identify the chart's contents.

2. Make the chart values easier to interpret as follows:

a. Remove the Primary Minor Horizontal gridlines.

b. Add Primary Major Horizontal gridlines to the chart.

3. Clarify the purpose of the horizontal axis as follows:

a. Add a primary vertical axis title to the chart.

b. Enter the text Revenue $ as the title of the primary vertical axis.

c. Change the font of the primary horizontal axis title to Arial Black.

d. Change the font size to 14 point.

4. Jason asks you to make it easier to interpret the values associated with the data points in the clustered column chart. Provide this information as follows:

a. Add a data table with legend keys to the chart.

b. Remove the legend since the data table provides the same information.

5. Go to the Cost of Sales worksheet. The doughnut chart on this worksheet shows how the cost of sales for each smart watch model affected the total cost of sales in the past year. Apply the Style 3 chart style to the doughnut chart.

6. Change the text direction of the data labels to Horizontal to make the labels easier to read.

7. Jason wants you to emphasize that the Fit Fanatic model has the highest cost of sales at 36%. Explode the largest slice of the pie (representing the Fit Fanatic model) by 15%.

8. Go to the Gross Profit worksheet. Jason wants you to compare the changes in revenue for the five models of smart watches in each quarter. Provide this information as follows:

a. In cell G10, insert a Line sparkline that plots the quarterly revenue for the Basic Trainer model (the range B10:E10).

b. Change the sparkline color to Lavender, Accent 6 (10th column, 1st row in the Theme Colors palette) to coordinate with the other colors on the worksheet.

c. Show markers on the sparkline to emphasize the values represented.

d. Use the Fill Handle to fill the range G11:G15 with the sparkline settings in cell G10.

9. The line chart in this worksheet compares the quarterly cost of sales for each smart watch model. Jason finds the chart difficult to interpret and wants to focus on the data for the fourth quarter. Change the chart type to a Clustered bar chart that uses the smart watch models on the vertical axis to more clearly compare the quarterly cost of sales for each of the five models.

10. Resize and reposition the clustered bar chart so that its upper-left corner is within cell I3, and its lower-right corner is within cell T21.

11. Change the fill color of the data series representing Qtr 3 to Light Gray, Background 2, Darker 25% (3rd column, 3rd row of the Theme Colors palette) to highlight the data series.

12. Show the trend of the cost of sales in the fourth quarter as follows:

a. Add a Linear Trendline to the chart for Qtr 3.

b. Change the outline color of the trendline to Light Gray, Background 2, Darker 25% (3rd column, 3rd row of the Theme Colors palette) to coordinate with the color of the Qtr 3 data series.

13. Jason also wants you to create a chart comparing the third quarter revenue from each smart watch model to the total third quarter revenue to determine which model had the most sales in that period. Create a new chart as follows:

a. Create a pie chart based on the nonadjacent range A10:A14, E10:E14.

b. Add a chart title above the chart. Enter Quarter 3 Revenue as the chart title.

c. Change the chart colors to Monochromatic Palette 6 to match the colors of the other charts in the workbook.

d. Resize and reposition the chart so that its upper-left corner is within cell I22 and its lower-right corner is within cell O36.

e. Change the chart layout to Layout 2 to show percentages on the data series and the legend at the top of the chart.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Annual Revenue Worksheet

Final Figure 2: Cost of Sales Worksheet

Final Figure 3: Gross Profit Worksheet