Question Details

Page 1 of 6
Take-Home Exam
MIS 6543 Business Analytics
Fall 2013
Arkansas State University
INDIVIDUAL ASSIGNMENT
Instructor: Dr. Ahmad Syamil
Due date: 2:00 PM, Tuesday, October 22, 2013. Of course you can submit your files earlier.
Start working on this assignment ASAP. If you wait until the last minute, you will not be able to
finish it.
Name your files using your name, location, and status, i.e., whether you are an online or face-toface
(F2F) student. After that, put your files on Blackboard> MIS6543> Course Tools> Journals>
Digital Dropbox>Create Journal Entry>. Write an appropriate title and message including your
status> Attach file.
Inform me by email after you put your files on Blackboard. Don’t attach your assignment files with
your email. If you are an ASU Jonesboro face-to-face-student, you must also submit the
complete hard copy of your work under my office (BU 406) or in class. Do not submit the hard
copy of the answer to each problem one-by-one. Staple together.
Please refer to the first Excel tip on page 25, figure 2.2 on page 26, and “Wyndor” Excel file. Use
the following colors for your Excel files: light blue (data cells without border), yellow (decision
variables with regular border), and orange (target or objective cell with heavy border). Always put
initial values such as 1s in the yellow cells before running Solver. If not, Solver may give you an
error message.
** Include the screenshots of your Excel files to justify your answers. Handwritten submissions
are not acceptable. Use Excel, Word, or any other software even when you have to draw a
picture. **
Problem 1: Management Science, Chapter 4 - The Art of Modeling with Spreadsheets.
Axl Rose, the lead singer of Guns N' Roses, needs your help to invest his money to pay for his
son- university education. His son, Dylan, is considering entering an Ivy League university 5
years from now. Axl Rose can only give his money to you now and wants to minimize his initial
investment. You can put his money in more than 1 investment type. No new money for
investment in the second year and onward because he is busy with a worldwide tour with his
music group and a U.K. rock band Duran Duran. Any new investment after the first year can only
be made through reinvestment of previous investment.
Dylan- first year university expenses will amount to $70,000 and go up by $4,000 per year in the
last three years of his education. Assume that now is the beginning of year 1. It means that Axl
Rose will withdraw his investment at the beginning of years 6, 7, 8, and 9. Some possible
investments:
Investment Type Availability Maturity (Year) Return at Maturity (%)
P Every year 1 5
Q 1, 3, 5, 7 2 13
R 1, 4 3 19
S 1 7 64
a. Visualize where you want to finish. What numbers are needed? What are the decisions
that need to be made? What should the objective be?
b. Create an LP model in algebraic form
c. Find the optimal solution using Solver.
d. Explain the optimal solution. Example: Axl Rose should invest $X in P at the beginning of
the first year, keep his investment in P for 2 years, and then receive $Y from divesting
Page 2 of 6
investment P at the beginning of the third year. He then should invest $Y at Q at the
beginning of the third year …
Problem 2: Management Science, Chapter 5 - What-If Analysis for Linear Programming.
Essence Beverage Co. produces a variety of fruit drinks. The company will introduce a new blend
of strawberry, blackberry, and apple juices. The new blend must consist of at least 50% of
strawberry juice, between 20% - 30% of blackberry juice, and exactly 20% of apple juice. For the
current session, 10,000 gallons of strawberry juice and 8,500 gallons of blackberry juice can be
purchased. Apple juice is unlimited. The costs are $1.00 per gallon for the strawberry juice, $1.50
per gallon for the blackberry juice, and $0.50 per gallon for the apple juice. The company can sell
the entire production it can blend for $2.5 per gallon.
a. Create an LP model to maximize the total profit in algebraic form.
b. Find the optimal solution using Solver. Use the sensitivity analysis to answer the next
questions.
c. Refer to the “Variables Cells” in the sensitivity analysis. Explain each of the final values,
reduced costs (use Google), and objective coefficients. Calculate and interpret the
allowable range of each objective coefficient.
d. Refer to the “Constraints” in the sensitivity analysis. Explain each of the final values,
shadow prices, and constraint R.H. sides. Calculate and interpret the allowable range of
each constraint R.H. side. What are the managerial implications of the shadow prices?
e. If the company could get extra amounts of the strawberry juice, should the company do
so? If so, how much should the company willing to pay for each extra gallon of the
strawberry juice? How many extra gallons would the company want to acquire?
f. If the company could acquire extra amounts of the blackberry juice, should the company
do so? If so, how much should the company willing to pay for each extra gallon of the
blackberry juice? How many extra gallons would they want to buy?
Problem 3: Management Science, Chapter 6 - Network Optimization Problems.
RedWolf Manufacturing Co. has 3 factories, 2 distribution centers (DCs), and 4 major customers.
Maximum factory capacities in units and shipping cost per unit in $ from each factory to each DC
are as follows:
Distribution Center Maximum Capacity
Factory DC1 DC2 in units
FA $42 $74 650
FB $82 $53 850
FC $54 $65 580
Customer demand in units and shipping cost per unit in $ from each DC to each customer are as
follows:
Distribution
Center
Customer
AX BT CQ DZ
DC1 $58 $41 $82 $45
DC2 $34 $62 $73 $74
Demand in units 320 340 380 410
RedWolf must satisfy all demands. It is acceptable if some factories do not produce at the
maximum capacity.
a. (1) Draw a network that depicts the company- supply chain network. Identify the
supply nodes, transshipment nodes, and demand nodes in this network. Handwritten
submissions/pictures are not acceptable. Use Excel, Word, or any other software. A
simple picture is enough.
(2) Formulate this problem in algebraic form.
(3) Solve this problem using Solver.
b. A new supply chain manager just arrived. His name is Bono. He just retired as the
lead singer of U2. He reorganized the logistics system and decided that freights
Page 3 of 6
between the two DCs are allowed at $5 per unit and that direct deliveries can be
made from FC to DZ at a cost of $35 per unit.
(1) Draw a network that depicts the new company's supply chain network. Identify
the supply nodes, transshipment nodes, and demand nodes in this network.
(2) Formulate this problem in algebraic form.
(3) Solve this problem using Solver. Compare your solutions in parts (a) and (b).
(4) What managerial lessons can be learned from this problem?
Problem 4: Management Science, Chapter 7 - Using Binary Integer Programming.
NorthStar Bank is expanding into a state that has 13 counties. State archaic law allows creating
bank branches in any counties that are adjacent to a county in which a principal place of business
(PPB) is situated. Hence, the bank serves the people in a county where the bank sets up a PBB
and any adjacent counties.
a. Suppose that only 1 PBB can be set up in the state. Where should the PBB be situated to
maximize the number of people served? Formulate this problem in algebraic form and
then solve this problem using Solver.
b. If 2 PBBs can be established in the state, where should the 2 PBBs be located?
Formulate this problem in algebraic form and then solve this problem using Solver.
c. NorthStar Bank management has learned that a local bank in county 5 is for sale. If only
1 PBB can be established in the state, should the management buy the local bank?
Justify your answer with a calculation, i.e., the number of people served. Compare your
calculations in parts (a) and (c).
d. Suppose NorthStar Bank has decided to buy the local bank in county 5 because the local
bank is cheap. If 2 PBBs can be established in the state and one of them in county 5,
where should the other PBB be located to maximize the number of people served?
Formulate this problem in algebraic form and then solve this problem using Solver.
e. What managerial lessons have you learned from this problem?
The map displays the state that has 13 counties with the number of people in each
county.
Page 4 of 6
Problem 5: Management Science, Chapter 11 - Queuing Models.
SkyGate Co. tests and repairs high-tech equipment. Jobs arrive at the rate of 3 jobs/day. One day
= 8 hours. The interarrival times follow an exponential distribution. The company- testing facility
is a single-channel system operated by a crew of 4 highly skilled technicians. The service times
have a mean of 2 hours and follow an exponential distribution. The testing costs the company
$285 per hour. The waiting cost is $360 per hour.
a. What is the proper waiting line model for this problem? M/D/3? M/G/4? Or something
else? Explain.
b. Find and explain the lambda, mu, s, L, Lq, W, Wq, rho, Po, and total cost of this particular
model.
c. The crew leader suggested the company management to buy a new automated testing
equipment that would allow a constant testing time of 1.5 hours/job. Assume the standard
deviation is 0. However, the new testing cost, including equipment amortization, would be
$330 per hour. What is the proper waiting line model for this new equipment? Find and
explain the new lambda, mu, s, L, Lq, W, Wq, rho, Po, and total cost. Should the
company buy the new equipment?
d. If the new equipment only needs 3 technicians, what should the company do with the
other technician? Use your management knowledge and Google.
e. Is high process variation good or bad for most companies? What are the effects of
reducing process variation on L, Lq, W and Wq? What management techniques are
useful to reduce process variation? Use Google. We also discussed this topic in class.
f. Is it reasonable to think that companies such as SkyGate can attract new customers by
reducing waiting time? Create and explain a table for this problem similar to table 12.2 on
page 508.
g. What are the managerial lessons of this problem?
PS: If you use Google, don’t copy and paste. Use your own words. List all your
references including web site addresses.
Problem 6: Management Science, Chapters 11 and 12.
Oasis Business Advisory, a consulting firm, leases 1 copy machine for $50 per day that is used
by all consultants. Six consultants use the machine per hour. Average usage (service time) = 9
minutes/consultant. The interarrival and service times follow the exponential distribution.
a. What is the proper waiting line model for this problem? M/D/5? M/G/8? Or something
else? Explain.
b. How do you know that 1 copy machine can serve 6 consultants/hour?
c. Use chapters 11 (Excel template) and 12 (queuing simulator) to find and explain L, Lq,
W, Wq, and Po. Do you get similar results?
d. Suppose that the average salary (waiting cost) is $25/hour for each consultant. What is
the total cost/day of having 1 copy machine? 1 Day = 8 working hours.
e. What is the optimum number of copy machines to minimize the total cost/day? Justify
your answer. Are there any other factors, besides the total cost, that should be
considered before leasing more than 1 copy machine?
f. What are the similarities and differences between the techniques in chapter 11 (analytical
methods of waiting line models) and those in chapter 12 (simulation of waiting line
models)? Which technique do you prefer to analyze a simple problem? If you have to
analyze a complex network of machines such as a factory that has 100 machines, which
technique would you use? You can also Google “process simulation” to enrich your
answer.
g. What managerial lessons do you learn from this problem?
Page 5 of 6
Notes:
1. You can get your assignment files from:
1. Your @smail.astate.edu email registered at Blackboard.
2. Blackboard Learn
3. Your classmate.
2. Most of the problems require Microsoft Excel. It is your responsibility to be familiar with the
Microsoft Excel version you are using (2007, 2010, or 2013). The ASU College of Business
computer labs use Excel 2013. Please buy or borrow any Excel book if necessary. You can
get and read Excel ebooks on-line from the ASU library.
ASU students can get Excel, Symantec EndPoint Anti Virus, and other software free of
charge from http://www.astate.edu/a/its/software-downloads/
3. Creating homework files properly including Microsoft Excel files is a vital part of this class.
You must create your homework file from scratch. If your file does not work properly before
the due date, please do not send your file to someone (including to me) and ask someone to
fix your file. I can give you some hints, but I do not fix your file. If I fix your file, it means that I
am the one who finishes your homework. You, and not the instructor, have the
responsibility to finish this assignment.
4. Start this assignment as early as possible. If you wait until the last minute, you will not be
able to finish this assignment on time.
5. Drop your homework files in Blackboard Digital Drop Box. Name your file using:
FirstName_LastName_Location_F2F_or_Online_THE (Take-Home Exam)
_Problem#_MIS6543_Fall_2013.
Example: John_Public__Jonesboro_F2F_THE_Problem1_MIS6543_Fall_2013.
6. If you are in a hurry, you can e-mail your virus-free homework files to me. My computer will
automatically delete your files if they are not virus free. You can drop your files in the
Blackboard Drop Box the next day.
7. Type the following information on the first worksheet (cover sheet) of your Excel file:
ï‚· Course name, i.e., MIS 6543 Business Analytics.
ï‚· Term, i.e., Fall 2013.
ï‚· Take Home Exam.
ï‚· Your full name.
ï‚· ASU campus location and status (face-to-face or online).
ï‚· The name of the instructor, i.e., my name.
ï‚· Type and sign or initial the following:
“I did not copy any other person- work. I did not share my work with anybody else. I created
this work from scratch. I did not revise or copy and paste any other files. Nobody else helped
me create my files. If I did any dishonest activities, I will get an “F” in this course and may be
expelled from ASU.”
Name: _______________________ Date: ____________
Page 6 of 6
8. Before submitting your file, please create a footer and header on each worksheet.
Header:
Left: type your name. Middle: Click the “date” icon. Right: Click “time” icons.
Footer:
Left: Click the “file name” icon.
Refer to
“Excel Basics #17: Page Setup”
http://www.youtube.com/watch?v=NxfYESUnKoM
or
“In Excel: Add the date, time, filename, and page numbers to a header or footer in Excel
2010”
http://www.youtube.com/watch?v=4KP3ouBX-mQ
9. Please type the title (problem # and so on) on top of each Excel worksheet/file. For example:
Problem 1: Management Science, Chapter 4 - The Art of Modeling with Spreadsheets.
10. Submit 1 file for each problem. Your work must be neat, and easy to read. Don't make
someone hunt around your pages to find your answers. Write your solutions so that other
people can read and understand your solutions. If a problem consists of (a), (b), and (c),
you must also mark your answer with (a), (b), and (c). I grade not only the contents but also
the organization of your work. Format your answers and tables nicely. Please also use this
class to learn Excel and to learn how to organize your work properly.
11. If it is possible, print each problem on one page. If not, at least do not split a table/graph, i.e.,
do not print a table/graph on two pages.
Refer to
“How to make an Excel sheet print out on one page”
http://www.youtube.com/watch?v=pYCvgYUtiCw
and
“Video Excel - How to manually set the print area in Excel 2010”
https://www.youtube.com/watch?v=x0YAY79FFCk