Homework answers / question archive / Illustrated Excel 2019 | Module 1: SAM Project 1a ExpressBuy Complete a profit analysis worksheet GETTING STARTED Open the file IL_EX19_1a_FirstLastName_1

Illustrated Excel 2019 | Module 1: SAM Project 1a ExpressBuy Complete a profit analysis worksheet GETTING STARTED Open the file IL_EX19_1a_FirstLastName_1

MS Excel

Share With

---

Illustrated Excel 2019 | Module 1: SAM Project 1a

ExpressBuy

Complete a profit analysis worksheet

GETTING STARTED

• Open the file IL_EX19_1a_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX19_1a_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 IL_EX19_1a_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. Emma Demitros is a direct sales representative for ExpressBuy, an online marketplace for consumer products, headquartered in Yonkers, New York. She has created a worksheet comparing the profits of two office product lines, and has asked you to help complete the analysis.
   Go to the Profit Analysis worksheet. Enter the text ExpressBuy Office Products in cell A1 to provide a title for the worksheet.
2. In cell A2, enter the text Profit Analysis to provide a subtitle.
3. Clear everything from cell G2 to remove the redundant information.
4. In cell A3, change the text "2017" to 2019 so that the cell contains the correct text, "ExpressBuy Office Supplies 2019-2021 Profit".
5. In cell D9, enter the value 48050 to provide the profit amount for storage products in 2021.
6. Use the Fill Handle to fill the range E6:E10 with the formula in cell E5 to find the total profit for each type of office supply.
7. Emma wants to calculate the total profit in office supplies for each year and for all types. Enter this information as follows:
8. In cell B11, enter a formula that uses the SUM function to calculate the total profit of office supplies for 2019 (the range B5:B10).
9. Copy the formula in cell B11, and then paste it in the range C11:E11, pasting the formulas only.
10. In cell A13, insert the text 2019-2021 Profit after the word "Supplies" so that the cell contains the text "ExpressBuy Printing Supplies 2019-2021 Profit".
11. Enter the information shown in Table 1 into the range B18:D18 to provide profit information for specialty printing supplies.

   Table 1: Values for the Range B18:D18

 

Cell	B18	C18	D18
Value	34200	36500	35750

 

1. Emma wants to know the difference in total profits between the two product lines. Determine the difference as follows:
2. In cell B23, enter a formula that subtracts the total profit of office supplies (cell B11) from the total profit of printing supplies (cell B19).
3. Copy the formula in cell B23, and then paste it in the range C23:E23, pasting the formulas only.
4. Emma started calculating statistics on the sales of office and printing supplies in 2021. Complete the statistics as follows:
5. In cell H4, enter a formula that uses the AVERAGE function to find the average profit from office supplies in 2021 (the range D5:D10).
6. In cell H7, enter a formula that uses the MAX function to find the most profitable printing supply in 2021 (the range D15:D18).
7. In cell H8, enter a formula that uses the MIN function to find the least profitable office supply in 2021 (the range D5:D10).
8. Emma wants to identify the most profitable printing supply. Copy the contents of cell A16 and paste the cell value with no formatting in cell I7.
9. Hide the gridlines for the Profit Analysis worksheet to make it easier to read.
10. Change the orientation of the worksheet to Landscape to prepare for printing the worksheet on one page.

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.

 

 

      Final Figure 1: Profit Analysis Worksheet