Performing Calculations with formulas and functions

Scrub Jay Peripherals
PERFORMING CALCULATIONS WITH FORMULAS AND FUNCTIONS

GETTING STARTED

• Save the file NP_EX365_2021_3a_FirstLastName_1.xlsx as NP_EX365_2021_3a_FirstLastName_2.xlsx
  • Edit the file name by changing “1” to “2”.
  • If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
• With the file NP_EX365_2021_3a_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
  • If cell B6 does not display your name, delete the file and download a new copy.

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. Use the values in the range B6:C6 to extend the incremented percentages to the range D6:H6.
2. Use the values in the range A7:A8 to extend the list of years to the range A9:A11.
3. Use AutoFill to fill the range A9:H11 with the formatting from the range A7:H8.
4. In cell B8, create a formula without using a function that multiplies the value in cell B7 by the value in cell B6 and then adds the value in B7. In this formula, use a mixed reference for cell B6 by including an absolute reference to row 6. Copy the formula from cell B8 to the range B9:B11 and then copy the formulas from the range B8:B11 to the range C8:H11.
5. Switch to the Cost Estimates worksheet. In cell A9, create a formula using the AVERAGE function that calculates the average of the values in the range A5:A7, then copy your formula to cell D9.
6. In cell A10, create a formula using the MAX function that identifies the maximum value in the range A5:A7 and then copy your formula to cell D10.
7. In cell A11, create a formula using the MIN function that identifies the minimum value in the range A5:A7 and then copy your formula to cell D11.
8. In cell B13, create a formula using the VLOOKUP function that looks up the value from cell A11 in the range A5:B7, returns the value in column 2, and specifies an exact match. Copy the formula to cell E13.
9. Switch to the Profit Projections worksheet. In cell H5, use the TODAY function to insert the current date.
10. In cell H7, create a formula that uses the NETWORKDAYS function to calculate the number of working days between the dates in cells H5 and H6. Leave the Holidays argument blank.
11. Use the values in the range B12:C12 to extend the incremented percentages to the range D12:H12.
12. Use the values in the range A13:A14 to extend the list of years to the range A15:A17.
13. In cell B13, create a formula without a function using absolute references that subtracts the values of cells B5 and B7 from cell B6 and then multiplies the result by cell B8.
    Copy the formula from cell B13 to the range C13:H13.
14. In cell B14, create a formula without using a function that adds 1 to the value in cell B12 and then multiplies the result by the value in cell B13. In this formula, use a mixed reference for cell B12 by including an absolute reference to row 12. Copy the formula from cell B14 to the range B15:B17 and then copy the formulas from the range B14:B17 to the range C14:H17.
15. Delete row 19.

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.
(Note: The value in cell H5 and H7 of the Profit Projections worksheet is intentionally blurred.)