Can you help me make a sales budget, direct material budget, schedule of expected collections from customers, cash budget, financial statement analysis?

Case #2 Lotus Coffee - Appendix B Comparative Financial Statements and Industry Ratios $ S Lotus Coffee Inc. Comparative Income Statement Year Ended September 30 Liabilities and Shareholders' Equity Current Liabilities Line of credit Accounts payable Current portion of long-term debt Total Current Liabilities 4,587 144,000 144,000 144,000 148,587 s 2021 1,275,000 $ 422.220 852,780 2022 1,440.000 479,424 960,576 Non-Current Liabilities Loan payable Total Liabilities 300.000 448,587 156,000 300,000 Coffee sales Cost of goods sold Gross profit Pperating expenses Staff expense General operating expense Depreciation expense Income from operations pther 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 Shareholders' Equity Common shares Retained earnings Total Shareholders' Equity Total Liabilities and Shareholders' Equity 50,000 99,294 149.294 597,881 S 50,000 187,870 237,870 S 537,870 $ 235,030 $ 268,576 Financial Ratios Lotus Coffee Inc. Comparative Balance Sheet At September 30 2021 2022 Accounts receivable turnover Asset Turnover Current ratio Debt to total assets Gross profit margin Profit (Operating) margin Return on assets Working capital Assets Current Assets Cash Accounts receivable Materials Inventory Total Current Assets 2021 6.80 2.13 1.84 75.0% 66.9% 18.4% 39.3% 125.294 s 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 537,870 s ACCT-1071 Accounting Fundamentals Group case study #2 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 packages. Images are used with pression from www.rockscecille.com 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 30. Fall 2021 Page 1 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 Depreciation expense $11,000 $11,000 $11,000 $11,000 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 prefers to have as much raw material (unroasted beans) on hand as possible. He likes to maintain a balance of raw 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 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. Fall 2021 Page 3 18 ACCT-1071 Accounting Fundamentals Group case #2: Lotus Coffee Inc. Grading Rubric Group number: Group members: 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 Times New Roman font. Complete the sales budget. Properly calculate by quarter and in total. 6 Comments: Complete the direct materials budget. Properly calculate by quarter and overall total the number of kilograms needed and the cost of materials to purchase. Comments: Complete the budgeted income statement. Accurately present revenue and expenses using source data as well as relevant calculations from previous prepared budgets. Income statement is prepared in 18 good form by quarters and in total. Comments: Complete the schedule of expected collections from customers. Schedule is organized by quarters and overall budget total based on sales dollars collected Comments: 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 20 correct quarter. 15 Comments: Complete the financial statement analysis. Categorize ratios for 2021 & 2022 in order of liquidity, solvency and profitability. Provide a definition of each category. 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 Comments: Total score * 103 1 - fx Case #2 Lotus Coffee - Appendix A A B D E F G I 1 Case #2 Lotus Coffee - Appendix A 2 3 4 5 Q2 6 Q4 Jul-Sep 40,000 $10 7 8 2023 Operating Projections Q1 Q3 Oct-Dec Jan-Mar Apr-Jun 60,000 50,000 40,000 $10 $10 $10 Estimated to be 20% of sales Estimated to be 30% of sales $11,000 $11,000 $11,000 $6.60 $6.60 $7.70 Expected package sales Unit selling price Staff costs Operating Expenses Depreciation expense Raw material cost per kilogram 9 10 11 $11,000 $7.70 12 13 14 15 16 17 18 19 20 21 22 23 24 Case #2 Lotus Coffee - Appendix B Comparative Financial Statements and Industry Ratios $ S Lotus Coffee Inc. Comparative Income Statement Year Ended September 30 Liabilities and Shareholders' Equity Current Liabilities Line of credit Accounts payable Current portion of long-term debt Total Current Liabilities 4,587 144,000 144,000 144,000 148,587 s 2021 1,275,000 $ 422.220 852,780 2022 1,440.000 479,424 960,576 Non-Current Liabilities Loan payable Total Liabilities 300.000 448,587 156,000 300,000 Coffee sales Cost of goods sold Gross profit Pperating expenses Staff expense General operating expense Depreciation expense Income from operations pther 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 Shareholders' Equity Common shares Retained earnings Total Shareholders' Equity Total Liabilities and Shareholders' Equity 50,000 99,294 149.294 597,881 S 50,000 187,870 237,870 S 537,870 $ 235,030 $ 268,576 Financial Ratios Lotus Coffee Inc. Comparative Balance Sheet At September 30 2021 2022 Accounts receivable turnover Asset Turnover Current ratio Debt to total assets Gross profit margin Profit (Operating) margin Return on assets Working capital Assets Current Assets Cash Accounts receivable Materials Inventory Total Current Assets 2021 6.80 2.13 1.84 75.0% 66.9% 18.4% 39.3% 125.294 s 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 537,870 s ACCT-1071 Accounting Fundamentals Group case study #2 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 packages. Images are used with pression from www.rockscecille.com 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 30. Fall 2021 Page 1 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 Depreciation expense $11,000 $11,000 $11,000 $11,000 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 prefers to have as much raw material (unroasted beans) on hand as possible. He likes to maintain a balance of raw 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 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. Fall 2021 Page 3 18 ACCT-1071 Accounting Fundamentals Group case #2: Lotus Coffee Inc. Grading Rubric Group number: Group members: 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 Times New Roman font. Complete the sales budget. Properly calculate by quarter and in total. 6 Comments: Complete the direct materials budget. Properly calculate by quarter and overall total the number of kilograms needed and the cost of materials to purchase. Comments: Complete the budgeted income statement. Accurately present revenue and expenses using source data as well as relevant calculations from previous prepared budgets. Income statement is prepared in 18 good form by quarters and in total. Comments: Complete the schedule of expected collections from customers. Schedule is organized by quarters and overall budget total based on sales dollars collected Comments: 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 20 correct quarter. 15 Comments: Complete the financial statement analysis. Categorize ratios for 2021 & 2022 in order of liquidity, solvency and profitability. Provide a definition of each category. 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 Comments: Total score * 103 1 - fx Case #2 Lotus Coffee - Appendix A A B D E F G I 1 Case #2 Lotus Coffee - Appendix A 2 3 4 5 Q2 6 Q4 Jul-Sep 40,000 $10 7 8 2023 Operating Projections Q1 Q3 Oct-Dec Jan-Mar Apr-Jun 60,000 50,000 40,000 $10 $10 $10 Estimated to be 20% of sales Estimated to be 30% of sales $11,000 $11,000 $11,000 $6.60 $6.60 $7.70 Expected package sales Unit selling price Staff costs Operating Expenses Depreciation expense Raw material cost per kilogram 9 10 11 $11,000 $7.70 12 13 14 15 16 17 18 19 20 21 22 23 24