Question

XYZ Corporation is facing pressure from increasing costs for its products as well as demands from employees for more competitive wages. Management wants you to develop a cash budget model that it can use to analyze the impact of various assumptions on the projected month-end cash positions for June through September. (A cash budget model lays out the cash inflows and outflows for each month to arrive at numbers of interest to a business manager such as the projected month-end cash positions.)

You are building your model in the month of February. But because you are not given all information needed to do full cash flow projections for April and May, your full projections will start with the month of June. Do projections through the month of September.

XYZ’s sales are projected to be as follows: April: $153,000, May: $146,000, June: $142,000, July: $210,000, August: $240,000, Sept.: $225,000, Oct.: $300,000. The company’s sales are 75% in cash collected the same month and 25% on credit collected the next month. The company purchases its products at 50% of sales and pays half of it in the month after purchase and the other half in the month after that. Purchases are made one month prior to sales.

The current wage rate is 20% of sales, lease payments are $30,000 per month, quarterly interest payments (on existing long-term debt) of $10,000 are due in June and September, a dividend payout of $100,000 is scheduled for July, and tax payments in June and September are estimated to be $9,000 and $17,000, respectively.

Assume that at the end of May the company will have no short-term debt or investment and a cash balance of $50,000. For your projections this is given input data that cannot be changed based on anything.

Also assume that all transactions take place on the last day of the month and the cash balance at the beginning of any month is the same as that at the end of the previous month.

Submit three printouts for the following 3 versions of the model using the templates provided. Although it should not be necessary, you can add (only a few) additional properly labeled intermediate calculation lines to the templates. But you must do so below row 41 or to the right of column I. You cannot delete or move any of the labeled lines already shown in the templates and your model must calculate and show projections for all of these lines. The printouts you submit must look like the templates with any rows and columns you add shown in gray.

For parts (b) and (c) also include a description of the logic you used in your model. The description must be concise and easy to follow, in the form of diagrams (such as decision trees) or concise algebraic equations, and cannot be verbal descriptions of your logic. The logic cannot be handwritten.

(a)          Create a model to project the month-end cash balances for June to Sept. and show what these balances will be under the above assumptions. You must redo and submit this part using the template provided even though you may have already done it as a practice problem.

(No points for this part, but you must do and submit it)

(b)          Management wants to maintain a minimum cash balance of $15,000, using short-term debt if necessary. However, if at the end of any month the cash level is projected to be above that level, the excess should be used to pay off any outstanding short-term debt. The company will have to pay interest (payable monthly) at an annual rate of 8% on any short-term debt. Modify your model in (a) to incorporate these refinements.

Show your formulas for the following cells G30, G32, G34, G36 using the method discussed in the class. Also make sure that your print out shows the row and column numbers. Otherwise one cannot follow your formulas.

(c)           Management further wants to invest any cash balance above the target $15,000 level earning 6% annual interest income (paid monthly). (Any excess cash should first be used to pay off outstanding short-term debt. Similarly any investment should be drawn down before incurring any short-term debt.) Modify your model in (b) to incorporate these refinements.

Show your formulas for the following cells G31, G34, G36, G38, G39 using the method discussed in the class. Also make sure that your print out shows the row and column numbers. Otherwise one cannot follow your formulas.

Answer 1

Months	April	May	June	July	August	September	October
Sales	153000	146000	142000	210000	240000	225000	300000
Sales collected in same month (sales* 75%)	114750	109500	106500	157500	180000	168750	225000
Sales collected after one month (sales* 25%)	38250	36500	35500	52500	60000	56250	75000
Purchase of product (sales *50%)	76500	73000	71000	105000	120000	112500	150000
Purchase made in months (one month before sale) (next month's purchase of product)	73000	71000	105000	120000	112500	150000
50% amount paid after one month of purchase (purchase made in months * 50%)	36500	35500	52500	60000	56250	75000
50% amount paid after two month of purchase (purchase made in months * 50%)	36500	35500	52500	60000	56250	75000
Wages paid (sales * 20%)	30600	29200	28400	42000	48000	45000	60000
Cash collection from sales
	June	July	August	September
May	36500
June	106500	35500
July		157500	52500
August			180000	60000
September				168750
Cash collection from sales	143000	193000	232500	228750
Cash paid for inventory purchase
	June	July	August	September
April	36500
May	35500	35500
June		52500	52500
July			60000	60000
August				56250
Cash collection from sales	72000	88000	112500	116250

Cash budget
Months	June	July	August	September
Beginning balance	50000	43600	-23400	18600
Collections from sales	143000	193000	232500	228750
Total cash available to spend	193000	236600	209100	247350
Less: distributions
Payment to Inventory Purchases	72000	88000	112500	116250
Wages paid	28400	42000	48000	45000
Lease payment	30000	30000	30000	30000
Interest paid on existing debt paid	10000			10000
Dividend paid		100000
Tax payment	9000			17000
Total cash distributions	149400	260000	190500	218250
Cash balance	43600	-23400	18600	29100

Cash budget
Months	June	July	August	September
Beginning balance	50000	43600	15000	18344
Collections from sales	143000	193000	232500	228750
Total cash available to spend	193000	236600	247500	247094
Less: distributions
Payment to Inventory Purchases	72000	88000	112500	116250
Wages paid	28400	42000	48000	45000
Lease payment	30000	30000	30000	30000
Interest paid on existing debt paid	10000			10000
Dividend paid		100000
Tax payment	9000			17000
Total cash distributions	149400	260000	190500	218250
Cash excess (deficiency) (Total cash available to spend - Total cash distributions)	43600	-23400	57000	28844
Minimum cash balance	15000	15000	15000	15000
Cash excess available (needed)= (cash excess (deficiency) + minimum cash balance)	28600	-38400	42000	13844
Financing
Borrowing		38400
Repayments			-38400
Interest paid			-256
Total financing	0	38400	-38656	0
Ending cash balance (Total cash available to spend - Total cash distributions +Total financing )	43600	15000	18344	28844
For month July, cash excess need is 38400, so amount borrow short term debt
Interest paid on July (38400*8%*1/12)	256

Cash budget
Months	June	July	August	September
Beginning balance	50000	15000	15000	15000
Collections from sales	143000	193000	232500	228750
Total cash available to spend	193000	208000	247500	243750
Less: distributions
Payment to Inventory Purchases	72000	88000	112500	116250
Wages paid	28400	42000	48000	45000
Lease payment	30000	30000	30000	30000
Interest paid on existing debt paid	10000			10000
Dividend paid		100000
Tax payment	9000			17000
Total cash distributions	149400	260000	190500	218250
Cash excess (deficiency) (Total cash available to spend - Total cash distributions)	43600	-52000	57000	25500
Minimum cash balance	15000	15000	15000	15000
Cash excess available (needed)= (cash excess (deficiency) + minimum cash balance)	28600	-67000	42000	10500
Financing
Borrowing		38257
Repayments			-38257
Interest paid			-255
Investment made	-28600		-3488	-10517
Investment withdrawn		28600
Interest received		143		17
Total financing	-28600	67000	-42000	-10500
Ending cash balance (Total cash available to spend - Total cash distributions +Total financing )	15000	15000	15000	15000
Ending balance must be 15000 for all month. Because of excess amount invested or shortfall amount borrowed.
Company invests 28600 excess amounts in June.
In July company need	67000
Less: interest received (28600*6%*1/12)	-143
Less: investment withdrawn	-28600
Amount borrowed	38257
Excess amount in august	42000
Less: repayment of borrowing	-38257
Less: interest paid (38257*8%*1/12)	-255
Amount invested in august	3488
Cash excess available in Sep	10500
Add: interest received (3488*6%*1/12) (rounded)	17
Amount invested in Sep	10517