Jump to content
Leixer

If you have time, please read & help :D

Recommended Posts

These are math problems while using Spread sheet, for some this might be hard, some could be fair or easy, for me it's hard hehe. can any 1 help me on solving this?i have small knowledge in excel spreadsheet THANK YOU, not sure if this is the right place for this topic

 

PROBLEMS:

 
1. A company that makes concrete sells it in three quality levels, type 1 the being lowest, and type
3 being the highest. After deducting all variable costs, the net revenue is $30 per Tonne for
type 1, $40 for type 2, and $50 per type 3. There are three operations, each of which limits the
amount of production: crushing, grinding, and mixing. In addition, each type of concrete must
be inspected. The model has been formulated as:
Let X1, X2, and X3 represent respectively the number of type 1, 2, and 3 made in Tonnes per
hour.
maximize 30X1 + 40X2 + 50X3
subject to
Crushing 2X1 + 4X2 + 5X3 80
Grinding 6X1 + 8X2 + 12X3 210
Mixing 5X1 + 7X2 + 8X3 146
Inspection 3X1 + 4X2 + 5X3 50
non-negativity X1 ; X2 ; X3 0
(a) Solve using a spreadsheet Solver, and print the Answer and Sensitivity Reports.
(b) State the solution in words, and indicate which constraints are binding.
(c) By using the information from the Sensitivity Report (NOT by re-running the model each
time), give the predicted change to the objective function value (and the reasoning behind your
answer) for the following situations (taken one at a time). If the OFV cannot be predicted
exactly, then give an answer such as “the OFV will increase by at least $100”.
(i) The revenue per Tonne for type 1 concrete rises by $0.70.
(ii) There are three fewer units of crushing.
(iii) The revenue per Tonne for type 2 concrete increases by $8.00.
(iv) The number of units of grinding decreases by 20.
(v) The number of units of mixing increases by nine.
(vi) The price per Tonnes of Type 1 cement falls by $3, and the price for Type 3 concrete rises
by $15.
(See the next page for the second problem.)
1
2. A company makes calculators at facilities in Shanghai (China), Bonn (Germany), and Cape
Town (South Africa). These plants can make 1800, 900, and 2700 calculators per week beyond
the demand in the “local” markets of Eastern Asia, Germany, and Africa respectively. All three
plants can ship to markets elsewhere: Canada/USA, Latin America, Europe, and Western Asia.
The demands per week in these three markets are for 1500, 600, 750, and 2300 calculators per
week respectively. Calculators are shipped in boxes of 50. The shipping costs per box are as
follows:
From/To Canada/USA Latin America Europe Western Asia
Shanghai 180 270 205 120
Bonn 230 250 80 190
Cape Town 200 210 185 220
(a) Formulate a model in Excel (there is no need to give the algebraic model) and solve it to
determine how much should be shipped from the factories to the markets. State the solution
in words.
(b) Now suppose that calculators can be shipped from Cape Town to Bonn at a cost of $15 per
box. For legal reasons, Shanghai cannot transship to Bonn. Formulate a model in Excel
and solve it, and state the solution in words.
 

Share this post


Link to post
Share on other sites

I made a quick search for linear programming in excel:

http://www.youtube.com/watch?v=RicajFzoenk

 

I hope that helps, If not searching for 'linear programming in excel'  or 'linear programming using excel solver' should give you plenty of other results.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

  • Forum Statistics

    15,975
    Total Topics
    174,654
    Total Posts
  • Recently Browsing

    No registered users viewing this page.

  • Upcoming Events

    No upcoming events found
  • Recent Event Reviews

×
×
  • Create New...