Requirement 1: Complete the Appendices

Required:

• Appendix A: fill in the effect of transactions during 2022 in columns E & F, and complete the trial balance at the end of 2022 (after adjusting but before closing entries) in columns H & I.
• Appendix C: fill in the unearned revenue for each residential client in column J.
• Appendix D: fill in each expense for 2022 in the boxes in column D.

Note: You should not alter the tables provided by Jag and Elk, e.g., by adding your own accounts to the trial balance, or by creating new data about purchases and payments on top of what they provided. But you can use other cells in the Excel sheets for your own workings

Requirement 2: Prepare the Financial Statements

Required: Prepare the Balance Sheet, Income Statement, and Statement of Cash Flows for the year ended 31 December 2022 by filling in the templates provided in the “Financial Statements” tab.

Note: Please use formulas throughout your solutions, and not type in numbers manually, so that you can trace where you made mistakes, and so that I can trace your work (see “Assessment”).

Requirement 3: Analyze the Business and Communicate Your Findings

Required: Review the company’s financial results for 2022 by filling in the templates provided at the “Ratios” tab. Write a short memo (200 words maximum) about the financial health of the company by analyzing its financial ratios and comparing them with the closest competitor’s ratios.

Assessment

Grading will be based on the accuracy of your solutions. If you made errors in Requirement 1, I would attempt to carry errors forward to Requirements 2 and 3 by replacing your solutions with the correct answers. Hence it is essential to always use formulas throughout the spreadsheet, and not to alter the order or layout of Jag and Elk’s tables in the appendices.

Some helpful tips!

I recommend starting with Appendices B and C by computing the commercial and residential revenue, and then Accounts Receivable and Unearned Revenue. After that, you can work through Appendix D to calculate expenses. Along the way as you calculate the transactions during the year, you can steadily complete the middle columns of Appendix A, and then the trial balance at the end of the year. Out of fairness to all students, for the individual assignment, I will only answer clarifying questions on Google Docs so all students can see the answer.

Finally… 

This is an individual assignment, so out of fairness to all students, copying another student’s work (e.g., virtually identical spreadsheets) will be taken very seriously and will result in no credit being given. Do note that there are many different possible ways of doing the workings and formulas.