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

Question:

You have recently been hired to improve the financial condition of Idaho Springs Hardware, a small chain of three hardware stores in the mountain communities of 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 which is presented in the following table.

In addition to the seasonality of sales, you have observed several other patterns. Individuals account for about 40% of the firmís sales, and they pay in cash. The other 60% of sales are to contractors with credit accounts, and due to the credit policy, 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 $9,000, including benefits. Its salaried employees are paid $12,000, also including benefits. In the past the company has 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 $700 per month, except in the October to December period 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. Note that your records indicate that sales in April and May were $51,000 and $57,000, respectively.
January 2006 sales are expected to be $36,000. What would the ending cash balances be 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 9% 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 bankís cash management department offered to sweep any cash in excess of the minimum into a money market fund which 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 your 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?

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 2002

ISBN: 9780324178241

3rd Edition

Authors: Timothy R. Mayes, Todd M. Shank

Question Posted: