New Perspectives Excel 365 | Module 3: SAM Critical Thinking Project C Pro Gear

GETTING STARTED

1. Save the file NP_EX365_CT3C_FirstLastName_1.xlsx as NP_EX365_CT3C_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_CT3C_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. Latrice Ramsey is a financial analyst for Pro Gear in Jersey City, New Jersey. The company manufactures equipment and clothing for professional sports teams, and Latrice is analyzing its shipping and sales data in an Excel workbook. She asks for your help in completing the analysis. Go to the Shipping Analysis worksheet. Before you can complete the summary information for Latrice, you need to perform calculations in other parts of the worksheet. Determine the number of days in transit for each order using a formula that subtracts the shipping date from the delivery date. Copy the formula down through the column.

2. The total shipping charge depends on how fast the shipper completed the delivery. Use a function in the appropriate cell to test whether the delivery was completed in two days or less. If it was, add $14.00 to the standard shipping charge. Otherwise, use the standard shipping charge as the total charge. Copy the formula down through the column.

3. To complete the summary information, start by using a function to count the number of cities receiving shipments.

4. Use a function to average the number of days a shipment spent in transit and round up the result to display a whole number of days.

5. Use a function to calculate the most days a shipment spent in transit.

6. Use a similar function to calculate the fewest days a shipment spent in transit.

7. Use a function in the appropriate cell to determine the name of a shipper that delivered a shipment in the minimum number of days. Display "Not found", if necessary, and specify an exact match in the function.

8. Go to the Sales Analysis worksheet. Use a function in the appropriate cell to insert the current date as the report date.

9. Calculate the total sales of each product category for four quarters and for the year. Bold the totals, if necessary.

10. Use a function in the appropriate cell to avoid a divide by zero error while dividing the footwear sales for the year by the total sales to determine the percentage the footwear category contributed to the total sales. Display "Error", if necessary, and update the formula so it always references the total sales cell. Copy the formula but not the formatting to the rest of the column.

11. Without using a function, calculate the footwear sales goal for the current year by multiplying the footwear sales for the previous year by the target increase percentage, and then adding the result to the footwear sales for the previous year. Update the formula so it always references the target increase cell. Copy the formula but not the formatting to the rest of the column.

12. Use a function in the appropriate cell to subtract the footwear sales goal amount from this year's total sales for footwear and round the result to the nearest whole number. Copy the formula but not the formatting to the rest of the column.

13. The Projected Sales: Units of Player Equipment section of the worksheet shows sales projections in increments of 2%. Extend the list of percentages to 10%.

14. Without using a function, calculate the first projection in Year 2 using a formula that multiplies the sales for the current year by the first potential percentage and then adds the current year value. Update the formula so that it always references the percentage row. Copy the formula down to fill the remaining years, and then across to fill the remaining percentages.

15. The New Product Introduction: Embedded Headset section of the worksheet shows sales projections for a new product, a headset for coaches. Determine how many headsets the company needs to sell to reach sales of $250,000.

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.

The value in cell B2 of the Sales Analysis worksheet has been intentionally blurred as it will never be constant. The value in cell I14 of the Sales Analysis worksheet has been intentionally blurred as it is the result of a Goal Seek Analysis.

Final Figure 1: Shipping 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.

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.