Question

he ZapCon Company is considering investing in three projects. If it fully invests in a project, the realized cash flows (in millions of dollars) will be as listed in the file P04_99.xlsx. For example, project 1 requires a cash outflow of $3 million today and returns $5.5 million 36 months from now. Today, ZapCon has $3 million in cash. At each time point (0, 6, 12, 18, 24, and 30 months from today), the company can, if desired, borrow up to $5 million at 3.5% (per six months) interest. Leftover cash earns 2% (per six months) interest. For example, if after borrowing and investing at time 0, ZapCon has $1 million, it would receive $20,000 in interest 6 months from now. The company’s goal is to maximize cash on hand after cash flows three years from now are accounted for. What investment and borrowing strategy should it use? Assume that the company can invest in a fraction of a project. For example, if it invests in 0.5 of project 3, it has, for example, cash outflows of −$1 million now and 6 months from now.

ZapCon cash flows (in $ millions)
	Months from now
Project	0	6	12	18	24	30	36
1	-$3.0	-$1.0	-$1.8	$0.4	$1.8	$1.8	$5.5
2	-$2.0	-$0.5	$1.5	$1.5	$1.5	$0.2	$1.0
3	-$2.0	-$2.0	-$1.8	$1.0	$1.0	$1.0	$5.0

Answer 1

Answer:

We enter the given information into the following modeling spreadsheet: Investing and borrowing plan Range names used Note: Units are in millions of dolars Cash aft Model!F32:F38 Inputs Proportic ModellB26B28 Maximun ModellD26:D28 AmountModellB19 H19 Maximun ModellB21 H21 Final cas Model!B40 amou $2.00 Borrowing 1 Interest rate Interest rate $2.00 3% 3.5% Cash flows on projects Months from now 12 30 Project 18 S3.00 -S1.00 -$1.80 S0.40 $1.80 S1.80$5.50 S2.00 -S0.50 S1.50 S1.50 S1.50 S0.20 -$1.00 S2.00 -S2.00 -$1.80 $1.00 S1.00 S1.00 $6.00 24 36 Borrowing plan Months from now 12 30 36 18 24 Amount bo $1.00 $1.00 $0.00 $0.00 S0.00 S0.00 $0.00 Maximuma $2.00 $2.00 $2.00 $2.00 $2.00 S2.00 S2.00

Investment decisions Proportion funded Maximum proportion Project 1 0.20<1.00 2 0.30<1.00 3 0.20<1.00 Constraints on cash balance Months fror Beginning Cash bor Investme Borrowe Cash after investing 12 18 24 30 36 C0 Final cash Objective to maximize: final cash at end of month 36

Here we have started by entering arbitrary values into Proportion funded and Amount borrowed.

Next, we calculate the cash balances and flows. We start this by entering -B4 Into cell B32. Then, we link to the cash borrowed by entering into C32 the following formula -B19 And dragging this to C33 C38. Then, to calculate the investment cash flow, we enter into D32 SUMPRODUCT (B26:B28,B12 B14) And drag this to D33:D38. Then, the borrowed balance will be the previous year's balance, with interest, plus the new amount borrowed. So, we enter into E32 -B19 Then, we enter into E33 E32 (1+SBS7)+C19 And drag this to E34 E38.

Then, to find the cash balance we enter into cell F32 B32+C32+D32 And drag this to the cell range F33:F38. Next, we enter F32*SBSG Into cell B33 and drag this to the cell range B34:B38. Finally, we enter the final cash in cell B40 with the following formula F38-E38

This yields the following modeling spreadsheet Investing and borrowing plan Range names used Cash afte-ModellF32:F38 Proportio-Model!B26:B28 Maximum ModellD26:D28 Amount 1-ModellB19H19 Maximum ModellB21:H21 Final cas-ModellB40 Note: Units are in millions of dollars Inputs Initial amount S2.00 Borrowing lin $2.00 Interest rate ( 3% Interest rate ( 3.5% Cash flows on projects Months from now 12 Project 18 S3.00 $1.00 $1.80 S0.40 S1.80 $1.80 S5.50 S2.00 -S0.50 S1.50 S1.50 S1.50 S0.20 S1.00 S2.00 -S2.00 -$1.80 S1.00 $1.00 $1.00 S6.00 30 24 36

Borrowing plan Months from noww 12 18 24 30 36 Amount bom $1.00 S1.00 0.00 S0.00 S0.00 S0.00 S0.00 Maximum am $2.00 S2.00 S2.00 $2.00 $2.00 $2.00 S2.00 Investment decisions Project 0.20 0.30 0.20 1.00 1.00 1.00 2

Constraints on cash balance Months from Beginning Cash bor Investmer Borrowec Cash after investing $2.00 $1.00-$1.60 $1.00 $1.40 > S0.04 S1.00 -S0.75 S2.04 S0.29 12 S0.01 S0.00 S0.27 S2.11 -S0.26 0 18 24 30 36 S0.02 S0.00 $2.00 $2.42 $2.02 0 S0.0 S0.00 S0.73 S2.18 S0.72 S0.02 S0.00 S1.01 S2.26 S1.03 S0.03 S0.00 $0.62 $2.34 S0.65 Final cash -S0.40 < Objective to maximize: final cash at end of month 36

Now, we will use Solver to determine the maximum cash on hand for the company given the investment. Thus, the objective cell will be Final_cash and our changing cells will be Proportion_funded and Amount_borrowed. Then, in order to ensure that the amount borrowed is less than or equal to the maximum amount, we add the following constraint to solver 23 Change Constraint Constraint: Maximum-amount Cel Reference: Amount borrowed <= Add Cancel Then, we also need to ensure that the proportion funded is less than or equal to the maximum with the following Solver constraint Change Constraint Cel Reference: Constraint: Proportion-funded! 国に 11 Maximumproportion OK

Then, we need to require that the cash at the end of each six months is nonnegative with the following constraint: Change Constraint Constraint Cash_after investing 0 Cancel

This yields the following Solver dialog box: Solver Parameters Final_cash Set Objective: To: o Max Yalue Of By Changing Variable Cels: Proporton funded, Amount borrowed Subject to the Constraints: Amount_borrowed< Maximum amount Cosh_after_investing>-0 Add Change Delete Proportion funded-Maximum proportion Beset All Load/Save Make Unconstrained Variables Non-Negative Sclect a Solving Method: Simplex LP Optons Solving Method Select the GRG Noninear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for lnear Solver Problems, and select the Evolutonary engine for Solver problems that are Solve Hep Close

Which yields the following optimal solution: Investing and borrowing plan Range names used Note: Units are in millions of dol Cash after-Model!F32 F38 Inputs Initial amou $2.00 Borrowing1 $2.00 Interest rate 3% Interest rate 3.5% Proportior-Model!B26B28 Maximum-Model!D26D28 Amount b-ModellB19-H19 Maximum-ModellB21-H21 Final_cash Model B40 Cash flows on projects Months from now 18 $3.00 -$1.00 $1.80 $0.40 $1.80 $1.80 S5.50 $2.00 0.50 $1.50 S1.50 $1.50 S0.20 $1.00 S2.00 -$2.00 $1.80 $1.00S1.00 $1.00 S6.00 Project 12 24 30 36 Borrowing plan Months from now 36 Amount bo $0.00 S2.00 $1.80 SO.00 S0.00 S0.00 $0.00 12 18 24 30 Maximuma $2.00 $2.00 S2.00 $2.00 S2.00 S2.00 S2.00

Investment decisions 1.00 1.00 1.00 0.00 0.00 1.00 Constraints on cash balance Months froBeginning Cash borr Investment BorroweCash after investing $2.00S0.00 $2.00 $0.00 S0.00 0.00 $2.00 $2.00 $2.00 S0.00 0.00 $1.80 -$1.80 $3.87 S0.00 0.00 S0.00 $1.00 S4.01 $1.00 S0.03 0.00 $1.00 S4.15 S1.03>- S0.03 S0.00 $1.00 $4.29 1.03 >- 0.03 S0.00 S6.00 $4.44 $6.03 12 18 24 30 36 0 Final cash S1.59 Objective to maximize: final cash at end of month 36 Therefore, the maximum cash on hand at the end of three years, after paying back loans, is