Instructions: In groups of 2-3, do the following case study in Excel. Make sure to label your answers, Make sure to answer each question, including the subquestions (often lettered). Iggy's Ice Cream Shop, a small-sized, family-owned ice cream parlor established in 2012, located in a small coastal town known for its seasonal tourism. Iggy's specializes in offering a variety of homemade, unique ice cream flavors that have successfully attracted both tourists and locals, resulting in substantial peak-season cash inflows. However, the business grapples with significant cash flow issues during the off-peak months, from October to April, due to reduced foot traffic and the town's dormant tourist scene. The owner has considered closing during the off season, but with operating costs staying high it doesnt seem like the best option. So instead, hes hired a small team (you) to help him figure out the problem. Problem Statement As a seasonal business, Iggy's enjoys tremendous revenue inflows during the peak summer season, between May and September. Nonetheless, maintaining operations during the offseason is proving financially challenging. The business finds it difficult to manage fixed expenses such as rent, wages, insurance, and machinery maintenance, and variable costs like ingredients procurement. Their current financial statements as of the end of the fiscal year are as follows: Income Statement (In $) FY 2022 Sales 450,000 Cost of Goods Sold 130,000 Gross Profit 320,000 Operating Expenses 150,000 Net Income 170,000 Balance Sheet (In $) FY 2022 Assets Cash 10,000 Accounts Receivable 5,000 Inventory 15,000 Total Current Assets 30,000 Property, Plant & Equipment 120,000 Total Assets 150,000 Liabilities and Owner's Equity Accounts Payable 12,000 Notes Payable 30,000 Total Current Liabilities 42,000 Long-term Liabilities 50,000 Owner's Equity 58,000 Total Liabilities & Owner's Equity 150,000 Objective The purpose of this case study is to address Iggy's Ice Cream Shop's seasonal cash flow problems, identify potential solutions to ensure sufficient liquidity throughout the year, and provide strategic recommendations for effective working capital management. Discussion Questions 1. Analyze Iggy's financial statements. What do they suggest about the business's liquidity, operational efficiency, and credit management? 2. How might the seasonal nature of the business impact its current and quick ratios? 3. What are some strategies Iggy's can implement to manage its cash flows throughout the year? Consider factors such as inventory management, accounts receivable, and accounts payable. 4. Suggest a financial model or strategy that could help alleviate the offseason financial burden. Justify your answer with financial reasons. Month Sales Cost of Goods Sold Gross Profit Operating Expenses Net Income January 10,000 2,900 7,100 12,500 -5,400 February 10,000 2,900 7,100 12,500 -5,400 March 15,000 4,350 10,650 12,500 -1,850 April 20,000 5,800 14,200 12,500 1,700 May 45,000 13,050 31,950 12,500 19,450 June 75,000 21,750 53,250 12,500 40,750 July 85,000 24,650 60,350 12,500 47,850 August 75,000 21,750 53,250 12,500 40,750 September 55,000 15,950 39,050 12,500 26,550 October 25,000 7,250 17,750 12,500 5,250 November 20,000 5,300 14,700 12,500 2,200 December 15,000 4,350 10,650 12,500 -1,850 450,000 130,000 320,000 150,000 170,000 Iggys predicts that sales next year will be identical, and has forecasted the next years sales and costs to be the same. However, they want to take a more conservative approach, and want to keep a cash balance of $5000 every month. They also want to borrow instead of taking it out of equity. Assume that Iggys can borrow money at 7.5% annual rate. 5. Based on the monthly income statements and Iggy's desire to maintain a minimum cash balance of $5,000, identify the months where Iggy's faces cash flow problems. a. How much does the company need to borrow each of these months to cover its expenses and maintain the minimum cash balance? 6. Calculate the total amount of interest that Iggy's would have to pay over the year if they borrow each month as required* at an annual interest rate of 7.5%. 7. Given Iggy's net income for the year, would it be more beneficial for the company to borrow to cover cash shortfalls, or should they use their own earnings? a. Consider the cost of borrowing and the need to maintain a cash balance. *Note that Iggys will borrow enough to keep a cash balance of $5000 at all times and pay back as much of the loan as possible while maintaining the same cash balance. Iggys has big plans for 2024. He took a working capital management class and now he wants to put some of the principals he has learned to the test. First, he predicts sales will increase 10% while operating cost will increase at 3%. COGS will increase by 3% as well. 8. What would be the new monthly sales for Iggy's Ice Cream in the second year, assuming a 10% increase in sales? 9. What would be the new monthly operating expenses, assuming a 3% increase? 10. What will be the new net income per month, given the changes in sales and operating expenses? 11. If Iggy's reduces its working capital safety net from $5000 to $2000 and invests the additional unneeded profits into 6-month T-bills with an annual returns of 4.89%, how much additional income can they generate from these investments per month? 12. Given the above changes, what would the new monthly cash flow situation look like for Iggy's, considering the increased interest rate of 9%, the previous debt of $6850, and the income generated from T-bills? 13. In December, you have the choice to use debt or equity. Which do you choose and why? Iggy's Ice Cream: The Inventory Conundrum Iggy's Ice Cream is pretty stoked about its new sales forecast. The business is seasonal, with bustling sales during the summer and a slow pace in the winter. It generally does about 70% cash/debit and 30% in credit (often for events and youll need this 30% credit number later). Over time, Iggy's Ice Cream has started facing a significant problem inventory management. In the case of Iggy's, it usually takes about 30 days to sell its inventory, another 30 days to collect receivables, and Iggy's pays its suppliers in 45 days. During the peak summer months, Iggy's anticipates high demand and orders large quantities of ice cream. This strategy often leads to overstocking, as actual sales rarely match these high projections. As a result, the shop ends up discounting the ice cream heavily to clear out the excess inventory by the end of the summer. In contrast, during the winter, Iggy's tends to understock, assuming slower sales. However, the shop often runs out of popular products like ice cream cakes, vanilla ice cream, and mint chocolate chip ice cream. This mismatch between demand and inventory has led to lost sales and unhappy customers. The main challenge is finding the right balance. How can Iggy's efficiently manage its inventory to avoid overstocking during summer and understocking during winter? Let's take a look at a chart representing the monthly balances of Assets, including Cash, Inventory, and Accounts Receivable, and Liabilities. Month Cash Inventory Accounts Receivable Accounts Payable Sales COGS Jan $5,000 $4,000 $2,000 $1,500 $15,000 $10,000 Feb $5,000 $4,500 $2,500 $1,750 $15,000 $10,000 Mar $5,000 $5,500 $3,000 $2,000 $20,000 $13,500 Apr $5,000 $7,000 $3,500 $2,500 $25,000 $17,000 May $5,000 $10,000 $4,500 $3,000 $30,000 $20,000 Jun $5,000 $15,000 $5,500 $3,500 $35,000 $23,500 Jul $5,000 $18,000 $7,000 $4,000 $40,000 $27,000 Aug $5,000 $14,000 $8,000 $4,500 $35,000 $23,500 Sep $5,000 $10,000 $6,500 $3,500 $30,000 $20,000 Oct $5,000 $7,000 $5,000 $2,500 $25,000 $17,000 Nov $5,000 $5,500 $4,000 $2,000 $20,000 $13,500 Dec $5,000 $4,000 $3,000 $1,500 $15,000 $10,000 14. Calculate the monthly Inventory Turnover Ratio (COGS divided by average inventory) for Iggy's. a. Which months exhibit the best and worst inventory management according to this ratio? 15. Calculate the monthly Receivables Turnover Ratio (Sales divided by average accounts receivable) and evaluate Iggy's collection efficiency. 16. Calculate the monthly Payables Turnover Ratio (COGS divided by average accounts payable) and comment on how effectively Iggy's pays its suppliers. 17. Determine the monthly Cash Conversion Cycle (Days Inventory Outstanding + Days Sales Outstanding - Days Payables Outstanding). a. How is this cycle affecting Iggy's cash flow b. How could this impact their borrowing requirements? 18. Come up with a new inventory plan using the level production method. a. Forecast how changes in Iggy's inventory management would affect these ratios and the Cash Conversion Cycle. 19. Propose and evaluate other potential strategies that Iggy's could use to improve its inventory management, considering the impacts on the Cash Conversion Cycle and other financial ratios. 20. Do you think Iggys should work off of a high or low working capital model. Certainly, they should be somewhere in the middle, but which way should Iggy lean? If you were the working capital manager giving Iggy advice for the NEXT year, what would you tell him?