New Perspectives Excel 365 | Module 3: End of Module Project 2 Mobius Audiobooks
PERFORM CALCULATIONS WITH FORMULAS AND FUNCTIONS
· Excel Projects Help
GETTING STARTED
1. Save the file NP_EX365_EOM3-2_FirstLastName_1.xlsx as NP_EX365_EOM3-2_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_EOM3-2_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. Binh Phan is a sales manager for Mobius, an e-commerce site that sells a variety of goods, including audiobooks. Binh has collected data on audiobook bundles sold in the past year and asks you to use Excel to add formulas and help her analyze the data. Go to the Audiobook Bundles worksheet. In cell B4, insert the TODAY function to record the current date.
2. In cell D19, create a formula without using a function that multiples the number of historical audiobooks (cell B19) by the average book length (cell E7). Use an absolute reference to the average book length, and then copy the formula to find the length of the other genres of audiobooks, filling the range without formatting.
3. Create lookup functions to complete the Bundle Lookup table. In cell H8, create a formula that uses the XLOOKUP function to display the number of audiobooks in the Mystery bundle. Lookup the bundle name (cell H7) in the Genre list (the range A19:A24), and return the number of audiobooks (the range B19:B24). Use the defaults for the other arguments.
4. In cell H9, create a formula that uses the XLOOKUP function to display the authors in the Mystery bundle. Look up the bundle name (cell H7) in the list of bundles in the Bundles & Authors table (the range G19:G24), and return the names of the authors (the range H19:J24). Use the defaults for the other arguments.
5. In cell H10, create a formula without using a function that multiples the number of audiobooks (cell H8) by the average book length (cell E7), and then adds the result to the length of promotional materials (cell E8).
6. In cell H11, create a formula that uses the IF function that tests whether the total length (cell H10) is greater than the limit (cell E9). Display "Yes" if the total length is over the limit and display "No" if it is not.
7. Switch to the Audiobook Orders worksheet. Use the value in cell B4 to fill the blank column headings with the names of the other three quarters.
8. Use the Quick Analysis tool to sum the quarterly sales revenue for each audiobook bundle and the totals (the range B5:E11) and display the results in the range F5:F11. [Mac Hint: In cell F5, insert a formula using the SUM function to total the orders for each quarter (the range B5:E5), and then fill the range F6:F11 with the formula in cell F5, filling without formatting.]
9. In cell G5, create a formula that uses the ROUNDUP function to divide the total orders for the Historical bundle (cell F5) by the total orders (cell F11) and round the result to 2 decimal places. Use an absolute reference to the total orders cell, and then fill the rest of the column with the formula in cell G5, filling without formatting.
10. In cell H5, create a formula that uses the MAX function to find the highest number of Historical bundle orders (the range B5:E5). Fill the rest of the column except for the Total cell with the formula in cell H5, filling without formatting.
11. In cell I5, create a formula that uses the MIN function to find the lowest number of Historical bundle orders (the range B5:E5). Fill the rest of the column except for the Total cell with the formula in cell I5, filling without formatting.
12. In cell B13, create a formula that uses the ROUND and AVERAGE functions to calculate the average of Quarter 1 orders (the range B5:B10) and round the result to 0 decimal places. Fill the cells for Quarters 2, 3, and 4 with the formula in cell B13.
13. In cell B15, create a formula that uses the IFERROR function to divide the average orders for Quarter 1 (cell B13) by the goal for the quarter (cell B14) and displays the text "Enter goal" if the result is an error. Fill the cells for Quarters 2, 3, and 4 with the formula in cell B15.
14. Use Goal Seek to identify the number of Mystery bundles to sell (cell B18) that results in sales of 100,000 (cell B21).
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 B4 of the Audiobook Bundles worksheet has been intentionally blurred as it will never be constant.
Final Figure 1: Audiobook Bundles 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: Audiobook Orders 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.