Financial Statement Preparation for Wheelie Cool Bicycles

You have recently started your own accounting firm and have been engaged by Wheelie Cool Bicycles (“WCB”), which is a new business that commenced operations on April 1, 2020. WCB manufacturers a variety bicycles and sells them to local shops across Canada. One of the owners, who is the company spokesperson, has reached out to your firm for some assistance in preparing WCB’s annual financial statements for the fiscal year ended June 30, 2021. The demand for WCB’s bikes continues to grow and the company needs to expand in order to produce a sufficient supply. As such, the owners are working with a local bank to obtain a loan in order to finance this growth. WCB needs to submit financial statements to the local bank, as part of its loan application. The owners have tried to prepare the financial statements themselves; however, they keep spinning their wheels and have decided to ask for help. They have outlined their questions below and have requested a brief memo with your responses and recommendations by November 21, 2021. When performing calculations, please explain your process for the owners’ reference. For example, if you use a filter or an “if” statement in Excel, describe how you used these tools. Ideally, the owners should be able to use your description to re-perform the procedure and produce the same result.

1. The owners have provided you with sales data that their system has recorded since they commenced operations. WCB ships products to local bike shops with FOB shipping point terms. The company does not offer any sales discounts, but just asks that the invoice is paid in 30 days. Based on this information, how much revenue should be recognized during the 2021 fiscal year? Please explain your calculations.
2. Based on the sales data, does WCB need to record any deferred revenue at the end of the 2021 fiscal year? If yes, explain why and calculate the amount that should be reported on the balance sheet. Don’t forget to outline your calculations. If no, explain why and give an example of when WCB may encounter a transaction that involves deferred revenue.

3. A particular type of activity has been grinding the owners’ gears. They are completely confused and do not know the appropriate accounting treatment. Customers placed orders, but did not provide payment and WCB was unable to ship the products before the year end date. The owners would like you to provide some guidance on how to account for this activity and the rationale that supports your recommendation.

Sales Revenue Recognition

4. Using the data, what amount should be reported as accounts receivable on the balance sheet as at the end of the 2021 fiscal year? Please explain how you calculated this balance.

5. Calculate the amounts that should be reported in the following accounts receivable buckets: (1) 0-30 days outstanding, (2) 31-60 days outstanding, (3) 61-90 days outstanding, (4) 91-120 days outstanding, and (5) >120 days outstanding. Please outline the process you used to
calculate these amounts.

6. The owners would appreciate some assistance in calculating the bad debt expense for the year. Industry benchmarks indicate that companies record bad debt expense as 1% of sales or the following percentages if the aging method is used:

WCB has not decided whether it is going to use the percentage of sales or the aging method. As such, the owners would like you to calculate the bad debt expense using both approaches. Please remember to explain your calculations. The owners would also like to know what factors that they should consider when determining the correct approach.

7. The loan officer at the bank has asked WCB to assess its ability to collect cash from its customers. This is important because the company needs cash flow in order to make the loan payments. The owners have done some research and have found that comparable companies report the following accounts receivable buckets:

The above percentages reflect the portion of accounts receivable that corresponds to each bucket. For example, these percentages can be interpreted as follows: if the comparable company has $1 million of accounts receivable, then $750,000 of this balance has typically been outstanding for only 0-30 days. How does WCB compare to these other companies within the same industry? How would you assess WCB’s ability to convert accounts receivable into cash? Regardless of whether there is a collection problem, how could the owners encourage customers to pay in a timelier manner?

The project memo should be prepared in a Word document, double-spaced on letter-size paper using one-inch margins and 12-point Times New Roman font or equivalent. The report should not be more than 3 pages, plus one page of appendices. You may include any tables that are helpful to communicate your response in the appendices. Any responses provided beyond the page limit will not be graded. Only the Word document can be submitted; therefore, it is critical to ensure it includes all responses and explanations. The Excel files containing the data and any rough work will not be accepted or graded. Marks will be deducted if you do not follow the submission requirements. Please make sure that you clearly explain all of your calculations. Partial credit will only be awarded when explanations are provided and can be followed.
Outside sources (articles, books, etc.) may be used but are not required. Any reference should be listed in a bibliography at the end of the report. The bibliography will not be included in the page limit. You must show all calculations and state all assumptions leading to your answer. It is recommended that you keep the rough draft or photocopy of your assignment to minimize the problems caused by lost or missing assignments. In general, no late assignments will be accepted except for any unusual circumstances pre-approved by the instructor. When this happens, the student is required to provide proper documentation and discuss the situation immediately with the instructor to sort out the submission due date. Depending on the situation, the instructor reserves the right to possibly impose a penalty mark for the late submission or defer the weighting of the assignment to the final exam.

Students need to submit the project in Quercus. Please include a cover page with your name and student number. This cover page is not included in the page limit. The instructor will further review the procedures to submit the assignment via Quercus in class. When you upload the file to Quercus, please make sure that the file name consists of: Your Name, Student Number, Lecture Number, Memo. Each student will receive a copy of the marking rubric, which will state the grade for the project.

An important component of this project is applying concepts that we have learned in class to real world data. As a starting point, here are a couple of excel tools that might help you:

There are a number of ways in which you can use excel to respond to the questions in this project. You are welcome to use any strategy you would like to perform the requested calculations (i.e., you are not restricted to using filters or “if” statements). Please remember that regardless of the approach used – you must clearly explain your calculations and how you’ve used the tools in excel.