Illustrated Excel 365 | Module 4: SAM Project A Road Runner Cycling

GETTING STARTED

1. Save the file IL_EX365_4A_FirstLastName_1.xlsx as IL_EX365_4A_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_4A_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. Alain Dufour is a financial analyst for Road Runner Cycling, a manufacturer of electric bicycles in St. Lous, Missouri. 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 stacked column chart shows the combined sales revenue the company earned from five models of e-bikes 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 Major Vertical gridlines.

b. Add Primary Major Horizontal gridlines to the chart.

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

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

b. Enter the text Quarters as the title of the primary horizontal axis.

c. Change the font of the primary horizontal axis title to Tahoma.

d. Change the font size to 12 point.

4. Alain asks you to make it easier to interpret the values associated with the data points in the stacked 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 2-D pie chart on this worksheet shows how the cost of sales for each e-bike model affected the total cost of sales in the past year. Apply the Style 9 chart style to the 2-D pie chart to match the appearance of the other charts in the workbook.

6. Change the position of the data labels to Best Fit to make the labels with light text easier to read against the data series colors.

7. Alain wants you to emphasize that the Road Runner model has the highest cost of sales at 22%. Explode the largest slice of the pie (representing the Road Runner model) by 10%.

8. Go to the Gross Profit worksheet. Alain wants to compare the changes in revenue for the five models of e-bikes in each quarter. Provide this information as follows:

a. In cell G7, insert a Line sparkline that plots the quarterly revenue for the All Terrain model (the range B7:E7).

b. Change the sparkline color to Red, 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 G8:G11 with the sparkline settings in cell G7.

9. The clustered bar chart in this worksheet compares the quarterly cost of sales for each e-bike model. Alain finds the chart difficult to interpret and wants to focus on the data for the fourth quarter. Change the chart type to a Clustered Column chart that uses the e-bike models on the horizontal axis to more clearly compare the quarterly cost of sales for each of the five models.

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

11. Change the fill color of the data series representing Qtr 4 to Gray, Accent 4, Lighter 40% (8th column, 4th 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 4.

b. Change the outline color of the trendline to Gray, Accent 4 (8th column, 1st row of the Theme Colors palette) to coordinate with the color of the Qtr 4 data series.

13. Alain also wants you to create a chart comparing the fourth quarter revenue from each e-bike model to the total fourth quarter revenue to determine which model had the most sales in that period. Create a new chart as follows:

a. Create a doughnut chart based on the nonadjacent range A7:A11, E7:E11.

b. Enter Quarter 4 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 I24 and its lower-right corner is within cell S38.

e. Change the chart layout to Layout 6 to show percentages on the data series and the legend on the right.

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