| Case #2 Lotus Coffee - Appendix A | | | |
| | | | | |
| | | | | |
| | 2023 Operating Projections |
| | Q1 | Q2 | Q3 | Q4 |
| | Oct-Dec | Jan-Mar | Apr-Jun | Jul-Sep |
| Expected package sales | 30,000 | 24,000 | 22,000 | 22,000 |
| Unit selling price | $20 | $21 | $21 | $22 |
| Overhead budget | Estimated to be $0.75 per unit produced |
| Selling and Admin expenses | Estimated to be 30% of sales |
| Depreciation expense | $10,000 | $10,000 | $10,000 | $10,000 |
| Raw material cost per kilogram | $7.25 | $7.25 | $7.25 | $7.25 |
Lotus Coffee Inc. Larry owns a coffee roasting business in Winnipeg - Lotus Coffee Inc. He has been doing the bookkeeping for the business, but he has no accounting experience. Since you are currently taking an accounting course at Red River College Polytechnic, he has asked you to help him. Lotus Coffee imports raw specialty coffee beans from growers in Dak Lak, a province of Vietnam. Specialty coffee is considered to be Arabica coffees highest standard. To be recognized as specialty coffee, the beans must undergo strict evaluation; the growers must fully comply with the prescribed production process, from the stage of cultivation, to harvesting, preliminary treatment, preservation, etc. Once receiving the coffee beans shipment, Lotus Coffee implements strict standards in the roasting process. It then packages these fabulous beans for Images are used with permission from www.quocloccoffee.com sale to consumers across Canada. Lotus Coffee packages all its beans into 908-gram packages. It takes 15 minutes to roast and package each bag at an expected hourly rate of $14 per hour. Most of Lotus Coffee's customers are specialty coffee shops. However, it does have a small retail space where customers may come to purchase roasted coffee beans for home use. About 25% of its total sales are paid in cash, and the remaining 75% is billed to coffee shops. Of the sales billed to coffee shops, 65% are collected within the same quarter and the remaining is collected in the following quarter. All expenses are paid the month they are incurred. The company's year-end is September 30th Larry provided the following information (EXCEL APPENDIX A). Q1 Oct-Dec 30,000 2023 Operating Projections Q2 Q3 Jan-Mar Apr-Jun 24,000 22,000 $21 $21 Q4 Jul-Sep 22,000 $20 $22 Estimated to be $0.75 per unit produced Expected package sales Unit selling price Overhead budget Selling and Admin expenses Depreciation expense Raw material cost per kilogram Estimated to be 30% of sales $10,000 $10,000 $10,000 $10,000 $7.25 $7.25 $7.25 $7.25 Phetus Lotus purchases its coffee beans in kilogram quantities (1000 grams = 1 kilogram). Shipments from Vietnam can be unreliable, so Larry prefers to have as much raw material (unroasted beans) on hand as possible. He likes to maintain a balance of raw materials equal to Images are used with permission from www.quocloccottee.com approximately 40% of next quarter sales. The September 30, 2022 raw material balance was 17,652 kilograms. CHE In past years, all the coffee bags were sold in the same quarter they were made to maintain freshness. This has left Lotus without any finished bags of coffee for sales in the early part of the next quarter. Because of this, Larry has decided that he would like to start keeping a finished coffee bag inventory of 10% of next quarter's sales. He expects sales for the first quarter in 2024 to be 33,000 packages. Larry plans to replace the roasting equipment in June 2023 with a brand-new machine that will produce even more aromatic coffee beans starting in the next fiscal year. The expected cost is approximately $99,000 and will be paid for in cash. The company needs to repay $13,500 per month on the long-term non-interest-bearing loan from Larry's father. In addition, he pays himself dividends of $12,000 per month in lieu of salary. Lotus Coffee likes to keep a minimum of $20,000 in its bank. The company has a $150,000 line of credit that can be drawn from for short-term financing. Interest is paid quarterly, and it is calculated as 5% per year on the outstanding balance at the end of the previous quarter. The line of credit balance is zero at the beginning of fiscal 2023. Any excess cash is immediately used to pay down the line of credit. Larry wants to see a complete sales budget, production budget, direct materials budget shown in kilograms and total cost, direct labour budget, income statement budget, schedule of cash collections and cash budget for 2023 each by quarter and in total. Larry is also curious about financial ratios. He would like you to explain the concept of each of the three main categories of ratios. He has provided a set of comparative financial statements and ratio calculations from the 2021 results (EXCEL APPENDIX B). From the 2022 financial statements, Larry would like you to calculate the 2022 ratios for each ratio listed from 2021. Larry wants to know how Lotus Coffee is doing compared to the previous year including any recommendations for improvement. He is particularly interested in a conclusion of how Lotus Coffee is doing under each category of ratio. As this is a huge undertaking, Larry suggests that you get a few of your friends from school to help you and to ignore all taxes for now. In appreciation, you and your friends will receive gift cards to your favourite coffee shops that serve Lotus Coffee beans. Time to call them up, convince them to help and get to work. After all, you do need coffee to study and complete all of the assignments in this college life. Complete your report in Excel, 12-point Arial font. Submit all your work in one file. Please refer to the attached rubric to ensure that all your answers are properly presented. Grading Rubric Criteria Score Max All budgets are properly completed, including titles, with allocation to quarters from the source data and overall totals. It is logical, easy to follow, incorporates all relevant case facts and is free from abbreviations, slang, and spelling, grammar, and incorrect word usage errors. The budget is completed using 12-point Arial font. Complete the sales budget. Properly calculate by quarter and in total. 6 Complete the production budget. Properly calculate by quarter and in total. 12 Complete the direct materials budget. Properly calculate by quarter and overall total the number of kilograms needed and the cost of materials to purchase. 18 Complete the direct labour budget. Properly calculate by quarter and in total. 6 Complete the budgeted income statement. Cost of one finished bag of coffee is correctly calculated showing calculations. 3 Accurately present revenue and expenses using source data as well as relevant calculations from previous prepared budgets. Income statement is prepared in good form by quarters and in total. 14 Complete the schedule of expected collections from customers. Schedule is organized by quarters and overall budget total based on sales dollars collected. 15 Complete the cash budget. The budget is prepared in good form and clearly shows the individual cash receipts and disbursements by quarter and in total. Required borrowing and repayments and interest charges are accurately calculated and included in the correct quarter. 22 Complete the financial statement analysis. Provide a definition of each category. Categorize ratios for 2021 & 2022 in order of liquidity, solvency and profitability. Complete the ratios for 2022 and provide commentary if the ratio is better, worse or the same compared to 2021. Provide a conclusion overall for each category and indicate any recommendations for improvement. 26
