Working with Formulas - Exercise

Contact Us or call 1-877-932-8228
Working with Formulas - Exercise

Working with Formulas

Duration: 15 to 25 minutes.

In this exercise, you will use formulas to enter additional data into the quarterly profit and loss statement we are creating for Dave's Lemonade Stand.

  1. Continue working in the workbook from the last exercise or open Dave's Lemonade Stand - Working with Formulas.xlsx from your Webucator/Excel2013/Exercises folder.
  2. Use AutoSum to sum the Total Income in row 5.
  3. Enter a formula to make payroll 30% of Total Income each quarter.
  4. Enter a formula to make marketing 25% of the each quarter's Total Income.
  5. Enter a formula to make Supplies 20% of Total Income each quarter.
  6. Use AutoSum to sum the Total Expense in row 10.
  7. Use a formula to calculate Net Income (Total Income - Total Expense).
  8. Enter "Margin" in cell A12 and then use a formula to calculate the Margin (Net Income / Total Income).
  9. Format all cells to look like they do in the below image:
  10. Save the workbook.

Solution:

  1. Open or go to the specified file.
  2. Use AutoSum to sum the Total Income in row 5.
    1. Select cell B5 and click AutoSum and press Enter.
    2. Grab the Fill Handle in cell B5 and drag the formula in cell B5 to cell E5.
  3. Enter a formula to make payroll 30% of Total Income each quarter.
    1. In cell B7, enter "=B5*.3"
    2. Grab the Fill Handle in cell B5 and drag the formula in cell B7 to cell E7.
  4. Enter a formula to make marketing 25% of the first quarter's Total Income each quarter.
    1. In cell B8, enter "=B5*.25".
    2. Grab the Fill Handle in cell B8 and drag the formula in cell B8 to cell E8.
  5. Enter a formula to make Supplies 20% of Total Income each quarter.
    1. In cell B9, enter "=B5*.2".
    2. Grab the Fill Handle in cell B9 and drag the formula in cell B9 to cell E9.
  6. Use AutoSum to sum the Total Expense in row 10.
    1. Select cell B10 and click AutoSum and press Enter.
    2. Grab the Fill Handle in cell B10 and drag the formula in cell B10 to cell E10.
  7. Use a formula to calculate Net Income (Total Income - Total Expense).
    1. Select cell B11 and enter "=B5-B10".
    2. Grab the Fill Handle in cell B11 and drag the formula in cell B11 to cell E11.
  8. Enter "Margin" in cell A12 and then use a formula to calculate the Margin (Net Income / Total Income), which should be displayed as a percent.
    1. Enter "Margin" in cell A12.
    2. In cell B12 enter "=B11/B5".
    3. Grab the Fill Handle in cell B12 and drag the formula in cell B12 to cell E12.
    4. Select cells B12:E12 and click the Percent command.
  9. Make any necessary formatting changes.
  10. Save the workbook.
Next