4. You were recently hired to improve the financial condition of Idaho Springs Hardware, a small chain of three hardware stores in Colorado. On your first day the owner, Chuck Vitaska, told you that the biggest problem facing the firm has been periodic unexpected cash shortages that have made it necessary for him to delay wage payments to his employees. Having recently received a degree in finance, you immediately realize that your first priority is to develop a cash budget and to arrange for a short-term borrowing agreement with the firm's bank. After looking at the firm's past financial records, you developed a sales forecast for the remainder of the year, as is presented in the following table. Month June 2021 July August September Sales $119,350 140,525 146,300 134,750 Month October November December Sales 113,575 90,475 78,925 In addition to the seasonality of sales, you have observed several other patterns. Individuals account for 40% of the firm's sales, and they pay in cash. The other 60% of sales are to contractors with credit accounts, and they have up to 60 days to pay. As a result, about 20% of sales to contractors are paid one month after the sale, and the other 80% is paid two months after the sale. Each month the firm purchases inventory equal to about 50% of the following month's sales. About 30% of this inventory is paid for in the month of delivery, while the remaining 70% is paid one month later. Each month the company pays its hourly employees a total of $16,000, including benefits. Its salaried employees are paid $22,000, also including benefits. In the past, the company had to borrow to build its stores and for the initial inventories, This debt has resulted in monthly interest payments of $6,000 and monthly principal payments of $450. On average, maintenance at the stores is expected to cost about $1,200 per month, except October to December when snow removal costs will add about $400 per month. Sales taxes are 7% of quarterly sales and must be paid in June, September, and December. Other taxes are also paid during those months and are expected to be about 4% of quarterly sales in each of those months. The owner wishes to maintain a cash balance of at least $12,000 to limit the risk of cash shortages. The cash balance at the end of May is expected to be $15,000 (before any borrowing or investing). a. Create a simple cash budget for Idaho Springs Hardware for June to Decem- ber. Note that your records indicate that sales in April and May were $98,175 and $109,725, respectively, January 2022 sales are expected to be $69,300. What would be the ending cash balances if the firm does not borrow to main- tain its $12,000 minimum b. Now assume that the firm can borrow from the bank at a rate of 6% per annum to maintain its liquidity and meet its required minimum cash balance. In addition, if the firm has funds in excess of the minimum, it will use the excess to pay off any previous balance. c. While negotiating a line of credit, the firm's bank offered to sweep any cash in excess of the minimum into a money market fund that will return an average of 4% per year after expenses. If you accept this offer, how will it affect the firm's ending cash balances and need to borrow in each month? Note that the firm must have paid off all short-term loans before any excess cash can be invested, and invested funds will be used instead of borrowing when needed. d. After completing the cash budget, you begin to think of ways to reduce the firm's borrowing needs. One idea that comes to mind is changing the firm's credit policy with contractors because they seem to always pay at the last minute. Three scenarios come to mind: (1) In the best case, contractors are required to pay for 100% of their purchases during the month after the sale. You believe that this would cause a 5% decline in sales. (2) In the base case, everything remains as already outlined. (3) In the worst case, contractors would be required to pay for 100% of their purchases during the month after the sale, and you believe that this would cause a 20% drop in sales. You decide to use the Scenario Manager to evaluate these scenarios. To summarize the impact of the change, you will examine the impact on the firm's maximum borrowing needs and cumulative net interest cost (after accounting for investment earnings). In your opinion, should the firm change its credit policy? 4. You were recently hired to improve the financial condition of Idaho Springs Hardware, a small chain of three hardware stores in Colorado. On your first day the owner, Chuck Vitaska, told you that the biggest problem facing the firm has been periodic unexpected cash shortages that have made it necessary for him to delay wage payments to his employees. Having recently received a degree in finance, you immediately realize that your first priority is to develop a cash budget and to arrange for a short-term borrowing agreement with the firm's bank. After looking at the firm's past financial records, you developed a sales forecast for the remainder of the year, as is presented in the following table. Month June 2021 July August September Sales $119,350 140,525 146,300 134,750 Month October November December Sales 113,575 90,475 78,925 In addition to the seasonality of sales, you have observed several other patterns. Individuals account for 40% of the firm's sales, and they pay in cash. The other 60% of sales are to contractors with credit accounts, and they have up to 60 days to pay. As a result, about 20% of sales to contractors are paid one month after the sale, and the other 80% is paid two months after the sale. Each month the firm purchases inventory equal to about 50% of the following month's sales. About 30% of this inventory is paid for in the month of delivery, while the remaining 70% is paid one month later. Each month the company pays its hourly employees a total of $16,000, including benefits. Its salaried employees are paid $22,000, also including benefits. In the past, the company had to borrow to build its stores and for the initial inventories, This debt has resulted in monthly interest payments of $6,000 and monthly principal payments of $450. On average, maintenance at the stores is expected to cost about $1,200 per month, except October to December when snow removal costs will add about $400 per month. Sales taxes are 7% of quarterly sales and must be paid in June, September, and December. Other taxes are also paid during those months and are expected to be about 4% of quarterly sales in each of those months. The owner wishes to maintain a cash balance of at least $12,000 to limit the risk of cash shortages. The cash balance at the end of May is expected to be $15,000 (before any borrowing or investing). a. Create a simple cash budget for Idaho Springs Hardware for June to Decem- ber. Note that your records indicate that sales in April and May were $98,175 and $109,725, respectively, January 2022 sales are expected to be $69,300. What would be the ending cash balances if the firm does not borrow to main- tain its $12,000 minimum b. Now assume that the firm can borrow from the bank at a rate of 6% per annum to maintain its liquidity and meet its required minimum cash balance. In addition, if the firm has funds in excess of the minimum, it will use the excess to pay off any previous balance. c. While negotiating a line of credit, the firm's bank offered to sweep any cash in excess of the minimum into a money market fund that will return an average of 4% per year after expenses. If you accept this offer, how will it affect the firm's ending cash balances and need to borrow in each month? Note that the firm must have paid off all short-term loans before any excess cash can be invested, and invested funds will be used instead of borrowing when needed. d. After completing the cash budget, you begin to think of ways to reduce the firm's borrowing needs. One idea that comes to mind is changing the firm's credit policy with contractors because they seem to always pay at the last minute. Three scenarios come to mind: (1) In the best case, contractors are required to pay for 100% of their purchases during the month after the sale. You believe that this would cause a 5% decline in sales. (2) In the base case, everything remains as already outlined. (3) In the worst case, contractors would be required to pay for 100% of their purchases during the month after the sale, and you believe that this would cause a 20% drop in sales. You decide to use the Scenario Manager to evaluate these scenarios. To summarize the impact of the change, you will examine the impact on the firm's maximum borrowing needs and cumulative net interest cost (after accounting for investment earnings). In your opinion, should the firm change its credit policy