# Break Even Solver

Shared by:
Categories
Tags
-
Stats
views:
0
posted:
9/30/2011
language:
English
pages:
8
Document Sample

```							                                        Break Even (Goal Seek)

Finley    Langstrom
Sprocket    Wrench
Price               \$24.95     \$19.95
Units                     1          1
Revenue                \$25        \$20

Unit Cost          \$12.50       \$9.50
Variable Costs        \$13         \$10
Fixed Costs      \$100,000     \$75,000
Total Costs      \$100,013     \$75,010

Product Profit    (\$99,988)   (\$74,990)

Total Profit     (\$174,977)

Page 1
Break Even (Solver)

Finley Sprocket      Langstrom Wrench
Price            24.95                 19.95
Units            1                     1
Revenue          =B3 * B4              =C3 * C4

Unit Cost        12.5                  9.5
Variable Costs   =B7 * B4 - C4         =C7 * C4 - B4
Fixed Costs      100000                75000
Total Costs      =B8+B9                =C8+C9

Product Profit   =B5-B10               =C5-C10

Total Profit     =B12+C12

Page 2
Break Even (Solver)

Break-Even Model
Target Cell:       =\$B\$14=0
Changing Cells:    =COUNT(\$B\$4,\$C\$4)
Constraint #1:     =\$B\$12=0
Constrant #2:      =\$C\$12=0
Solver Options:    ={100,100,0.000001,0.05,FALSE,TRUE,FALSE,1,1,1,0.0001,FALSE}

Page 3
Worksheet: [Solver.xls]Transportation Problem
Report Created: 2/28/2004 6:03:18 PM

Target Cell (Min)
Cell          Name             Original Value     Final Value
\$B\$20 Shipping Total: San Fran                83            3200

Cell         Name              Original Value     Final Value
\$B\$7 S. Carolina San Fran                      1              0
\$C\$7 S. Carolina Denver                        1              0
\$D\$7 S. Carolina Chicago                       1             80
\$E\$7 S. Carolina Dallas                        1              0
\$F\$7 S. Carolina N.Y.                          1            220
\$B\$8 Tennessee San Fran                        1              0
\$C\$8 Tennessee Denver                          1              0
\$D\$8 Tennessee Chicago                         1            100
\$E\$8 Tennessee Dallas                          1            160
\$F\$8 Tennessee N.Y.                            1              0
\$B\$9 Arizona San Fran                          1            180
\$C\$9 Arizona Denver                            1             80
\$D\$9 Arizona Chicago                           1             20
\$E\$9 Arizona Dallas                            1              0
\$F\$9 Arizona N.Y.                              1              0

Constraints
Cell          Name                Cell Value        Formula          Status        Slack
\$G\$7 S. Carolina TOTALS                     300    \$G\$7<=\$H\$7      Not Binding   9.999999999
\$G\$8 Tennessee TOTALS                       260    \$G\$8<=\$H\$8      Binding                  0
\$G\$9 Arizona TOTALS                         280    \$G\$9<=\$H\$9      Binding                  0
\$B\$10 TOTALS San Fran                       180    \$B\$10=\$B\$11     Not Binding              0
\$C\$10 TOTALS Denver                           80   \$C\$10=\$C\$11     Not Binding              0
\$D\$10 TOTALS Chicago                        200    \$D\$10=\$D\$11     Not Binding              0
\$E\$10 TOTALS Dallas                         160    \$E\$10=\$E\$11     Not Binding              0
\$F\$10 TOTALS N.Y.                           220    \$F\$10=\$F\$11     Not Binding              0
\$B\$7 S. Carolina San Fran                      0   \$B\$7>=0         Binding                  0
\$C\$7 S. Carolina Denver                        0   \$C\$7>=0         Binding                  0
\$D\$7 S. Carolina Chicago                      80   \$D\$7>=0         Not Binding             80
\$E\$7 S. Carolina Dallas                        0   \$E\$7>=0         Binding                  0
\$F\$7 S. Carolina N.Y.                       220    \$F\$7>=0         Not Binding            220
\$B\$8 Tennessee San Fran                        0   \$B\$8>=0         Binding                  0
\$C\$8 Tennessee Denver                          0   \$C\$8>=0         Binding                  0
\$D\$8 Tennessee Chicago                      100    \$D\$8>=0         Not Binding            100
\$E\$8 Tennessee Dallas                       160    \$E\$8>=0         Not Binding            160
\$F\$8 Tennessee N.Y.                            0   \$F\$8>=0         Binding                  0
\$B\$9 Arizona San Fran                       180    \$B\$9>=0         Not Binding            180
\$C\$9 Arizona Denver                           80   \$C\$9>=0         Not Binding             80
\$D\$9   Arizona Chicago   20 \$D\$9>=0   Not Binding   20
\$E\$9   Arizona Dallas     0 \$E\$9>=0   Binding        0
\$F\$9   Arizona N.Y.       0 \$F\$9>=0   Binding        0
Microsoft Excel 11.0 Sensitivity Report
Worksheet: [Solver.xls]Transportation Problem
Report Created: 2/28/2004 6:03:19 PM

Final Reduced Objective Allowable Allowable
Cell           Name           Value   Cost   Coefficient Increase Decrease
\$B\$7    S. Carolina San Fran       0        6          10     1E+30        6
\$C\$7    S. Carolina Denver         0        3           8     1E+30        3
\$D\$7    S. Carolina Chicago       80        0           6         0        1
\$E\$7    S. Carolina Dallas         0        0           5     1E+30        0
\$F\$7    S. Carolina N.Y.         220        0           4         4   1E+30
\$B\$8    Tennessee San Fran         0        4           6     1E+30        4
\$C\$8    Tennessee Denver           0        2           5     1E+30        2
\$D\$8    Tennessee Chicago        100        0           4         2        0
\$E\$8    Tennessee Dallas         160        0           3         0   1E+30
\$F\$8    Tennessee N.Y.             0        4           6     1E+30        4
\$B\$9    Arizona San Fran         180        0           3         4   1E+30
\$C\$9    Arizona Denver            80        0           4         2   1E+30
\$D\$9    Arizona Chicago           20        0           5         1        2
\$E\$9    Arizona Dallas             0        1           5     1E+30        1
\$F\$9    Arizona N.Y.               0        6           9     1E+30        6

Constraints
Cell           Name           Value  Price    R.H. Side  Increase Decrease
\$G\$7    S. Carolina TOTALS       300         0        310     1E+30       10
\$G\$8    Tennessee TOTALS         260        -2        260        80       10
\$G\$9    Arizona TOTALS           280        -1        280        80       10
\$B\$10   TOTALS San Fran          180         4        180        10       80
\$C\$10   TOTALS Denver             80         5         80        10       80
\$D\$10   TOTALS Chicago           200         6        200        10       80
\$E\$10   TOTALS Dallas            160         5        160        10       80
\$F\$10   TOTALS N.Y.              220         4        220        10      220
Microsoft Excel 11.0 Limits Report
Worksheet: [Solver.xls]Limits Report 1
Report Created: 2/28/2004 6:03:20 PM

Target
Cell           Name             Value
\$B\$20 Shipping Total: San Fran    3200

Cell             Name           Value      Limit     Result      Limit     Result
\$B\$7    S. Carolina San Fran         0   -4.12513E-10   3200   -4.12513E-10   3200
\$C\$7    S. Carolina Denver           0   -1.79455E-10   3200   -1.79455E-10   3200
\$D\$7    S. Carolina Chicago         80             80   3200             80   3200
\$E\$7    S. Carolina Dallas           0   -3.65901E-10   3200   -3.65901E-10   3200
\$F\$7    S. Carolina N.Y.           220            220   3200            220   3200
\$B\$8    Tennessee San Fran           0   -4.12513E-10   3200   -4.12513E-10   3200
\$C\$8    Tennessee Denver             0   -1.79455E-10   3200   -1.79455E-10   3200
\$D\$8    Tennessee Chicago          100            100   3200            100   3200
\$E\$8    Tennessee Dallas           160            160   3200            160   3200
\$F\$8    Tennessee N.Y.               0   -5.05736E-10   3200   -5.05736E-10   3200
\$B\$9    Arizona San Fran           180            180   3200            180   3200
\$C\$9    Arizona Denver              80             80   3200             80   3200
\$D\$9    Arizona Chicago             20             20   3200             20   3200
\$E\$9    Arizona Dallas               0   -3.65901E-10   3200   -3.65901E-10   3200
\$F\$9    Arizona N.Y.                 0   -5.05736E-10   3200   -5.05736E-10   3200
df3d2c06-0b24-481a-ac90-f069cd2de44a.xls

Number to ship from plant X to
warehouse Y (at intersection).

Warehouses
Plants:        San Fran   Denver    Chicago   Dallas        N.Y.      TOTALS          Supply
S. Carolina    1         1          1        1            1           5             310
Tennessee      1         1          1        1            1           5             260
Arizona    1         1          1        1            1           5             280
TOTALS      3         3          3        3            3
Warehouse
180        80        200      160          220
Demands

Warehouses
Plants:        San Fran   Denver    Chicago   Dallas        N.Y.
S. Carolina    10         8          6       5             4
Tennessee       6         5          4       3             6
Arizona     3         4          5       5             9
TOTALS      19        17         15      13            19

Shipping                                      Shipping costs from plant X to
Total:   83                                warehouse Y (at intersection).

Page 8

```
Related docs
Other docs by wanghonghx
025-2008+bipolar+disorder+rh