Page 227 -

EXCEL SOLUTION OF HEWLITT CORPORATION FINANCIAL PLANNING PROBLEM 207

Add-On Cost ($/ton)
Miami Fort Miami Fort Beckjord East Bend Zimmer
Supplier Unit 5 Unit 7 Unit 1 Unit 2 Unit 1
RAG 10.00 10.00 10.00 5.00 6.00
Peabody 10.00 10.00 11.00 6.00 7.00
American 13.00 13.00 15.00 9.00 9.00
Consol 10.00 10.00 11.00 7.00 7.00
Cyprus 10.00 10.00 10.00 5.00 6.00
Addington 5.00 5.00 6.00 4.00 4.00
Waterloo 11.00 11.00 11.00 7.00 9.00

Managerial Report
Prepare a report that summarizes your recommendations regarding Cinergy’s coal allocation problem. Be
sure to include information and analysis for the following issues.
1 Determine how much coal to purchase from each of the mining companies and how it should be allocated
to the generating units. What is the cost to purchase, deliver and process the coal?
2 Compute the average cost of coal in cents per million BTUs for each generating unit (a measure of the cost
of fuel for the generating units).
3 Compute the average number of BTUs per pound of coal received at each generating unit (a measure of
the energy efficiency of the coal received at each unit).
4 Suppose that Cinergy can purchase an additional 80 000 tons of coal from American Coal Sales
as an ‘all or nothing deal’ for $30 per ton. Should Cinergy purchase the additional 80 000 tons
of coal?
5 Suppose that Cinergy learns that the energy content of the coal from Cyprus Amax is actually 13 000 BTUs
per pound. Should Cinergy revise its procurement plan?
6 Cinergy has learned from its trading group that Cinergy can sell 50 000 megawatt-hours of
electricity over the grid (to other electricity suppliers) at a price of $30 per megawatt-hour.
Should Cinergy sell the electricity? If so, which generating units should produce the additional
electricity?

Excel Solution of Hewlitt Corporation Financial Planning
Appendix 4.1
Problem

In Appendix 2.1 we showed how Excel could be used to solve linear programming
problems. To illustrate the use of Excel in solving a more complex linear program-
ming problems, we show the solution to the Hewlitt Corporation financial planning
problem presented in Section 4.5.
The spreadsheet formulation and solution of the Hewlitt Corporation problem
are shown in Figure 4.13. As described in Appendix 2.1, our practice is to put the
data required for the problem in the top part of the spreadsheet and build the model
in the bottom part of the spreadsheet. The model consists of a set of cells for the
decision variables, a cell for the objective function, a set of cells for the left-hand-side

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.