"Larry wants to see a complete sales budget, direct materials budget shown in kilograms and total cost, income statement budget, schedule of cash collections and cah budget for 2023 each by quarter and in total. Larry is also curious about financial ratios."

What is the cash budget and financial ratios?

Lotus Coffee Inc. Your brother 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 sale to consumers across Canada. Lotus Coffee packages all its beans into 454-gram Images are used with permission from www.quocloccoffee.com packages. 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 20% of its total sales are paid in cash, and the remaining 80% is billed to coffee shops. Of the sales billed to coffee shops, 60% 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). 2023 Operating Projections Q1 Q2 Q3 Q4 Oct-Dec Jan-Mar Apr-Jun Jul-Sep Expected package sales 60,000 50,000 40,000 40,000 Unit selling price $10 $10 $10 $10 Staff costs Estimated to be 20% of sales Operating Expenses Estimated to be 30% of sales $11,000 $11,000 $11,000 $11,000 Depreciation expense Raw material cost per kilogram $6.60 $6.60 $7.70 $7.70 Lotus purchases its coffee beans in kilogram quantities (1000 grams = 1 kilogram). Shipments from Vietnam can be unreliable, so Larry Ca Phe Lotus prefers to have as much raw material (unroasted beans) on hand as possible. He likes to maintain a balance of raw CH 02 C Ph C Ph materials equal to approximately 60% of next quarter sales. All the roasted beans are sold in the same quarter they are made to maintain freshness. He expects |+ sales for the first quarter in 2024 to be 65,000 packages. Larry plans to replace the roasting equipment in May 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 $100,000 and will be paid for in cash. The company needs to repay $12,000 per month on the long-term non-interest-bearing loan from Larry's father. In addition, he pays himself dividends of $15,000 per month in lieu of salary. Lotus Coffee has a $250,000 line of credit that can be drawn from for short-term financing. Interest is paid quarterly, and it is calculated as 4.5% per year on the outstanding balance at the Fall 2021 Page 2 ACCT-1071 Accounting Fundamentals Group case study #2 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, direct materials budget shown in kilograms and total cost, 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, your brother 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 Times New Roman font. Submit all your work in one file. Please refer to the attached rubric to ensure that all your answers are properly presented. Case #2 Lotus Coffee - Appendix A Q1 Oct-Dec 60,000 $10 2023 Operating Projections Q2 Q3 Jan-Mar Apr-Jun 50,000 40,000 $10 $10 Estimated to be 20% of sales Estimated to be 30% of sales $11,000 $11,000 $6.60 $7.70 Q4 Jul-Sep 40,000 $10 Expected package sales Unit selling price Staff costs Operating Expenses Depreciation expense Raw material cost per kilogram $11,000 $6.60 $11,000 $7.70 Case #2 Lotus Coffee - Appendix B Comparative Financial Statements and Industry Ratios Lotus Coffee Inc. Comparative Income Statement Year Ended September 30 S 2021 1,275,000 $ 422,220 852.780 2022 1,440,000 479,424 960,576 Coffee sales Cost of goods sold Gross profit Operating expenses Staff expense General operating expense Depreciation expense Income from operations Other income and expenses Interest expense Income before income taxes 255,000 318,750 44,000 235,030 288,000 360,000 44,000 268,576 $ 235,030 $ 268,576 Lotus Coffee Inc. Comparative Balance Sheet At September 30 2021 2022 s Assets Current Assets Cash Accounts receivable Materials Inventory Total Current Assets 25,000 $ 150,000 98,881 273,881 30,000 120,000 107,870 257,870 Proptery Plant and Equipment Land Buildings (net of depreciation) Equipment (net of depreciation) Total Property, Plant and Equipment Total Assets 120,000 160,000 44,000 324,000 597,881 S 120,000 130,000 30,000 280,000 s 537,870 s S Liabilities and Shareholders' Equity Current Liabilities Line of credit Accounts payable Current portion of long-term debt Total Current Liabilities 4,587 144,000 148,587 144.000 144,000 Non-Current Liabilities Loan payable Total Liabilities 300,000 448,587 156,000 300,000 50,000 Shareholders' Equity Common shares Retained earnings Total Shareholders' Equity Total Liabilities and Shareholders' Equity ' 99,294 149,294 597,881 S 50,000 187,870 237,870 537,870 s Financial Ratios Accounts receivable turnover Asset Turnover Current ratio Debt to total assets Gross profit margin Profit (Operating) margin Return on assets Working capital 2021 6.80 2.13 1.84 75.0% 66.9% 18.4% 39.3% 125,294