New Perspectives Excel 2019 | Module 3: SAM Critical Thinking Project 1c Scrub Jay Peripherals Performing Calculations with Formulas and Functions GETTING STARTED Open the file NP_EX19_CT3c_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought3

Share With

New Perspectives Excel 2019 | Module 3: SAM Critical Thinking Project 1c

Scrub Jay Peripherals

Performing Calculations with Formulas and Functions

GETTING STARTED

• Open the file NP_EX19_CT3c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX19_CT3c_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file NP_EX19_CT3c_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. Danilo Lewellen is an entrepreneur planning to manufacture and market an innovative handheld controller for virtual reality gaming. Danilo is building a business plan as he recruits investors and plans logistics for the launch of his new company, Scrub Jay Peripherals, and wants to use Excel to summarize data and create projections.
   Switch to the Sales Projections worksheet. The table for yearly sales projections should show year-over-year growth projections ranging from 5% at the low end to 35% at the high end, from 2021 through 2025.
   Extend the percentages horizontally up to 35%.
2. Extend the list of years down to 2025.
3. AutoFill the formatting from A7:H8 through to cell H11 to format the rest of the table.
4. Calculate the 5% projection in 2022 using a formula that multiplies the 2021 value by the percentage and then adds the 2021 value to the result. Be sure that the formula always references the percentage row. Copy the formula down to fill the remaining years and then across to fill the remaining percentages.
5. Switch to the Cost Estimates worksheet, which shows the cost per unit for manufacturing and shipping among various vendors. Danillo wants extra data to help make vendor and shipper recommendations.
   Use a function to calculate the average manufacturing cost per unit in the appropriate cell. Use a similar function to calculate the average shipping cost per unit.
6. Use a function to calculate the maximum manufacturing cost per unit in the appropriate cell. Use a similar function to calculate the maximum shipping cost per unit.
7. Use a function to calculate the minimum manufacturing cost per unit in the appropriate cell. Use a similar function to calculate the minimum shipping cost per unit.
8. Use a function in the appropriate cell to determine the best manufacturing candidate based on minimum cost. Specify an exact match in your function since the values are not sorted. Use a similar formula in the appropriate cell to find the best shipping candidate based on minimum cost.
9. Switch to the Profit Projections worksheet. This worksheet shows cost and sales numbers for 2021, a table similar to the one on the Sales Projections worksheet, and a timeline to launch. The timeline needs to be completed.
   Use a function in the appropriate cell to insert the current date in the Timeline section of the worksheet. Be sure that the date displays in the format *m/dd/yyyy, matching the format in the cell below.
10. Use a function in the appropriate cell to calculate the number of working days between the current date and the launch date.
11. The profit projections will be based on the sales projections table in the upper left. Begin completing this table by extending the percentages horizontally up to 35%.
12. Extend the list of years down to 2025.
13. Without using a function, calculate the 5% projection in 2021 using a formula that subtracts the manufacturing cost and the shipping cost from the sales price and then multiplies the result by the number of sales units. The formula should always reference those exact cells. Copy the formula across the row.
14. Calculate the 5% projection in 2022 by adding 1 to the percentage and then multiplying the result by the 2021 projection. Update the formula to always reference the percentage row. Copy the formula down to fill the remaining years, and then across to fill the remaining percentages.
15. The table is now complete. Delete the row of text below the table.

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 SAM website to submit your completed project.

(Note: The current date in the Profit Projections worksheet may differ from that shown below.)

Final Figure 1: Sales Projections Worksheet

Final Figure 2: Cost Estimates Worksheet

Final Figure 3: Profit Projections Worksheet