ACCT4112 Project: Accounting Data Analyses - Use as guide

INTRODUCTION
The transition from principles of accounting to intermediate level of difficulty can be
abrupt for students, and instructors can wrongly assume that all the skills learned in the prior
courses are available for new intermediate tasks. This practice set provides a transition from
introductory to intermediate level of difficulty, while reinforcing or alerting students to the
foundational skills they need for future coursework. The practice set is half traditional
accounting cycle basics (journal entries and adjusting entries) in a more authentic setting than
typical textbook practice sets, and then extends beyond the introductory level to have students
study the resulting financial data and give the client advice about the financial status of their
small plumbing business.

CLIENT BACKGROUND
Your professional career has commenced! You just finished a meeting with Jag and Elk,
two plumbers that merged their start-up plumbing businesses two years ago. Jag’s residential
customers are all on prepaid service contract basis – for a fixed fee paid in advance, customers
get three years of plumbing services (total $648, $18 per month) regardless of the number of
calls, nature of the service, or materials needed. Elk’s commercial business is a parts delivery
service known for having every part in stock and delivered “same day” so your labor crew
doesn’t stall out for lack of a part. Both have agreed not to take any salaries from the business
and just split dividends after they have paid off all debt. They both have simple lifestyles, so
their part-time job on the weekend with a plumbing business in the next town pays their bills
until their business is big enough to throw off a living wage.
Upon merging their businesses into Jag & Elk Pipes, Jag and Elk obtained a loan and
bought a 2014 Nissan truck equipped with plumbing tools and rented a small warehouse in
midtown. 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 to commercial clients
either before work, on their lunch hour, or after the residential jobs each day. Neither Jag nor
Elk have any financial background but have kept basic records of activity for tax purposes. Their
banker requires a set of accrual basis financial statements annually. They have asked you to help,
given your new found accounting expertise. In addition to creating the financial statements for
the bank, they have requested that you analyze the profitability and liquidity of their company.

THE CLIENT MEETING
Jag: “Here are balances in our accounts at the beginning of the year (Appendix A). We
have two sets of records other than the checkbook. We keep a job log of all our trips, one for
residential clients and one for commercial clients (Appendix B). We keep track of who has paid
us on this log along with noting the date and the materials used on each trip. We also have a
listing of all our residential contracts since inception (Appendix C).”
Elk: “We don’t write that many checks but I have summarized all the checks written
during 2015 and the inventory details for you on this schedule (Appendix D). When we bought
the Nissan truck last year, we estimated it will last about six years and be worth about $5,000 at
the end of the sixth year. When we bought the tools for the truck last year, we estimated they
will last about three years and be worth about $500 at the end of the three years. We use the
straight line method of depreciation.”

Jag: “Our note is at 9.5% annual interest and matures on June 1, 2020 but we hope we
can pay it off earlier. Our effective tax rate is 15%. We have one thing that we haven’t paid for
yet: the last two weeks of payroll costs on our part-time worker ($600).”
Elk: “Oh yeah. Our accountant last year asked us if we thought any of our credit
customers might not be able to pay us. We just guessed. We ended up collecting everything
from last year. For the current customers, I would guess 2% of the uncollected amount at year
end as an estimate for future bad debts.”
REQUIREMENTS
Complete Journal Entries
Get the client data already loaded for you in the spreadsheet project file (Microsoft Excel®)
from your instructor. Create and post summary journal entries (amounts can be rounded to whole
dollars) to record all the transactions for 2015 (add formulas to increase or decrease based on
debits and credits in the journal entry columns). Prepare the adjusted trial balance (you are NOT
required to do closing entries).
All of this activity should be posted to the trial balance worksheet in the Excel project
(tab Appendix A) using the client’s existing chart of accounts (do not move rows or columns).
Use formulas to link journal entries to source documents or client data (don’t just type in the
amounts) so that if the client updates or corrects their assumptions or source document amounts,
the journal entries and related financial reports automatically update.
Create Financial Statements
Prepare financial statements: Income Statement (either multiple or single step), Balance
Sheet (add income to retained earnings) and Cash Flow Statement (either direct or indirect
method). Present them in good form on the separate “Financial Statement” worksheet tab in the
excel project file. Format your statements to look professional - e.g. margins even, centered on
the page, appropriate title on top of page, everything labeled nicely and linked to the appropriate
trial balances (link amounts to trial balances, no hard coding), so that if the client changes any
amounts, the financial statements automatically update.
Analyze the Business and Communicate Your Findings
Review the client’s financial results for 2015. Analyze the profitability of the company
using the profitability ratios of the DuPont formula (use ending values of total assets and total
equity instead of annual averages, the formula is available at textbook page #265-266). Analyze
the liquidity of the company by computing the current ratio and the quick ratio.
In a separate Microsoft Word file, write the client a ONE PAGE memo (300 word limit)
giving them clear and concise feedback on the profitability and liquidity analysis that you
performed. Exhibits do not count in the word limit. The memo should support your analysis with
observations and explanations that help the client understand your findings. Be specific but short.
You can use charts or exhibits to show financial relationships that help illustrate your thoughts.
Jag and Elk are busy and not interested in sweet talking or bragging about how hard you worked.
Get right to the point—mention all the important matters in the first sentences and then explain
these matters in the coming paragraphs. Avoid off-topic comments and do not mention ideas or
balances that do not support the important issues you are drawing to the client’s attention. Stick
to everyday language. Your client is a novice with no need or interest in learning technical terms.
Output to be submitted: Make sure saving your file-in-progress frequently. In completion,
upload your excel spreadsheet file in the Dropbox of the BrightSpace. One-page memo should be
saved at Microsoft Word file or pdf file. Upload the memo file also in the same Dropbox.