You are given the data for a company for 12 months. The company uses short-term loans and marketable securities investments to bring the cash balance to the desired level at the end of each month. Short-term loans will be paid down or paid off with any excess balances and marketable securities will be used only when the loan balance is zero. Marketable securities will be sold first to supply any needed cash infusions before loans are used. The company had no marketable securities and no short-term loans at the beginning of January.

The company expects to make an outlay for new capital equipment in May, June, July, or August. Create a drop-down list in Cell L24 for the user to select the month of the outlay. Use this and other input information to complete the Cash Budget. Then complete the section that gives the balance of short-term loans at the end of each month in Row 52 and the balance of marketable securities at the end of each month in Row 53. The formulas should show positive balances when marketable securities are being used, and it should show zero when marketable securities are not being used.

Format the range F43:N43 (net cash flow) so that all values less than zero display as red numbers instead of black numbers for any values of the inputs.

Name the input cells for collections Collect0, Collect1, and Collect 2. Then create and save three scenarios using the Scenario Manager that show the company's monthly net cash flow for April - December while changing the collection rates. The GOOD scenario will use Collect0 = 50%, Collect1 = 25%, and Collect2 = 25%, the NORMAL scenario will use the base case collection rates given here, and the BAD scenario will use Collect0 = 40%, Collect1 = 10%, and Collect2 = 50% . Save a summary of the scenarios on a separate tabbed page.

INPUTS
Collections on sales in the month of the sale				45%		Capital Outlay for New Equipment				$ 125,000
Collections on sales in the month following the sale				20%
Collections on sales in the second month following the sale				35%		Month of the Capital Outlay
Cash Operating Expenses as a Percentage of Current Month Sales				32%		Desired End-Of-Month cash balance				$ 30,000
	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Expected Sales	$ 15,000	$ 16,825	$ 17,425	$ 16,250	$ 15,540	$ 17,985	$ 20,500	$ 23,480	$ 22,250	$ 19,670	$ 23,400	$ 24,980
Collections on Sales
Cash Operating Expenses
Fixed Expenses				$ 3,000	$ 3,000	$ 3,000	$ 3,000	$ 3,000	$ 3,000	$ 3,000	$ 3,000	$ 3,000
Lease Payments				$ 1,250	$ -	$ -	$ 1,250	$ -	$ -	$ 1,250	$ -	$ -
Capital Outlay				$ -					$ -	$ -	$ -	$ -
Tax Payments				$ 850	$ -	$ -	$ 980	$ -	$ -	$ 1,025	$ -	$ -
Total Cash Outflows
CASH BUDGET
			Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Beginning Cash Balance
Net Cash Flow
Unadjusted Ending Cash Balance
Adjustment Needed
Ending Cash Balance			22,500
Short-Term Loans and Marketable Securities
			Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Short-Term Loans Outstanding			-
Marketable Securities			-

Using the outputs of the cash budget on the previous page, create a verticle column chart on this page that shows the balances of outstanding short term loans and marketable securities by month for April - December. These are two separate data series. Short term loan balances should display BELOW the zero line (as negative values) and marketable securities balances should diplay above the zero line (as positive values). FORMAT the chart so that it is self-explanatory and professional in appearance. Set the Y-axis maximum to $60,000 and the minimum to -$120,000 with major unit divisions set to $20,000. FORMAT the chart so that it is self-explanatory and professiona and appropriatel in appearance. Include a legend for the two series that displays at the bottom of the chart.