You were recently hired to improve the financial condition of Idaho Springs Hardware, a small chain of

Question:

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.
You were recently hired to improve the financial condition of

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 45% 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 $10,000, including benefits. Its salaried employees are paid $14,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 $4,000 and monthly principal payments of $221. On average, maintenance at the stores is expected to cost about $800 per month, except October to December when snow removal costs will add about $200 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 December. Your records indicate that sales in April and May were $56,100 and $62,700, respectively. January 2016 sales are expected to be $39,600. What would be the ending cash balances if the firm does not borrow to maintain 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? 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 further 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?

Cash Budget
A cash budget is an estimation of the cash flows for a business over a specific period of time. These cash inflows and outflows include revenues collected, expenses paid, and loans receipts and payment.  Its primary purpose is to provide the...
Fantastic news! We've Found the answer you've been seeking!

Step by Step Answer:

Related Book For  book-img-for-question

Financial Analysis with Microsoft Excel

ISBN: 978-1285432274

7th edition

Authors: Timothy R. Mayes, Todd M. Shank

Question Posted: