Land and buildings 600 Mortgage on real estate 700
Equipment 600 Common stock, $0.10 par 300
Retained earnings 500
Total assets $3,000 Total liabilities and equity $3,000
a.How much bank financing is needed to eliminate the past-due accounts payable?
b.Would you as a bank loan officer make the loan? Why or why not?
Spreadsheet Problem
Start with the partial model in the fileCh16P18BuildaModel.xlsfrom the textbook’s web site.
Helen Bowers, owner of Helen’s Fashion Designs, is planning to request a line of credit
from her bank. She has estimated the following sales forecasts for the firm for parts of 2003 and
2004:
Sales Labor and Raw Materials
May 2003 $180,000 $ 90,000
June 180,000 90,000
July 360,000 126,000
August 540,000 882,000
September 720,000 306,000
October 360,000 234,000
November 360,000 162,000
December 90,000 90,000
January 2004 180,000 NA
Collection estimates obtained from the credit and collection department are as follows: collec-
tions within the month of sale, 10 percent; collections the month following the sale, 75 percent;
collections the second month following the sale, 15 percent. Payments for labor and raw mate-
rials are typically made during the month following the one in which these costs have been in-
curred. Total labor and raw materials costs are estimated for each month as shown above.
General and administrative salaries will amount to approximately $27,000 a month; lease
payments under long-term lease contracts will be $9,000 a month; depreciation charges will be
$36,000 a month; miscellaneous expenses will be $2,700 a month; income tax payments of
$63,000 will be due in both September and December; and a progress payment of $180,000 on
a new design studio must be paid in October. Cash on hand on July 1 will amount to $132,000,
and a minimum cash balance of $90,000 will be maintained throughout the cash budget period.
a.Prepare a monthly cash budget for the last 6 months of 2003.
b.Prepare an estimate of the required financing (or excess funds) — that is, the amount of
money Bowers will need to borrow (or will have available to invest) — for each month
during that period.
c.Assume that receipts from sales come in uniformly during the month (that is, cash receipts
come in at the rate of 1/30 each day), but all outflows are paid on the 5th of the month.
Will this have an effect on the cash budget — in other words, would the cash budget you
have prepared be valid under these assumptions? If not, what can be done to make a valid
estimate of peak financing requirements? No calculations are required, although
calculations can be used to illustrate the effects.
d.Bowers produces on a seasonal basis, just ahead of sales. Without making any calculations,
discuss how the company’s current ratio and debt ratio would vary during the year
assuming all financial requirements were met by short-term bank loans. Could changes in
these ratios affect the firm’s ability to obtain bank credit?
e.If its customers began to pay late, this would slow down collections and thus increase the
required loan amount. Also, if sales dropped off, this would have an effect on the required
loan. Do a sensitivity analysis that shows the effects of these two factors on the maximum
loan requirement.
16–18
BUILD A MODEL: CASH
BUDGETING
Spreadsheet Problem 617
612 Working Capital Management
