Loblaw Manufacturing has asked you to create a cash budget to determine its financing needs for the period June through October. You have collected the following information.

 

Month sales other payments
June 2023        498,125 $272,500
July                  430,812,231,281
August            344.093 199.062
September     261,125 124,188
October            246,468 172,750
November 2     03,343   Other data: Sales for April and May were $372,031 and $338,906, respectively. The company collects 25% of its sales during the month of the sale, 45% the following month, and 30% two months after the month of the sale.    Each month it buys inventory equal to 60% of the expected sales of the next month, the company pays 45% of its inventory purchases in the same month of the purchase and the remaining 55% in the following month. The company's suppliers give you a discount of 3% of the total paid during the same month of purchase.    The company maintains a minimum cash balance of $25,500 each month, and pays 5.5% interest annually on short-term loans with its bank.   REQUIRED: Prepare a cash budget for the period from June to October 2023. The budget should take into account short-term loans, outstanding loan payments, as well as interest payments. The company ended May with an unadjusted cash balance of $30,000 Bob Loblaw, the president, is considering extending his inventory payments. He believes it may be less expensive to "borrow" from suppliers than from the bank. In other words, pay them later than is currently the case. Bob has asked you to use the Scenario Manager tool in Excel to evaluate 4 possible scenarios. You have to use "Scenario Manager" to determine what the total interest cost would be for this time period if the company paid (1) 0%, (2) 20%, (3) 35%, or (4) 45% of your inventory purchases in the same month of purchase. The rest would be paid in the following month. Create a summary of these four scenarios and explain whether the results support Bob's belief.