solution

Solutions Plus is an industrial chemicals company that produces
specialized cleaning fluids and solvents for a wide variety of
applications. Solutions Plus just received an invitation to submit
a bid to supply Great North American railroad with a cleaning fluid
for locomo- tives. Great North American needs the cleaning fluid at
11 locations (railway stations); it provided the following
information to Solutions Plus regarding the number of gallons of
cleaning fluid required at each location (see Table 6.8). Solutions
Plus can produce the cleaning fluid at its Cincinnati plant for
$1.20 per gal- lon. Even though the Cincinnati location is its only
plant, Solutions Plus has negotiated

TABLe 6.8 GALLONS OF CLEANING FLUID REQUIRED AT EACH
LOCATION

with an industrial chemicals company located in Oakland,
California, to produce and ship up to 500,000 gallons of the
locomotive cleaning fluid to selected Solutions Plus customer
locations. The Oakland company will charge Solutions Plus $1.65 per
gallon to produce the cleaning fluid, but Solutions Plus thinks
that the lower shipping costs from Oakland to some customer
locations may offset the added cost to produce the product. The
president of Solutions Plus, Charlie Weaver, contacted several
trucking companies to negotiate shipping rates between the two
production facilities (Cincinnati and Oakland) and the locations
where the railroad locomotives are cleaned. Table 6.9 shows the
quotes received in terms of dollars per gallon. The “—” entries in
Table 6.9 identify shipping routes that will not be considered
because of the large distances involved. These quotes for shipping
rates are guaranteed for one year. To submit a bid to the railroad
company, Solutions Plus must determine the price per gallon it will
charge. Solutions Plus usually sells its cleaning fluids for 15%
more than its cost to produce and deliver the product. For this big
contract, however, Fred Roedel, the director of marketing,
suggested that maybe the company should consider a smaller profit
margin. In addition, to ensure that if Solutions Plus wins the bid,
it will have adequate capacity to satisfy existing orders as well
as accept orders for other new business, the management team
decided to limit the number of gallons of the locomotive cleaning
fluid produced in the Cincinnati plant to 500,000 gallons at
most.

Managerial Report You are asked to make recommendations that
will help Solutions Plus prepare a bid. Your report should address,
but not be limited to, the following issues:

1. If Solutions Plus wins the bid, which production facility
(Cincinnati or Oakland) should supply the cleaning fluid to the
locations where the railroad locomotives are cleaned? How much
should be shipped from each facility to each location?

2. What is the breakeven point for Solutions Plus? That is, how
low can the company go on its bid without losing money?

3. If Solutions Plus wants to use its standard 15% markup, how
much should it bid?

4. Freight costs are significantly affected by the price of oil.
The contract on which Solutions Plus is bidding is for two years.
Discuss how fluctuation in freight costs might affect the bid
Solutions Plus submits.

***Must be solved in excel via excel solver ensure to
provide formulas used as well as constraints for excel solver for
knowledge purposes. Need to be able to understand how the data
inputted into excel/excel solver was derived***

( I need help seeing how to input the excel
formulas)