Reference no: EM133898775
Business Decision Models
This is an open-book, open-notes, take-home exam that is to be done individually. You may use any notes from class, any of your past problem set solutions, or any reference books that you would like. You may download or view anything on the class Canvas site, but are otherwise not allowed to use the internet nor AI Tools to assist with this exam. You are not allowed to receive help from any individual (student, professor, friend, etc.) other than me. Do not compare final solutions with your classmates until everyone has completed their exams. You may ask me any questions that you would like, but I reserve the right to refuse to answer any questions that would give unfair assistance. Generally I can only answer clarification questions and questions about software issues.
Please work your exam directly within the provided single Excel file with a separate tab for each problem (available on Canvas under the Final Exam link.) Do not work in separate Excel files and copy and paste results in. (Solver, TreePlan, and Crystal Ball data do not survive Excel's copy and paste). If you need to work on different computers (e.g., a Mac and Windows machine) or use the remote computer lab, this file can be moved back and forth between machines without issue. When completed, please submit the exam under the Final Exam link on Canvas no later than the due date and time shown above.
Guidelines to Improve Your Performance on Q1 and Q2:
You will be given partial credit for each portion of a model that is correct. Even if you are having trouble getting a final solution or putting together a complete model, it is in your best interest to try to set up as many components of the model as possible.
On the other hand, even a model that gets the right answer, but has an element that is not set up correctly (e.g., the logic of a constraint is not correct or missing), would not receive full credit. Each specified requirement must be included in the model (e.g., enforced with constraints) to earn credit. Furthermore, it is not sufficient to just state that you can infer based on the existing data that some property must be true. The requirements must each be enforced such that they would continue to hold true even if the data were changed and the model re-solved.
Part of your grade will be based upon good "spreadsheet style" based on the guidelines spelled out on page 17 of the Module 2 class slides (and it's corresponding post-class video). Multiple style issues will earn a deduction.
Pollution Control in the Wenatchee River
Toxic chemicals banned decades ago continue to linger in the Wenatchee River, threatening people and the environment, according to three studies by the Washington State Department of Ecology. Recently, the Washington Department of Health advised the public to not eat mountain whitefish from the Wenatchee River from Leavenworth downstream to where the river joins the Columbia, due to unhealthy levels of PCBs (polychlorinated biphenyl).
To address this situation, the state is going to build some pollution control stations. Three sites (near Leavenworth, Cashmere, and Wenatchee) are under consideration. The state is primarily interested in controlling the pollution levels of two types of PCB pollutants (labeled PCB-A and PCB-B). The state legislature requires that at least 57,000 pounds of PCB-A and at least 84,000 pounds of PCB-B be removed from the river. The relevant data for this problem are shown below. If a station is built at a site (at the cost indicated below), it can treat an effectively unlimited amount of water, subject to the cost per ton of water treated as listed below. The last two rows indicate the number of pounds of each pollutant that is removed per ton of water treated. For example, if they treat two tons of water at Leavenworth, both 56 pounds of PCB-A and 44 pounds of PCB-B would be removed from the river.
For political reasons, the state would only consider building a station near Wenatchee if there is also a station built near Leavenworth. Furthermore, the state can build on at most two of the three sites.
Build a linear programming spreadsheet model (with integer and/or binary variables if needed) that would determine where to build treatment stations and how many tons of water to treat at each station so as to meet the state legislature's requirements at the lowest possible total cost.
Walla Walla Development Capital Budgeting
Walla Walla Development (WWD) is trying to complete its investment plans for the next five years. Currently WWD has $3.9 million available for investment. Furthermore, WWD expects income streams from other business ventures over the next 4 years of $1.7 million, $1.1 million, $1.9 million, and then $1.2 million. These data are summarized in the table below. For simplicity of modeling, you may assume that these income streams occur at one year intervals.
There are three development projects that WWD is considering. The first is the Walla Walla Resort Hotel, a four-star deluxe hotel with views of the golden hillsides melting into broad expanses of crimson fields. A second project, the Walla Walla Southside Mall, would be a shopping complex adjacent to the Walla Walla Resort Hotel. The third project, the Bank of Walla Walla Building, is a 4-story office building to be built in downtown Walla Walla starting in two years, after the demolition of several smaller buildings. If WWD participates fully in these projects, each would have the projected cash flow streams over the next four years as indicated in the following table. (Again, for simplicity, you may assume these cash flows occur at one year intervals.) The estimated value of each project five years from now is also indicated.
Assume that WWD may participate either fully, fractionally (with a partner), or not at all in any or all of the three projects. If WWD participates in a project fractionally at less than 100%, all the cash flows, and the final value of that project for WWD are reduced proportionally. For example, if WWD participates at 50% in the Resort Hotel, the cash flows would be -$2.1 million (now); -$1 million (one year from now); +$0.8 million (two years from now); +$1.1 million (three years from now); and +$1.1 million (four years from now). The estimated value to WWD five years from now would be $5 million. WWD can borrow money at 4% interest per year. Any amount up to $4 million can be borrowed in each year. If a loan is taken out, it must be paid back the following year with interest (e.g., if $1 million is borrowed two years from now, $1.04 million is due three years from now). By company policy, WWD must maintain a minimum balance of $1 million at all times. WWD invests any and all remaining surplus funds (including the $1 million minimum balance) in a money market fund that earns a 1% return per year (e.g., if $3 million in surplus funds remain 3 years from now, then $3.03 million would be available 4 years from now). Assume no interest earned "now" (it is already included in the $3.9 million cash available now.) WWD would like to know how much to participate in each project and how much to borrow each year so as to maximize their net worth five years from now (the value of their development projects plus any surplus funds and savings interest minus any borrowed principal and interest due). Assume no loan is taken five years from now (it would have no effect on net worth, since the cash asset would be offset by the loan liability). To simplify the modeling, you may assume that all cash flows occur simultaneously at one year intervals (now, one year from now, etc.). Set up and solve a linear programming spreadsheet model for this problem. (To simplify grading, when building your model please follow the data structure outlined in columns I through M of the data spreadsheet. Calculate beginning and ending balances at each year, with all cash flows calculated in separate columns in-between; add or delete intermediate columns as needed, and label all columns.)
The Diego garcia Furniture Company
During the next production period, The Diego Garcia Furniture Company is considering producing dining room chairs, dining room tables and/or bookcases. Each item must go through the two stages of production (assembly and finishing), and requires a certain amount of wood (fine cherry wood). They have formulated a linear program to determine the production levels that would maximize profit. The solved spreadsheet model and corresponding sensitivity report are shown below.
For each of the following parts, answer the questions as specifically and completely as is possible using just the sensitivity report, without re-solving the problem with the Solver. Type your answers directly into spreadsheet tab 3, answering in the space provided for each problem part. Each part involves multiple questions, so be sure to distinctly answer all questions within each part. All problems are independent (i.e., any change made in one problem part does not apply for the other problem parts). For full credit, you must justify your answers by utilizing the results in the sensitivity report. Use bold type to highlight the key findings (e.g., production quantities will/may/won't change; total profit will increase by $x.)
Suppose the profit per bookcase decreases by $25. Will this change the optimal production quantities? What can be said about the change in total profit?
Suppose the profit per table decreases by $10 and the profit per bookcase increases by $40. Will this change the optimal production quantities? What can be said about the change in total profit?
Suppose a part-time worker in the assembly department calls in sick, so that now four fewer hours are available in the assembly department. How much would this affect total profit? Would it change the optimal production quantities?
Suppose one of the workers in the assembly department is also trained to do finishing. Would it be a good idea to have this worker shift some of her time from the assembly department to the finishing department? Indicate the rate at which this would increase or decrease total profit per minute shifted. How many minutes can be shifted before this rate might change?
The allowable decrease for the chairs variable cell and the allowable decrease for the wood constraint were both accidentally deleted from the sensitivity report. Based on the nature of the solution, you should be able to infer what numbers were there. For each, indicate what number should be there and, in a sentence or two, explain the intuition as to why you know that particular number should be there.
Monster Studios
Monster Studios is a small movie production studio specializing in horror flicks. Historically, the movies they have produced either do quite well (are "hits") or do quite poorly (are "flops"). They earn an average of $40 million from their hit movies, and lose an average of $15 million on their flops. Of all movies produced in recent years, 40% have been hits and 60% have been flops. At a cost of C dollars, a market research firm can be hired to analyze a movie script, conduct market surveys, and make a prediction of how the movie will do if it is produced. If they issue a positive report, it is likely the movie would be a hit, while if they issue a negative report, it is likely the movie would be a flop. However, the report is not always accurate. The market research firm has the following track record on thirty similar previous movie scripts that they have analyzed:
Suppose C = $3 million. Monster Studios has been presented with a new movie script entitled Zombie Professors that they feel is typical of other movies they have produced in recent years. In spreadsheet tab 4abc, build and solve a decision tree, and identify the strategy that maximizes their expected profit in responding to Zombie Professors. Should they hire the market research firm to issue a report on Zombie Professors? Should they produce the movie? (If they decide not to produce the movie, assume there are no additional revenues or costs, other than the cost of the market research firm if they were used.) Below the tree, write a sentence or two to fully describe the optimal strategy and the overall expected payoff. Clearly label this sentence as your answer to part a.
What is the maximum value of C that Monster Studios should be willing to pay to hire the market research firm to assess Zombie Professors? Below the tree and your answer to part a, write a sentence to answer this question, and clearly label it as your answer to part b. (If you modify the data in the decision tree to help you answer this question, please return the tree to its original form to answer part a.)
Suppose the market research firm from part a and b is no longer an option. To get an idea of what acquiring more information might be worth to Monster Studios, they want to determine the value of perfect information. Suppose there existed a "perfectly accurate" market research firm that always could correctly predict whether a script would be a hit or not. How much should Monster Studios be willing to pay to hire this perfectly accurate market research firm to assess Zombie Professors? Below the tree and your answer to parts a and b, write a sentence to answer this question (showing your work), and clearly label it as your answer to part c. (If you modify the data in the decision tree to help you answer this question, please return the tree to its original form to answer part a.)
Make a copy of the spreadsheet used to solve part a, by holding down control (or option on a Mac) while clicking and dragging tab 4abc to the right to make a copy of it. Double-click on the new tab to rename it to 4d. Now suppose Monster Studios is risk averse and wishes to take this into account when making the decision presented in part a (still assuming C = $3 million). Suppose further than Monster Studios would just barely be willing to accept a hypothetical 50-50 gamble that would net $50 million if it succeeds but lose $25 million if it fails. Using TreePlan and the exponential utility function to account for Monster Studios aversion to risk, re-solve the decision tree in spreadsheet tab 4d. In a sentence or two below the tree, fully describe the optimal strategy.
Optional Question This question is optional. If you do not complete Q5, or score lower on Q5 than each of (Q1, Q2, Q3, or Q4), then your exam score will simply be based on the scores of Q1 through Q4. If you complete Q5 and score better than one of (Q1, Q2, Q3, or Q4), then your score on the lowest scoring question (Q1, Q2, Q3, or Q4) will be raised to the average of its original score and your score on Q5. (For example, if you score 20 points on Q1 as your lowest scoring question, and then score 24 points on Q5, your score on Q1 would be raised to 22 points.)
New Business School Building (25 points). A prestigious university in the Pacific Northwest is planning to build a new business school building on campus. To accomplish this, the activities listed in the following table must be completed. For most of these activities there is a set of predecessor activities that must be completed before the activity begins. For example, the foundation cannot be laid until the building is designed and the site prepared.
Obtaining funding will likely take somewhere around 8 months. They estimate roughly a two-thirds chance that the time required to obtain funding will be within one month (plus or minus) of this estimate, but there is a small chance this estimate could be significantly off. The architect has estimated the time required to design the building is equally likely to be anywhere between 2 and 6 months. The general contractor has provided three estimates for each of the construction tasks-an optimistic scenario (minimum time required if the weather is good and all goes well), a most likely scenario, and a pessimistic scenario (maximum time required if there are weather or other problems). These estimates are provided in the table below. Finally, the landscaper has guaranteed that her work will be completed in exactly 1 month.
Use Crystal Ball to run a 1000-trial simulation of this project. For each assumption cell, use the probability distribution that best matches the description of uncertainty provided above. Based on the results of the simulation, answer each of the following questions in the orange-shaded cells in the data spreadsheet. Paste into your spreadsheet (where indicated below the orange-shaded cells) any Crystal Ball output required to justify your answers to the three questions. (Use Copy Chart under the Edit menu of the forecast chart window.)
Based on the results of the simulation, what is the expected time (mean) for project completion?
They hope to have the building ready for classes in 36 months. What is the probability that the project will be completed in 36 months or less? Get Assignment Help from the Best tutors!
Which of the activities would be most important to keep a close eye on in order to complete this project on schedule? Justify your answer in a sentence or two.