XYZ Corporation Cash Budget Model

This practical assignment requires students to develop a cash budget model for XYZ Corporation, projecting month-end cash balances from June to September. The model incorporates various factors such as sales projections, cost of goods sold, wages, lease payments, interest payments, dividends, and taxes. Students must initially create a basic model, then refine it to include short-term debt management to maintain a minimum cash balance of $15,000, paying off debt with excess cash and incurring interest on outstanding debt. Finally, the model must be further enhanced to incorporate investment of excess cash above the minimum balance, earning interest income. The assignment also requires students to demonstrate their understanding of the model's logic through concise algebraic equations or diagrams and to show formulas for specific cells within the model.

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.
(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.