BYU Student Author: @TylerBooth
Reviewers: @Christian, @DylanKing
Estimated Time to Solve: 90 Minutes
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
You have a friend named Alex who is very entrepreneurial. She loves photography and has decided to turn her hobby into a money-making side hustle. She has spent a lot of time improving her skills and connecting with her clients. However, now that tax season is approaching, she is looking for some help. She doesn’t know much about accounting or how to report her earnings from her new photography business on her tax filing. It seems confusing and she is not sure where to start. She has hired you to help her create accounting records, create basic financial statements for 2022, develop a managerial report to help her manage her business, and prepare to file her taxes as a sole proprietor.
To help you with this project, she has provided you with a spreadsheet of transactions that occurred during 2022 and a chart of accounts that she used to categorize the transactions. She has also said the following about the nature of her business:
- All transactions, whether income or expense, are made with cash.
- She operates the business with no debt.
- Each client pays a $500 Initial Deposit before the project is started and a $500 Final Payment after the project is completed.
- When expenses are incurred while traveling for a project, she bills the expense back to the client on the same day.
- Alex considers collecting payment in seven days or less from the payment due date is considered good. Collecting payment between eight and fourteen days is fair. Collecting payment fifteen days and later is poor.
Instructions
This spreadsheet has four sections that will need to be completed. The first two sheets are highlighted in red and should not be changed. However, you may copy and paste data as needed. If you get stuck on a particular section, the “Suggestions and Hints” section of this challenge should be helpful. The sections are:
Journal Entries
a. The journal entry to record Alex’s initial investment has already been completed and should serve as an example for the remaining journal entries.
b. There should be a journal entry for each transaction on the “Client Data” sheet. Each journal entry should include the category from the transaction and cash.
c. Each journal entry should be debited and credited correctly. For further guidance, see the “Suggestions and Hints” section of this page.
d. Alex would like to take bonus depreciation. Additionally, her accounting records will reflect tax rules. Manually create a journal entry dated 12/31/2022 and use the Depreciation and Accumulated Depreciation accounts. For more information on bonus depreciation, see the “Suggestions and Hints” section of this page.
Financial Statements
a. All accounts should contain a formula that references the “Journal Entries” sheet.
b. The Retained Earnings account should only reference the Net Income amount in the Income Statement.
c. For further guidance, see the “Suggestions and Hints” section of this page.
Managerial Reports
a. All empty columns should be filled with a formula that references the appropriate sheet.
b. The Income column should be the total of the Initial Deposit and the Final Payment for the project.
c. The Project Net Income column should be Income plus Travel Reimbursement minus Travel Expense.
d. The Travel Expense Occurrence and Travel Reimbursement Collected columns should reference the date that the associated transaction occurred.
e. The Days To Collect column should be the difference between the two dates. If the Travel Reimbursement has not yet been collected, the cell should read “Uncollected.” Include the Average Collection Period at the bottom of the column.
f. The Project Completion, Final Payment Collected, and Days To Collect columns should do the same as the Travel Expense Occurrence/Reimbursement/Days To Collect columns.
g. This Managerial Report should include conditional formatting that: (1) highlights the project number with red text and red fill if the Project Net Income is less than 1,000; (2) highlights the Project Net Income column with green text and green fill if the Project Net Income is 1,000 and with red text and red fill if the Project Net Income is less than 1,000; (3) both Days To Collect columns should be highlighted with red text and red fill if the cell reads “Uncollected”; (4) The Average Collection Period values should include icon sets that meet the collection criteria specified by Alex in the “Overview” section of this challenge above.
Schedule C
a. This sheet should include formulas on the appropriate lines referencing the “Financial Statements” sheet. Information will only be entered in Part I, II, and V.
b. Gross Receipts or Sales should be the total of Initial Deposit and Final Payment. Other Income will be Travel Reimbursements.
c. Meal deductions are limited to 50% of the total meal expense.
Data Files
Suggestions and Hints
Journal Entries
a. Copy and paste the date, category, and amount columns from the “Client Data” sheet into their respective columns on the “Journal Entries” sheet. Label the journal entries in ascending order beginning with 1. Next, copy the journal entry number, date, and amount columns and paste them directly after the existing data. Fill in these new categories with cash. Finally sort the data in ascending order based on journal entry number. This will alternate the transaction and cash categories and balance the journal entry.
b. The Debit/Credit column can be a formula with nested IF statements. Think through the logic of this problem. If the category is equal to cash (which could be a debit or credit and always comes after the transaction category because of the sort), then check the value above. If the value above is a credit, then the cash line should be a debit. If the value above is a debit, then the cash line should be a credit. If the category is not equal to cash, the formula should lookup the category in the “Chart of Accounts” sheet and, if it is an asset or expense, return a debit. Otherwise, it should return a credit. NOTE: This approach works in this simple case because there were no journal entry reversals during the year.
c. Depreciation is a cost recapture (expensing of the asset purchase price) over the useful life of the asset. However, as of this writing, bonus depreciation (the immediate expensing of the entire asset purchase price) is available for tax rules. This journal entry should equal to the total amount of the Camera Equipment and Office Equipment accounts.
Formula Suggestion
Cell E4: =IF(C4=“Cash”,IF(E3=“Debit”,“Credit”,“Debit”),IF(OR(XLOOKUP(C4,‘Chart of Accounts’!$B$1:$B$18,‘Chart of Accounts’!$C$1:$C$18)=“Asset”,XLOOKUP(C4,‘Chart of Accounts’!$B$1:$B$18,‘Chart of Accounts’!$C$1:$C$18)=“Expense”),“Debit”,“Credit”))
Financial Statements
a. Asset and Expense accounts will increase with debits and decrease with credits. Equity and Income accounts will increase with credits and decrease with debits. The formulas should net the debits and credits together for each account.
b. Accumulated Depreciation is a Contra-Asset account. This means that it increases with a credit and decreases with a debit. When reported on the Balance Sheet, it will be subtracted from the other assets.
c. Consider using the SUMIFS formula.
d. Total Assets should equal Total Liabilities and Equity
Formula Suggestion
Cell D6: =SUMIFS(‘Journal Entries’!D:D,‘Journal Entries’!E:E,“Debit”,‘Journal Entries’!C:C,‘Financial Statements’!C6)-SUMIFS(‘Journal Entries’!D:D,‘Journal Entries’!E:E,“Credit”,‘Journal Entries’!C:C,‘Financial Statements’!C6)
Managerial Reports
a. The formulas on this sheet may be complicated. To search for the dates, try this formula: =XLOOKUP(1,(‘Client Data’!$B$2:$B$94=$A2)*(‘Client Data’!$C$2:$C$94=$D$1),‘Client Data’!$A$2:$A$94) in cell F2 and adjust it to fit in columns G and J. This article provides some helpful explanation of the formula: Guidelines and examples of array formulas - Microsoft Support
Schedule C
a. Check Figure: Line 7 = 11,300
b. Check Figure: Line 28 = 10,258
Solution
Challenge49_Solution.xlsx
Solution Video: Challenge 49|EXCEL – Client Confusion