Step 32 of 40 - Prepare Financial Statements
This step provides an opportunity assemble Financial Statements using formulas that reference other worksheets.
Q: I haven't started building the financial statements yet, but all of the check figures are showing red. Did I do something wrong in my assignment?
The check figures are designed to give you feedback on the financial statements themselves, not the rest of the project. You should start seeing green checkmarks appear as you get the financial statements built out.
Q: Where do I get the numbers that I'm supposed to use on the financial statements?
A: The financial statements are to be based upon the Adjusted Trial Balance that you created on the Trial Balances worksheet. You'll choose accounts from the chart of accounts in the blue cells and then use a formula to reference the corresponding account balances from the Adjusted Trial Balance. You can use a simple cell reference, like "='Trial Balances'!A1 if the amount that you want to reference is in cell A1 of the Trial Balances worksheet. You can earn extra credit if you use the SUMIF or XLOOKUP functions (discussed earlier in the assignment) to look the values up by account. You will only get partial credit if you type in static values, like 88,000 instead.
Keep in mind that the three reports do interact with each other. You'll need to reference Net Income from the Income Statement on the Statement of Shareholder Equity. You'll then need to reference the ending balances for Common Stock and Retained Earnings from the Statement of Shareholder's Equity on the Balance Sheet. All other amounts on the balance sheet will be derived from the adjusted trial balance.
Q: Should I be seeing negative numbers on my general ledger worksheet?
A: Yes, this is the nature of a general ledger report. Asset and Expense accounts typically have debit balances (which will be positive amounts), while liability, equity, and revenue accounts typically have credit balances (which will be negative). The sum of the debits and credits (positive and negative balances) has to net out to zero. You're going to see the same thing on the trial balance reports, but the credit amounts won't be negative, but will instead appear in the Credit column. In short, a General Ledger report has a single balance column, which is why you see positive or negative numbers there. Conversely a trial balance has separate debit and credit columns.
Written description of the slides
In Step 32, you’re going to assemble all three financial statements. Use formulas that reference the Adjusted Trial Balance on the Trial Balances worksheet.
TIP: An amount from the Income Statement should be referenced on the Statement of Shareholder's Equity. Two amounts on the Statement of Shareholder's Equity should be referenced on the Balance Sheet.
TIP: Check figures are available in cells A30:M39.
NOTE: You must use formulas as opposed to entering numbers if you want full credit for these steps. You may use simple cell references or earn extra points by using SUMIF or XLOOKUP.
Steps labeled YOUR TURN are reinforcing steps that present opportunities to apply concepts covered earlier in the assignment.
We're here to help
If you're stuck or confused, send a copy of your workbook by way of one of these methods:
• Share: Click the Share command in the upper-right hand corner of the Excel screen, choose Share again, and then share the workbook with support@studentsexcel.com.
• Upload: You can upload a copy of your workbook at www.studentsexcel.com/student-upload.
• Email: You can email your workbook as an attachment to support@studentsexcel.com.
Be sure to listen to the audio portion of the video as you work through the assignment. The presenter elaborates further on what is being presented on screen and will offer tips.