Develop a cash budget model for XYZ Corporation to project the month-end cash balances for June to September based on sales, purchases, wages, lease payments, interest payments, dividend payout, and tax payments. Modify the model to incorporate refinements such as maintaining a minimum cash balance of $15,000 and investing any excess cash balance above the target level earning 6% annual interest income.
| 2 pages
| 854 words
| 224 views
Trusted by 2+ million users, 1000+ happy students everyday
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 inthe 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 bechanged 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 projectionsfor all of these lines. The printouts you submit must look like the templates with any rowsand 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. Thelogic 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
Found this document preview useful?
You are reading a preview Upload your documents to download or Become a Desklib member to get accesss