|
Cash Budgeting Review Assignment
This is an exercise in doing a monthly cash budget which is a very effective way to do short term financial planning. Every firm
should have such a plan (I know whenever an absolute statement is used it is always possible to think of an exception. In this case the chance of an exception is so rare that it's not worth worrying about.). The
completed cash budget is available in a spreadsheet file. You can get a copy of the spreadsheet file, the file is called cashbud.exe. After downloading this file double click on it and you will be asked for a
password. Supply the appropriate password and it will extract into: (Wb3 is Quattro, Wk1 is Lotus and Xls is Excel)
CASHBUD.wk1
CASHBUD.wb3
CASHBUD.xls.
The Norton Company produces a product that has the following sales (on an accrual basis) expectations for 1995:
|
Month
|
Sales ($)
|
|
May
|
150,000
|
|
June
|
150,000
|
|
July
|
300,000
|
|
August
|
450,000
|
|
September
|
600,000
|
|
October
|
300,000
|
|
November
|
300,000
|
|
December
|
75,000
|
|
January, 1996
|
150,000
|
|
February
|
170,000
|
|
March
|
180,000
|
|
Of these sales, 5% are collected during the month, 70% are collected the next month, and 25% are collected in the third month.
The company is in the process of developing the cash budget for the
July through December. The company has the following monthly expenses:
Administrative cost $50,000
Lease Payment $10,000.
The wage rate for labor is $7.50 per hour, and sales people receive a commission of 8% of sales. It has been determined that each dollar of
sales requires 2 minutes of labor. This labor is done for 10% of the sales three months away, for 80% of the sales two months away, and for 10% of sales one month away, and no labor is spent on the current
months sales. Commission is paid during the month the sale is made. Of the total wage bill for a month 80% percent is paid during the month and 20% in the following month.
The company will receive $2 million in October from the sale of securities and will make a $2 million progress payment on a new plant in November. In addition in September and December the company
will make a $50,000 installment payment on taxes. After the sale of securities the company will be required to make monthly interest payments, the securities carry a 9.125% annual interest rate.
The inventory the company purchases represents 59% of gross sales. In order to have proper inventory the company in any month purchases
65% of next month sales, 25% of the sales 2 months hence and 10%
of the current month's sales. In addition the company keeps a safety stock equal to 15% of the average sales for the next three months. Ten
percent of the inventory purchases are paid for the in the current month, with the remainder being paid in the following month.
The company has a policy that a minimum of $50,000 must be kept on
hand at the end of month. The excess is invested in 30 day marketable securities yielding 7% annually, shortages are borrowed at 9% annually and repaid as soon as possible. At the end of May the
cash balance was $110,000, of which $60,000 was invested in a 30 day security.
a. Prepare a cash budget using a computer program of your choice.
b. The company has always had very weak sales in the winter months, this time the sales manager is convinced that he can
improve the sales in December, January, February, and March by 50%, 40% 40% and 40% respectively. What impact does this have on the cash requirements of the company. Rerun your model. Explain what happen.
|
