In this activity, you will start working on a rough draft of a sales report.
1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.
https://github.com/alcadelina/Excel-
2. First, we need to set up formulas to calculate the operating profit. Click cell F2:
3. Type an equal sign (=), then, click cell D2:
4. Type a minus sign (-) and click cell E2:
5. Press Enter to complete the formula:
6. We will complete the rest of the column in the next activity. For now, click cell H2 and repeat Steps 2 through 5 to create a formula that subtracts depreciation from the operating profit, to calculate the net profit:
7. Now, let’s work on our scorecard. Click cell L2, where we want to calculate the average revenue:
8. Click the AutoSum arrow on the Home tab and click Average:
9. By default, Excel will select the values in the first part of the row, but this is not what we want. Click and drag to select cells D2 to D20 instead:
10. Press Enter to complete the formula and calculate the results:
11. Cell L3 should now be selected. This is where we want to display the highest revenue. Let’s use a different method to create this formula. To begin, click Formulas → Insert Function:
12. Ensure that the Most Recently Used category is selected. Then, click the MAX function from the list. Click OK to insert it. If you do not see the MAX function in the Most Recently Used category, change to the Maths category and select it from there.
13. Now, click and drag to select cells D2 to D20:
14. Click OK to complete the formula and calculate the results:
15. Now, use either the AutoSum command or the Insert Function command to calculate the lowest value in the Expenses column with the MIN function:
16. Let’s double-check the first two values we calculated. Select cells D2 to D20:
17. Look at the average calculated in the status bar, and compare it to the average calculated using the function:
18. However, the MAX function is not shown here. Right-click anywhere on the Status bar, then click to select Maximum in the Customize Status Bar dialog box:
19. Click anywhere in the Excel window outside of the Customize Status Bar dialog box. The dialog box will close, and you will now see the Max value in the status bar, which should match the result of the Max function:
20. Save your workbook as Activity 2-2 Complete. Close Microsoft 365 Excel to complete this activity.
21. Now, you can check out an example of a completed document in the link below:
No comments:
Post a Comment