Page 229 -

EXCEL SOLUTION OF HEWLITT CORPORATION FINANCIAL PLANNING PROBLEM 209

For this problem, some of the left-hand-side cells reference other cells that contain
formulas. These referenced cells provide Hewlitt’s cash flow in and cash flow out for
1
each of the eight years. The cells and their formulas are as follows:
Cell E21 ¼ A17

Cell E22 ¼ SUMPRODUCTð$E$7 :$G$7; $B$17 :$D$17Þþ $F$10 E17

Cell E23 ¼ SUMPRODUCTð$E$7 :$G$7; $B$17 :$D$17Þþ $F$10 F17

Cell E24 ¼ SUMPRODUCTð$E$7 :$G$7; $B$17 :$D$17Þþ $F$10 G17

Cell E25 ¼ SUMPRODUCTð$E$7 :$G$7; $B$17 :$D$17Þþ $F$10 H17

Cell E26 ¼ð1 þ E7Þ B17 þ F7 C17 þ G7 D17 þ F10 I17

Cell E27 ¼ð1 þ F7Þ C17 þ G7 D17 þ F10 J17

Cell E28 ¼ð1 þ G7Þ D17 þ F10 K17
Cell F21 ¼ SUMPRODUCT(E6:G6,B17:D17) þ E17
Cell F22 ¼ F17
Cell F23 ¼ G17
Cell F24 ¼ H17
Cell F25 ¼ I17
Cell F26 ¼ J17
Cell F27 ¼ K17
Cell F28 ¼ L17
Right-Hand Sides The right-hand sides for the eight constraints represent the
annual cash requirements. They are placed into cells I21:I28.
Cell I21 = B5 (Copy to I22:I28)
Excel Solution

We are now ready to use the information in the spreadsheet to determine the
optimal solution to the Hewlitt Corporation problem. The following steps describe
how to use Excel to obtain the optimal solution.
Step 1. Select the Tools menu
Step 2. Select the Solver option
Step 3. When the Solver Parameters dialog box appears:
Enter B20 in the Set Cell box
Select the Equal to: Min option
Enter A17:L17 in the By Changing Cells box
Choose Add
Step 4. When the Add Constraint dialog box appears:
Enter G21:G28 in the Cell Reference box
Select ¼
Enter I21:I28 in the Constraint box
Click OK
Step 5. When the Solver Parameters dialog box appears:
Choose Options
Step 6. When the Solver Options dialog box appears:
Select Assume Non-Negative
Click OK
Step 7. When the Solver Parameters dialog box appears:
Choose Solve

1
The cash flow in is the sum of the positive terms in each constraint equation in the mathematical model, and the
cash flow out is the sum of the negative terms in each constraint equation.

Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.