New Perspectives Excel 365 | Module 4: End of Module Project 1 Fresh Start Skin Care

GETTING STARTED

1. Save the file NP_EX365_EOM4-1_FirstLastName_1.xlsx as NP_EX365_EOM4-1_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 NP_EX365_EOM4-1_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. You are a sales assistant for Fresh Start Skin Care, a natural products company in Iowa City, Iowa. The sales manager, Renata Puente, has created an Excel workbook summarizing five years of product sales. She asks you to create charts to illustrate the data and identify trends. In the Sales Analysis worksheet, Renata asks you to show the five-year sales trend for each product category and for total revenue. In the range H6:H13, add column sparklines based on the data in the range C6:G13. Apply the sparkline style Dark Blue, Sparkline Style Dark #6 to match the style of the other sparklines in the worksheet.

2. Renata wants you to show the trend of annual sales by product category. Create a Line with Markers chart based on the years in the Annual Sales by Product Category table (the range B5:G5) and the total revenue amounts (the range B13:G13). Enter Annual Revenue by Product as the chart title. Format the vertical axis to display Major units in increments of 1000.0 to reduce clutter. Resize and reposition the chart so that it covers the range J5:O14.

3. Modify the Annual Revenue by Product chart to make it easier to interpret. Format the vertical axis to show 0 decimal places in the axis labels. Add an axis title to the primary vertical axis using Sales in thousands as the text.

4. Renata also wants to compare sales by product during the last two years. Create a 2-D Stacked Column chart based on the product categories (the range B5:B12) and the last two years of sales (the range F5:G12). Enter Sales by Product: 2028 and 2029 as the chart title, and then resize and reposition the chart so that it covers the range J15:O25.

5. Change the Sales by Product: 2028 and 2029 chart to a Stacked Bar chart to make the category labels easier to read. Change the gap width between bars to 80% to widen the bars.

6. Renata asks you to focus on annual sales for the top three best-selling products. Create a 2-D Clustered Column chart comparing the sales for cleanser, eye cream, and moisturizer (the range B5:G7, B10:G10). Enter Top Selling Products as the chart title, and then resize and reposition the chart so that it covers the range J26:O39.

7. Set the Maximum bounds for the vertical axis to 1600.0 and the Minimum bounds to 0.0 to increase the height of the columns. Display a data label above the Cleanser column for each year to provide details about the top sales.

8. Renata asks you to compare the sales by region in 2029. Create a 2-D Pie chart comparing the 2029 sales (the range G16:G21) for each region (the range B16:B21). Enter 2029 Sales by Region as the chart title, and then resize and reposition the chart so that it covers the range B24:E39.

9. Modify the 2029 Sales by Region chart by including data labels that show only the category name in the Best Fit position on each slice. Remove the legend since it now displays the same information as the data labels.

10. Renata also wants to compare the annual sales in the northwest, the top-selling region. Create a 2-D Pie chart comparing the annual sales in the Northwest region (the range B19:G19). Enter Annual Sales in Northwest as the chart title, and then resize and reposition the chart so that it covers the range F24:H39.

11. Modify the Annual Sales in Northwest chart to clarify the data it represents. Display the years (the range C16:G16) as the horizontal (category) axis labels. Add data labels in the Best Fit position that show only the percentage for each slice.

12. Finally, Renata asks you to compare the annual and total sales for the first five products in the Annual Sales by Product Category table and show the sales trend for the best-selling product. Create a Clustered Column – Line Combo chart based on the data for the first five products (the range B5:G10) and the total revenue (the range B13:G13). Enter Sales Trend as the chart title, and then move the chart to a new sheet, using Product Sales as the worksheet name.

13. Go to the Product Sales worksheet. Modify the Sales Trend chart to make the data easier to understand at a glance. Switch the rows and columns to display the years on the horizontal axis and the product types in the legend. Change the chart type to display all the product types as Clustered Column charts and the Total as a Line chart on a secondary axis. [Mac Hint: Select the Total series and use the Format Data Series pane to add the secondary axis.]

14. Add a Linear trendline that shows the sales for cleanser, the best-selling product.

15. Change the chart colors to Colorful Palette 4 to coordinate with the company's colors, and then apply Style 2 to the chart to soften its appearance.

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: Product Sales Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 2: Sales Analysis Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.