goal programming in EXCEL

Most firms, from manufacturers to restaurants (even fast-food
restaurants), have a variety of offerings. Each offering may have a
different selling price and variable cost. Utilizing break-even
analysis, we modify the equation for the simple break-even point to
reflect the proportion of sales for each product. We do this by
“weighting” each product’s contribution by its proportion of
sales. The formula is then





Where V = Variable Cost per unit

P = Selling Price per unit

F = Fixed Cost

W = percent each product is of total dollar sales

i = each product

Example 1 Shows how to determine the break-even point for the
multiproduct case.

EXAMPLE 1

The costs at Le Bistro, a French-style deli, follow. Fixed costs are
$3,500 per month.

Item Price Cost Forecasted Sales Units

Sandwich $2.95 $1.25 7,000

Soft drink .80 .30 7,000

Baked potato 1.55 .47 5,000

Tea .75 .25 5,000

Salad bar 2.85 1.00 3,000

With a variety of offerings, we proceed with break-even analysis just as
in a single-product case, except that we weight each of the products by
its proportion of total sales.

Multiproduct Break-Even-Determining Contribution

1 2 3 4 5 6
7 8


Weighted

Selling Variable Forcasted % of
Contribution

Item(i) Price (P) Cost (V) (V/P) 1-(V/P) Sales $
Sales (Col. 5 x Col.7)

Sandwich $2.95 $1.25 .42 .58 $20,650 .446
.259

Soft drink .80 .30 .38 .62 5,600 .121
.075

Baked potato 1.55 .47 .30 .70 7,750 .167
.117

Tea .75 .25 .33 .67 3,750 .081 .054

Salad bar 2.85 1.00 .35 .65 8,550 .185
.120

$46,300 1.000 .625

For instance, revenue for sandwiches is $20,650 (2.95 x 7,000), which is
44.6% of the total revenue of $46,300. Therefore, the contribution for
sandwiches is “weighted” by .446. The weighted contribution is .446
x .58 =.259. In this manner, its relative contribution is properly
reflected.

Using this approach for each product, we find that the total weighted
contribution is .625 for each dollar sales, and the break-even point in
dollars is $67,200:







The information given in this example implies total daily sales (52
weeks at 6 days each) of



Break-even figures by product provide the manager with added insight as
to the realism of his or her sales forecast. They indicate exactly what
must be sold each day, as we have done in Example 2.

You have now become an expert in the use of goal and integer
programming. Consider now how you would creatively apply an integer
goal programming model to this problem setting. You are asked here to
provide a “conceptual” or written formulation rather than a
mathematical formulation.

Please describe several economic or “hard” constraints that you
would include in your model.

Please describe several goal or “soft” constraints that you would
include in your model.

Please describe how you would prioritize and/or relative weight the
goals you have described in b.