You will need a basic understanding of accounting and Excel to solve all the problems: the SUM and IF functions; make the accounting numbers red, insert a double underline, create financial statements from a trial balance, use the Fill Handle, ways to AutoFill the formulas, use custom cell formatting, make a general ledger, prepare journal entries, perform data validation, and adjust background cell color features.
Download Practice Workbook
You can download the Excel file here.
Problem Overview
There will be eight exercises related to Excel accounting practice problems. Each problem has a distinct dataset. The “Problem” sheet shows the exercises, and the “Solution” sheet shows the solved problems. The following image showcases the first problem.
- Exercise 1 – Formatting: Twenty one cash transactions are given in this first exercise. Your task is to change the formatting of the expenses to red and find the total value, double underlining it.
- Exercise 2 – Transaction Analysis: Ten transactions are given. You will use them to create a summary (distribute them into Assets, Liability and Owner’s Equity account) and solve the accounting equation.
- The Owner invests $13,000 cash on a business.
- Purchases equipment: $5,000 cash.
- Buys supplies: $1,400 credit.
- Service provided: $1,000 cash.
- Purchase of marketing advertisement: $200 credit.
- Service provided: $1,200 cash and $1,800 credit.
- Cash payment of office rent: $500, salaries and wages $800, utilities bill $100.
- Payment of accounts payable for the marketing advertisement: $200
- From the sixth transaction, the owner received $400.
- Owner’s withdrawal:$1,100.
- Exercise 3 – Complete Financial Statements: Information is provided for four financial statements. Calculate the missing information.
- The following GIF shows a partial solution to this exercise.
- Exercise 4 – Examine the Impact of the Transactions: There are seventeen statements. Your task is to determine if those increase or decrease the three accounts (assets, liabilities, and owner’s equity).
- Exercise 5 – Prepare Financial Statements from Transactions: Ross Geller opens his own firm on December 1,2022. During the first month of the operation, the following transactions took place.
- Ross invested $13,000 cash on the firm.
- Paid $1,000 for the office’s December rent.
- $5,000 worth of equipment was purchased with credit.
- Performed services for a customer: cash $1,700.
- On a note payable, borrowed $900 from a bank.
- Performed a second service for a fee of $4,000 on account.
- Paid the following monthly expenses: $700 for salaries and wages, $500 for utilities, and $300 for advertising.
- For personal use, Ross withdrew $3,000 from the account.
- Create an income statement, an owner’s equity statement, and a balance sheet.
- Exercise 6 – Debit Credit Rules & Use of Data Validation: There is a list of ten accounts. You will return whether it will be Debit or Credit when those accounts increase. Use data validation and a conditional formula in the Decrease column to return the opposite.
- Exercise 7 – Create a Journal and a Ledger: Your goal is to create a journal and a ledger using the following transaction data for a shop:
- December 1 – Owner invested $10,000 on the business. The owner purchased office equipment: $5,000 for a 3-month, 13%, $5,000 payable note.
- December 2 – Receives $1,200 cash in advance for a service that is expected to be completed within the month.
- December 3 – Paid rent expense: $900 cash.
- December 20 – The owner withdraws $4,000 for personal use.
- Exercise 8 – Prepare Income Statement from a Trial Balance: An adjusted trial balance is given. Your task is to create an income statement using those values.
The following image shows the solution to the first problem.
