BYU Student Author: @Jimmy_Han
Reviewers: @Benjamin_Lau , @Sterling_Lane
Estimated Time to Solve: 30 Minutes
READ BEFORE YOU BEGIN: For this challenge, you are required to understand the fundamental knowledge of accounting. Specifically, you are expected to know…1) how journal entries work, 2) what the trial balance is, and 3) what each account in the trial balance means.
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
Recently onboarded as an Accountant at Cougar Equipment, a leader in providing top-tier equipment solutions, your primary mission involves crafting a detailed trial balance from journal entries. To elevate the clarity and user experience within Excel, you’re tasked with integrating conditional formatting into the trial balance, assigning distinct color codes to different account categories: assets, liabilities, owner’s equity, and temporary accounts. Your responsibilities further extend to the development of a Dashboard worksheet within the same Excel file. This dashboard is meticulously designed to showcase critical Key Performance Indicators (KPIs) including net profit margin, current ratio, return on assets (ROA), debt to equity ratio, inventory turnover, and return on equity (ROE). This initiative emphasizes your dedication to detail and analytical depth, offering a dynamic and insightful tool for management to effortlessly assess Cougar Equipment’s financial health and operational performance.
Instructions
- Open the “Challenge231_Data” file that is attached to this challenge. Make sure to unblock the file to use the macro in the file. To do so, follow these instructions: right-click and select Properties. Select the Unblock checkbox at the bottom of the General tab in Properties, then select OK.
- Create a trial balance under the header “Trial Balance” on cell K5 in the “Journal Entries” worksheet. Account ID should be in column J, Account Name in column K and the amount(balance) in column L. Excluding the header, the range of the first row of the trial balance should be J6:L6.
a. In column J, use the Sort and Unique function to create Account ID. Refer to Suggestions and Hints #1.
b. In column K, use the Lookup function to create Account Name based on the Account ID that we created above.
c. In column L, use the Sumif function to sum balances by each account. - Conditional Format the Trial Balance assigning distinct color codes to different account categories: assets, liabilities, owner’s equity, and temporary accounts. Refer to Suggestions and Hints #2.
- Using the Sumif/Sumifs function to create KPIs in the “Dashboard” worksheet. (The same formula of each KPI below is also in the comment within each cell where the KPI header is.)
a. Net Profit Margin = Net Income / Total Revenue.
Net Income is the sum of all temporary accounts. (Anything with account ID above 4000)
Total revenue = sum of all accounts. with account ID between 4000 and 5000.
b. Current Ratio = Current Assets / Current Liabilities
Current Assets = sum of all accounts. with account ID between 1100 and 1200.
Current Liabilities = sum of all accounts. with account ID between 2100 and 2200.
c. Return on Asset = Net Income / Total Asset
Net Income is the sum of all temporary accounts. (Anything with account ID above 4000)
Total Asset = sum of all accounts. with account ID between 1000 and 2000.
d. Debt to Equity Ratio = Total Liabilities / Total Shareholder’s Equity
Total Liabilities = sum of all accounts. with account ID between 2000 and 3000.
Total Shareholder’s Equity = sum of all accounts. with account ID between 3000 and 4000.
e. Inventory Turnover = Cost of Goods Sold / Inventory (for this KPI, you can lookup the value directly from Trial Balance instead of using Sumif)
f. Return on Equity = Net Income / Total Shareholder’s Equity
Net Income is the sum of all temporary accounts. (Anything with account ID above 4000)
Total Shareholder’s Equity = sum of all accounts. with account ID between 3000 and 4000. - Save your Excel file as xlsm
Data Files
Suggestions and Hints
- The
UNIQUEfunction in Excel extracts unique values from a range or array, allowing you to remove duplicates and see only distinct entries. To integrate it with theSORTfunction for sorting numbers, you can nest theUNIQUEfunction inside theSORTfunction. This combination enables you to first identify and list unique numbers from a given dataset and then sort these numbers in ascending or descending order, streamlining data analysis and organization. - When creating the conditional format:
Select the Range: First, select the range of cells you want to apply the conditional formatting to, including the target cells and the cells to their right that you also want to format. Use this area for the range: $J$6:$L$1048570
Conditional Formatting Rule: Go to Home > Conditional Formatting > New Rule. Now, enter the formula in the formula box. For the assets (with account numbers between 1,000 and 2,000), I used this formula: AND($J6<2000, $J6>0). Notice that I did not anchor the rows but the columns.
Solution