XYZ Corporation is facing pressure from increasing costs for itsproducts as well as demands...

60.1K

Verified Solution

Question

Accounting

XYZ Corporation is facing pressure from increasing costs for itsproducts as well as demands from employees for more competitivewages. Management wants you to develop a cash budget model that itcan use to analyze the impact of various assumptions on theprojected month-end cash positions for June through September. (Acash budget model lays out the cash inflows and outflows for eachmonth to arrive at numbers of interest to a business manager suchas the projected month-end cash positions.)
You are building your model in the month of February. Butbecause you are not given all information needed to do full cashflow projections for April and May, your full projections willstart with the month of June. Do projections through the month ofSeptember.
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% incash collected the same month and 25% on credit collected the nextmonth. The company purchases its products at 50% of sales and payshalf of it in the month after purchase and the other half in themonth 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 existinglong-term debt) of $10,000 are due in June and September, adividend payout of $100,000 is scheduled for July, and tax paymentsin June and September are estimated to be $9,000 and $17,000,respectively.
Assume that at the end of May the company will have noshort-term debt or investment and a cash balance of $50,000. Foryour projections this is given input data that cannot be changedbased on anything.
Also assume that all transactions take place on the last day ofthe month and the cash balance at the beginning of any month is thesame as that at the end of the previous month.
Submit three printouts for the following 3 versions of the modelusing the templates provided. Although it should not be necessary,you can add (only a few) additional properly labeled intermediatecalculation lines to the templates. But you must do so below row 41or to the right of column I. You cannot delete or move any of thelabeled lines already shown in the templates and your model mustcalculate and show projections for all of these lines. Theprintouts you submit must look like the templates with any rows andcolumns you add shown in gray.
For parts (b) and (c) also include a description of the logicyou used in your model. The description must be concise and easy tofollow, in the form of diagrams (such as decision trees) or concisealgebraic equations, and cannot be verbal descriptions of yourlogic. The logic cannot be handwritten.
(a)         Create a model to project the month-end cash balances forJune to Sept. and show what these balances will be under the aboveassumptions. You must redo and submit this part using the templateprovided even though you may have already done it as a practiceproblem.
(No points for this part, but you must do and submitit)
(b)         Management wants to maintain a minimum cash balance of $15,000,using short-term debt if necessary. However, if at the end of anymonth the cash level is projected to be above that level, theexcess should be used to pay off any outstanding short-term debt.The company will have to pay interest (payable monthly) at anannual 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, G36using the method discussed in the class. Also make sure that yourprint out shows the row and column numbers. Otherwise one cannotfollow your formulas.
(c)          Management further wants to invest any cash balance above thetarget $15,000 level earning 6% annual interest income (paidmonthly). (Any excess cash should first be used to pay offoutstanding short-term debt. Similarly any investment should bedrawn down before incurring any short-term debt.) Modify your modelin (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 thatyour print out shows the row and column numbers. Otherwise onecannot follow your formulas.

Answer & Explanation Solved by verified expert

3.6 Ratings (466 Votes)

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 months 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 See Answer

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member

Transcribed Image Text

In: AccountingXYZ Corporation is facing pressure from increasing costs for itsproducts as well as demands from...XYZ Corporation is facing pressure from increasing costs for itsproducts as well as demands from employees for more competitivewages. Management wants you to develop a cash budget model that itcan use to analyze the impact of various assumptions on theprojected month-end cash positions for June through September. (Acash budget model lays out the cash inflows and outflows for eachmonth to arrive at numbers of interest to a business manager suchas the projected month-end cash positions.)You are building your model in the month of February. Butbecause you are not given all information needed to do full cashflow projections for April and May, your full projections willstart with the month of June. Do projections through the month ofSeptember.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% incash collected the same month and 25% on credit collected the nextmonth. The company purchases its products at 50% of sales and payshalf of it in the month after purchase and the other half in themonth 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 existinglong-term debt) of $10,000 are due in June and September, adividend payout of $100,000 is scheduled for July, and tax paymentsin June and September are estimated to be $9,000 and $17,000,respectively.Assume that at the end of May the company will have noshort-term debt or investment and a cash balance of $50,000. Foryour projections this is given input data that cannot be changedbased on anything.Also assume that all transactions take place on the last day ofthe month and the cash balance at the beginning of any month is thesame as that at the end of the previous month.Submit three printouts for the following 3 versions of the modelusing the templates provided. Although it should not be necessary,you can add (only a few) additional properly labeled intermediatecalculation lines to the templates. But you must do so below row 41or to the right of column I. You cannot delete or move any of thelabeled lines already shown in the templates and your model mustcalculate and show projections for all of these lines. Theprintouts you submit must look like the templates with any rows andcolumns you add shown in gray.For parts (b) and (c) also include a description of the logicyou used in your model. The description must be concise and easy tofollow, in the form of diagrams (such as decision trees) or concisealgebraic equations, and cannot be verbal descriptions of yourlogic. The logic cannot be handwritten.(a) Create a model to project the month-end cash balances forJune to Sept. and show what these balances will be under the aboveassumptions. You must redo and submit this part using the templateprovided even though you may have already done it as a practiceproblem.(No points for this part, but you must do and submitit)(b) Management wants to maintain a minimum cash balance of $15,000,using short-term debt if necessary. However, if at the end of anymonth the cash level is projected to be above that level, theexcess should be used to pay off any outstanding short-term debt.The company will have to pay interest (payable monthly) at anannual 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, G36using the method discussed in the class. Also make sure that yourprint out shows the row and column numbers. Otherwise one cannotfollow your formulas.(c) Management further wants to invest any cash balance above thetarget $15,000 level earning 6% annual interest income (paidmonthly). (Any excess cash should first be used to pay offoutstanding short-term debt. Similarly any investment should bedrawn down before incurring any short-term debt.) Modify your modelin (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 thatyour print out shows the row and column numbers. Otherwise onecannot follow your formulas.