SOLUTIONS PLUS $-$ PROBLEM DESCRIPTION

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 bit to supply the Great North American railroad with cleaning fluid for locomotives.

Great North American needs the cleaning fluid at $11$ locations $($railway stations$)$; it provided

the following information to Solution plus regarding the number of gallons of cleaning fluid

required at each location $($see Table $1).$

Table $1.$ GALLONS OF CLEANING FLUID REQUIRED AT EACH LOCATION

Solutions Plus can produce the cleaning fluid at its Cincinnati plant for $$1\mathrm{.}20$ per gallon. Even

though the Cincinnati location is its only plant, Solutions Plus has negotiated with the

industrial chemical 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\mathrm{.}65$ per gallon to produce the cleaning fluid,

but Solution Plus thinks that the lower shipping costs from Oakland to some customer

locations may offset the added cost of producing 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 location where the railroad locomotives are cleaned. Table $2$ shows the quotes received

in terms of dollars per gallon. The $"--"$ entries in Table $2$ identify shipping routes that will not

be considered because of the large distances involved. These quotes for shipping rates are

guaranteed for one year.Table $2.$ FREIGHT COST $($$ PER GALLON$)$

To submit a big 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.

SOLUTIONS PLUS $-$ 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:

Draw the network that shows the origin and destination points, routes, and costs

associated with each route. $(10$ points$)$

Formulate and solve the distribution network problem using Excel Solver and

answer the following questions. $(15$ points$)$

a$.$ 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? $(7$ points$)$

b$.$ What is the breakeven point for Solutions Plus? That is$,$ how low can the

company go on its bid without losing money? $(6$ points$)$

c$.$ If Solutions Plus wants to use its standard $15\%$ markup, how much should it

bid? $(4$ points$)$

d$.$ 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. $(3$ points$)$

Please read the problem carefully. You are expected to draw the distribution network, build an LP model and solve it by using the Excel solver. Insert a textbox to type your comments in Excel if necessary. There are four questions that you must answer in your managerial report. You may keep the report short, i$.$e$.,1$ page

Please show how to draw the distribution network, how to build the LP model in excel and how to solve it using excel solver!!!! THIS IS THE PART I WANT HELP WITH