Case FA-3001E: Transaction Analysis for Pepper Food Delivery

Jennifer Allen started a food delivery service on January 1, 2020 and incorporated as Pepper Food Delivery. She and her parents contributed cash to the business in exchange for common stock. She plans to advertise using a variety of social media tools. She is working out of her home, so her expenses are minimal. She is renting a car for the delivery business for now; she hopes to purchase a delivery vehicle within the first year of operations. Restaurants will pay her a cash fee for each delivery.

Go to this link to download the Excel file containing the transactions for Pepper Food Delivery for January 2020. Be sure to download the file from Dropbox rather than simply viewing it (see this link for additional information.)

The links contained in the requirements below are for short tutorial videos on the Excel skills or accounting skills needed to complete the requirement – do view those tutorial videos.

Requirements

  1. In the Excel transactions file, perform the following tasks:
    • Wrap the text (link) in the headings (Row 1) to fit the column cells.
    • Make the column headings bold (link).
    • Widen Column A to be the width of the longest date in that column (link).
    • Widen Column B to be the width of the longest description in that column (link).
    • Use the SUM function (Windows link | Mac link) to total Columns C through L in Row 82.
    • Put a single underline above and a double underline below the sums (link).
  2. List each account in the column headings in Cells C1 through L1 as one of the following types of accounts (link):
    • Asset
    • Liability
    • Equity
    • Revenue
    • Expense
  3. Calculate the following items using formulas in Excel (link):
    • Total assets as of January 31, 2020 (put formula in Cell C84)
    • Total liabilities as of January 31, 2020 (put formula in Cell C85)
    • Revenues for the month ending January 31, 2020 (put formula in Cell C89)
    • Expenses for the month ending January 31, 2020 (put formula in Cell C90)
    • Net income for the month ending January 31, 2020 (put formula in Cell C91)
    • Equity as of January 31, 2020 (put formula in Cell C86)

Hint: Equity will be Common Stock + Retained Earnings + Net income – Dividends

4. Total liabilities and equity as of January 31, 2020 (put formula in Cell C87)Using your Excel worksheet and your answers to Requirement 2, answer the following questions:

a. What would the accounting equation (link) for Pepper Food Delivery be as of January 31, 2020?

b. What accounts would be found on Pepper Food Delivery’s balance sheet (link) as of January 31, 2020?

c. What accounts would be found on Pepper’s income statement (link) for the month ended January 31, 2020?

d. Did Pepper Food Delivery have a profitable month? How do you know?

e. What is the total amount that Pepper Food Delivery owes as of January 31, 2020?

4. What was Pepper Food Delivery’s largest total expense for January?

Tutorial video

If you would like to view a quick overview of the project in Excel, see link. A different transactions file has been used in the tutorial, but otherwise the steps are the same.