You will find the Excel test for an accounting interview in this article. You will need a basic understanding of accounting and Excel to solve all the problems. Additionally, you should know these SUM, INDEX, MATCH, VLOOKUP, 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, insert pivot tables, group pivot table data by the year and adjust background cell color features to find solutions to the problems. You can use any version of Excel to solve the problems.
Download Practice Workbook
You can download the Excel file from the following link.
There will be eleven exercises related to the Excel test for an accounting interview. Each problem has a distinct dataset. The “Problem” sheet shows the exercises, and the “Solution” sheet shows the problems worked out. The following image shows the first problem of this article.
- Exercise 01 Use of Formatting: Twenty one cash transactions are given in this first exercise. Your task is to change the formatting of the expenses to red color. Then, find the total value and give a double underline to it.
- Exercise 02 Transaction Analysis: Ten transactions are given. You will use those to create a summary of the transaction (distribute them into the Assets, Liability and Owner’s Equity account). After that, satisfy the accounting equation.
- Owner invests $13,000 cash on the business.
- Purchases equipment for $5,000 cash.
- Buys supplies for $1,400 on credit.
- Service provided for $1,000 cash.
- Purchase of marketing advertisement for $200 on credit.
- Service provided for $1,200 cash and $1,800 on credit.
- Cash payment of office rent $500, salaries and wages $800, utilities bill $100.
- Payment of accounts payable for the $200 marketing advertisement.
- From the sixth transaction, the owner received $400.
- Owner’s withdrawal of $1,100.
- Exercise 03 Complete Financial Statements: Information is provided for four financial statements. Your task is to calculate the missing information.
- The following animated image shows the partial solution to this exercise.
- Exercise 04 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 05 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 happened.
- Ross invested $13,000 in cash in the firm.
- Paid $1,000 toward the office’s December rent.
- $5,000 worth of equipment was purchased with credit.
- Performed services for a customer for cash of $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.
- Now, create an income statement, owner’s equity statement, and balance sheet from these.
- Exercise 06 Debit Credit Rules & Use of Data Validation: In this exercise, there is a list of ten accounts. You will return whether it will be Debit or Credit when those accounts increase. Use the data validation for this. Moreover, use a conditional formula in the Decrease column to return the opposite.
- Exercise 07 Create a Journal and a Ledger: Your goal is to create a journal and a ledger using the following transaction data for a shop:
- Firstly, December 1 – Owner invested $10,000 on the business. Additionally, the owner purchased office equipment of $5,000 for a 3-month, 13%, $5,000 note payable.
- Secondly, December 2 – Receives $1,200 cash in advance for a service that is expected to be completed within the month.
- Thirdly, December 3 – Paid rent expense of $900 in cash.
- Lastly, December 20 – The owner withdraws $4,000 for personal use.
- Exercise 08 Prepare Income Statement from a Trial Balance: An adjusted trial balance is given. Your task is to create an income statement using those values.
- Exercise 09 Lookup from Left to Right: Find the price of a specific Order ID (131403). Use the VLOOKUP function to solve this.
- Exercise 10 Lookup from Right to Left: Lookup the Order ID from a specific price ($50). This time, you will search for data from the right side to the left side. Use INDEX MATCH to solve this problem.
- Exercise 11 Grouping Data by Year by Using Pivot Table: The last task is to group the sales amount by the year. Additionally, you can try to group the data by weeks and months.
Finally, the following image shows the solution to the first problem.
Thank you for reading this article. By completing the Excel test for an accounting interview, we hope that you have gained knowledge about Excel. You can find more articles similar to this on the ExcelDemy website. Moreover, if you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!