New Perspectives Excel 365 | Module 3: SAM Project A Pro Gear

GETTING STARTED

1. Save the file NP_EX365_3A_FirstLastName_1.xlsx as NP_EX365_3A_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_3A_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. Start by determining the number of days in transit for each order as follows:

a. In cell F10, insert a formula without using a function that subtracts the shipping date (cell D10) from the delivery date (cell E10).

b. Use the Fill Handle to fill the range F11:F36 with the formula in cell F10.

2. If the shipper completed the delivery in two days or less, they can bill Pro Gear a surcharge of $14.00 per shipment. Calculate the total shipping charge as follows:

a. In cell H10, insert a formula using the IF function that tests whether the number of days in transit (cell F10) is less than or equal to 2.

b. If the number of days in transit is less than or equal to 2, add the standard shipping charge (cell G10) to 14.

c. If the number of days in transit is greater than 2, display the standard shipping charge (cell G10).

d. Use the Fill Handle to fill the range H11:H36 with the formula in cell H10.

3. Now you can complete the summary in the range A4:H7. In cell C5, insert a formula using the COUNTA function that counts the unique city names (the spill range J10#) to determine the number of cities receiving shipments.

4. In cell C6, enter a formula using the ROUNDUP and AVERAGE functions that averages the number of days in transit (the range F10:F36) and rounds the result to 0 decimal places to display a whole number of days.

5. In cell C7, enter a formula using the MAX function that identifies the most days a shipment spent in transit (the range F10:F36).

6. In cell G5, enter a formula using the MIN function that identifies the fewest days a shipment spent in transit (the range F10:F36).

7. Latrice wants to identify a shipper that delivered a shipment in the minimum number of days. Create a formula that provides this information as follows:

a. In cell G6, begin to enter a formula using the XLOOKUP function.

b. Use the minimum number of days in transit (cell G5) as the lookup value.

c. Use the Days in Transit (the range F10:F36) as the lookup_array.

d. Use the Shipper names (the range B10:B36) as the return_array.

e. Display "Not found" if the lookup value is not found.

f. Specify an exact match (0) as the match_mode.

g. Use the default for the search_mode so Excel searches from the top of the lookup_array.

8. Go to the Sales Analysis worksheet. In cell B2, use the TODAY function to insert the current date as the report date.

9. Use the Quick Analysis tool to sum the quarterly sales revenue for each product category and the annual total (the range B5:E9) and display the results in the range F5:F9. [Mac Hint: In cell F5, insert a formula using the SUM function to total the footwear sales for each quarter (the range B5:E5), and then fill the range F6:F9 with the formula in cell F5, filling without formatting. Bold the range F5:F8 to make the totals stand out.]

10. Latrice wants to calculate the percentage each product category contributed to the total sales. She also wants to avoid a divide by zero error in the calculation. Complete the formula as follows:

a. In cell G5, enter a formula using the IFERROR function that divides the amount of footwear sales for the year (cell F5) by the total sales (cell F9) and displays the text "Error" if the result is an error.

b. Use an absolute reference to cell F9 in the formula.

c. Use the Fill Handle to fill the range G6:G9 with the formula in cell G5, filling without formatting.

11. Latrice set a goal for the current year of increasing sales revenue by 2.5 percent over last year. Enter the revenue goals as follows:

a. In cell I5, enter a formula without using a function that multiplies the footwear revenue for the previous year (cell H5) by the target increase percentage (cell B11), and then adds the result to the footwear revenue for the previous year (cell H5).

b. Use an absolute reference to cell B11 in the formula.

c. Use the Fill Handle to fill the range I6:I9 with the formula in cell I5, filling without formatting.

12. She also wants to display the difference between the goal amount and the total revenue for this year rounded to the nearest whole number. Provide this information as follows:

a. In cell J5, enter a formula using the ROUND function that subtracts the goal amount (cell I5) from this year's total revenue for footwear (cell F5) and rounds the result to 0 decimal places.

b. Use the Fill Handle to fill the range J6:J9 with the formula in cell J5, filling without formatting.

13. Latrice wants to project sales for player equipment for the next four years using increases of a range of percentages. Use the values in the range B14:C14 to extend the percentages to the range D14:F14 in increments of 2%.

14. Calculate the yearly projections as follows:

a. In cell B16, enter a formula without using a function that multiplies the sales for the current year (cell B15) by the first potential percentage (cell B14) and then adds the current year value (cell B15).

b. Use a mixed reference to cell B14 in this formula by including an absolute reference to row 14.

c. Use the Fill Handle to fill the range B17:B19 and then the range C16:F19 with the formula in cell B16.

15. The range H13:I17 includes sales projections for a new product, a headset for coaches. Latrice wants to know how many headsets the company needs to sell to reach sales of $250,000. Use Goal Seek to set the sales total (cell I17) to 250,000 by changing the units sold (cell I14).

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: Shipping Analysis Worksheet

Final Figure 2: Sales Analysis Worksheet