CLIENT BACKGROUND: JAG & ELK PIPES

Your professional career has commenced! Jag and Elk, two plumbers who merged their plumbing business around the start of 2023, have retained your services to prepare their financial statements.

Jag’s residential customers purchase plumbing services on a prepaid contract basis. For a fixed fee paid in advance, customers get 36 months of plumbing services (total $3,240, $90 per month) regardless of the nature and frequency of the service, or materials needed.
Elk’s commercial customers purchase plumbing parts on credit. Jag and Elk are known for having every part in stock so that “your labor crew will never lack a part.”

Jag and Elk have agreed not to draw salaries from the business, and not to issue any dividends yet, until they have paid off all the business’ debts. They are both working part-time with an unrelated business on the weekends to pay their bills until their business is profitable enough to support them.

So far, you have gathered the following information about Jag and Elk’s business:

During 2023, after merging their businesses into Jag and Elk Pipes, Jag and Elk obtained a loan to buy a plumbing truck and plumbing tools, and rented a warehouse in Tuas.
They pay one part-time worker at the warehouse to manage inventory and customer service.
On days when they are on-site for a residential client, they deliver parts to commercial clients either before work, on their lunch hour, or after the residential jobs each day.

Neither Jag nor Elk has any financial background. An accountant helped them prepare their financial statements for the end of 2023, but during 2024 they only kept basic records. They are now past the deadline for submitting their financial statements for the financial year ended 31 December 2024 to ACRA and are desperately in need of help from an accountant. They have thus hired you to help prepare the financial statements, and to analyze their business. They have arranged for a meeting with you to hand over and explain their financial records…

THE CLIENT MEETING

You meet Jag and Elk at your office, and after a few friendly elbow bumps, they hand you a large stack of documents (tabulated at Appendices A to D of “02 Excel Spreadsheet.xlsx”). You flip through the documents, trying to understand them as they begin explaining the information to you:

Jag points to Appendix A: “Columns B and C are the balances in our accounts at the beginning of 2024. Unfortunately, we don’t know how the transactions during 2024 affect the accounts, and therefore we can’t produce the trial balance at the end of the year in Columns H and I.”

Elk points to Appendix B: “This logbook records our service for residential and commercial clients. We’ve explained what each column means under “Legend.” Basically, this logbook records when we delivered parts to our commercial clients, when they paid us, the cost of parts sold to commercial clients, and the cost of parts used for each residential service.”

Jag points to Appendix C: “This logbook records when residential customers prepaid us for their 36-month plumbing contracts, since our business began. The effective date is when the contract begins. We know we need to calculate unearned revenue for each client but didn’t know how to!”

Elk points to Appendix D: “We haven’t made many purchases since we started in 2023, but we have summarized the information that’s relevant. We know we’re supposed to use this information to calculate expenses, but we just can’t figure out how to calculate many of the expenses.”

Jag points to Appendix D, Part 1: “We purchased a plumbing truck and a set of plumbing tools in 2023. We estimated that the truck would last ten years, and the salvage value would be $1,000, and that the tools will last five years and have a salvage value of $500. We use the double-declining balance method for the truck and the straight-line method of depreciation for the tools.”

Elk points to Appendix D, Parts 2 to 4: “Our note is at 7.5% annual interest and matures on June 1, 2029. Our income tax rate is 30%. Wehavealsolistedourpaymentrecordsinourcheckbook.Wehaveonethingthatwehaven’t paid for: the last two weeks of payroll costs.”

Jag points to Appendix D, Part 5: “I would estimate that 12% of 2024 year-end accounts receivable is uncollectible. Oh yeah! I just remembered that during 2024, a customer paid for a debt ($355) that we wrote off in 2023. When we wrote it off, we deleted our billing record from our logbooks, so I almost forgot about it!”

After Jag and Elk leave your office, you sit for a while, sipping your bubble tea as you organize your thoughts. You wake up your computer and make a few notes about the records they provided:

Appendix A: account balances at the start of the year, with the effect of transactionsduring the year and the trial balance at the end of the year currentlyempty.
Appendix B: logbook recording the dates of delivery and services performed, the cost of parts used for commercial and residential clients, and payments by commercial clients.
Appendix C: logbook recording the start dates of each of the 36-month residential contracts.
AppendixD:additionalinformationaboutpurchasesandpayments,includingJagandElk’s incomplete attempt to calculate expenses for theyear.

You close your eyes and rub your temples, pondering hard about what you need to do…

REQUIREMENTS

Requirement 1: Complete the Appendices

Required:

Appendix A: fill in the effect of transactions during 2024 in columns E & F, and complete the trial balance at the end of 2024(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 2024 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 2024 by filling in the templates provided at 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 working (see “Assessment”).

Requirement 3: Analyze the Business and Communicate Your Findings

Required: Review the company’s financial results for 2024 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. To ensure fairness for all students, I will only respond to clarifying questions about the individual assignment in the shared Google Doc. This way, everyone has access to the same information.

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.