PART-1 Please go through Examples 1.2 (Woodworks at page 12) and
1.4 (Sam’s Bookstore at page 20) from our textbook and create Excel files as
described in examples. Show all the work they implemented and get familiar with
some nice features of Excel. Each example will be 20 POINTS
each of below questions I provide the solution values. So for each question you
Decide on decision variables
Form your objective function
Form your constraints
Bring parts b, c and d
all together to create a linear programming model
Transfer this model into Excel
Use solver to find optimum solution
Interpret the results and tell me what would be your and
interpretations recommendation based on this solution.
Create a single excel file and name
it as Assignment week1_ yourname_lastname
For each question use a separate
Excel worksheet not different files. Name worksheet-1 as Example-1, worksheet-2
as Example-2, Worksheet-3 as Q1 and worksheet-4 as Q2
Show all of your model and interpretation
in the same worksheet.
Please do not copy and paste from
other students. Please remember that everybody’s design will be different and
there is no chance that two student’s models would be exactly same.
Please also make sure that you used SOLVER
for optimum solution.
DUE DATE : Please submit your excel file before our first class on
Sept 10, 2021 before our next class. Late assignments will not be accepted.
Question 1 30 POINTS
A company produces two
types of tables, T1 and T2. It takes 2 hours to produce the parts of one unit
of T1, 1 hour to assemble and 2 hours to polish. .It takes 4 hours to produce
the parts of one unit of T2, 2.5 hour to assemble and 1.5 hours to polish. Per
month, 7000 hours are available for producing the parts, 4000 hours for
assembling the parts and 5500 hours for polishing the tables. The profit per
unit of T1 is $90 and per unit of T2 is $110. How many of each type of tables
should be produced in order to maximize the total monthly profit?
Answer 1 The maximum profit is $273000 . Hence the company
needs to produce 2300 tables of type T1 and 600 tables of type T2 in order to
maximize its profit.
Question-2 30 POINTS
John has $20,000 to
invest in three funds F1, F2 and F3. Fund F1 is offers a return of 2% and has a
low risk. Fund F2 offers a return of 4% and has a medium risk. Fund F3 offers a
return of 5% but has a high risk. To be on the safe side, John invests no more
than $3000 in F3 and at least twice as much as in F1 than in F2. Assuming that
the rates hold till the end of the year, what amounts should he invest in each
fund in order to maximize the year end return?
Answer_2 The return R is maximum at 603.33. For maximum return, John has to invest $11333 in fund F1, $5667 in fund F2 and
$3000 in fund F3.