OPRE 4350 – Spreadsheet Modeling and Business Analytics
Fall, 2020
Take-Home Final Exam General instructions and Honor Code requirements
• This exam will be released on Wednesday, December 2, 2020 at 8am (CST) and will be due Friday, December 4, 2020 at 5pm (CST). Submit your files to eLearningOur Course HomepageTurn- it-in: Take-Home Final Exam Submission (see “Important Notes” for details).
• This exam is to be undertaken individually. You are not to receive help from or give help to others. There should be no discussion of the exam or the topics covered, nor any discussion of course-related software, nor any sharing of course- or exam-related files, between the time the exam becomes available and the time you submit your completed work. You may not save any exam-related files on shared disk space and may not access other students’ exam-related files.
• This exam is open book and open notes. This means that you may use your textbook, course pack, handouts, notes, and assignments received and/or produced while taking this course. You may access files posted on the course website during the exam. You may also use notes and materials jointly produced with other students (e.g., your classmates) during the term.
Important Notes:
• You are provided with a separate Answer Sheet (in word document), where you should summarize your answers together with supporting documentations for all four questions per instruction. Name your answer sheet as AnswerSheet_LastName.docx.
Use separate Excel files for different questions and name them as Q1_LastName, Q2_LastName, Q3_LastName and Q4_LastName, respectively. Place the answer sheet and the four Excel files in ONE folder named after your last name.
Please submit that folder in one .zip file named as Final_LastName.zip. Submission should be made through Turn-it-in link on the eLearning course website (choose “Single File Upload”).
You do not need to submit pages of the exam other than the Answer Sheets. Please do not attempt to submit your files to my email due to size and security filtration issues.
• Partial credit may be given for incorrect work, but no credit will be given for answers (even correct ones) whose origins are unclear and cannot be traced to the submitted documentation.
• Please upload your spreadsheet files in the corresponding folders you submit. Spreadsheet file submission is not a substitute for submitting documentation, but a backup in case I have difficulty understanding your model.
1
Some Tips
If you have questions: I have tried to make the exam as clear and straightforward as possible. To ensure fairness, I will avoid answering specific, content-oriented questions for individual students. However, if you believe that the exam contains a typographical or administrative error of some kind, please bring it to my attention. Also, if you are unclear of something in the wording of the question, please explicitly note the assumption you are making and answer the question accordingly. You may send your instructor an e-mail seeking clarification, but please be aware that your instructor will have intermittent access to e-mail while you are working on the exam. In either case, please go through the following steps:
1) Be sure to read all the way through the exam. Some questions might be answered by referring to the “FAQs” section at the end of this document.
2) Check the course materials posted on eLearning. The questions may have already been answered there.
3) If your question has not been answered, please e-mail me. If I think your question is appropriate for the
class, I may post both question and answer to the entire class.
Practice clean and safe computing:
• You may want to first scratch your thoughts on a piece of paper like what we did in the class before jump the gun by building the excel spreadsheet.
• Try to layout your excel spreadsheet in a neat fashion in order to minimize your chance of making errors and my chance of grading your answer incorrectly.
• Make necessary reference to your excel model in your answer sheet.
• Make frequent back-ups of your work on different disks and/or under different file names.
Formatting: For fair grading purpose, standardization will be important. Please help by following these instructions:
• Keep your answers short but precise. Wherever a question says to “explain”, this means to give a concise answer that refers to specific results generated by your model. For example, don’t just say “yes” or give a long-winded hand-waving argument. Say “yes because X is greater than… or graph Y shows that…”. Don’t just say “likely or “unlikely”. Give a specific probability if you have one.
• Be sure to indicate where on the spreadsheets to look for supporting calculations, graphs, etc. When convenient, put those on the same spreadsheet as your written answer. Feel free to use multiple worksheets, labels, and annotations as appropriate to make it easy for us to find our way around.
• If the answer specifically asks for a number or numbers, please enter them where indicated on the answer sheet. If you feel the urge to give an explanation that would exceed the space I’ve provided, then highlight it.
• In every case, I have given you ample space for your answer. Please keep your answers short and to the point. As above, if you feel the need to provide additional detail, please do so elsewhere, and provide a reference to that explanation.
• Do not rely on me finding the essential part of your answer elsewhere. For example, do not just type in “See worksheet 2.x” and expect me to hunt for it there. Give me the short version of your answer (a number or a sentence or two is enough) and then tell me exactly where it came from.
• Please do not bother with fancy Excel tricks or techniques. I’m not interested in buttons that take me to different spreadsheets, animated graphs, or even fancy graphics. Just keep it simple, and be sure that everything you include has a purpose in communicating your thoughts.
2
• I may replicate your calculation in excel by rerunning your model. Please make sure everything is in place and replicable.
• Finally, realize that I will be grading your exams electronically. This takes somewhat longer to do and the logistics are a bit more complicated than grading paper copies. It is in your best interest to make it easy to read your exam and find your answers! 😉
Frequently Asked Questions
1. Do I have all of the information I need? Yes. I have provided you with all of the information necessary to do a good job answering each question. If you feel that you absolutely must make a specific assumption in order to proceed on a problem, please feel free to make an assumption that you think is reasonable. Be sure that you indicate in your write-up that you made the assumption and why you believe it to be appropriate. (Note: Do think carefully in making your assumptions, and be sure they are reasonable, given the situation you are modeling! An inappropriate assumption can result in a lower score.
2. Is this a “trick question”? I try not to ask trick questions. There is nothing that is hidden, nor do the problems rely on you interpreting them in a particular or unusual way. I have tried to make the exam as clear and straightforward as possible. If you are interpreting a problem in the most obvious way, that is probably the way I intended it.
3. What if I get overloaded at work or have a terrible schedule conflict that arises? Unless it is a verifiable emergency, there is no excuse for delaying your submission because the generous time window provided is far more than enough to work around your schedule.
4. Are you sure I can do this within the specified time window? If you have been conscientious about your work in the course, you should be able to address these problems reasonably well in the time allotted. Indeed, the generous time window is far more than enough to complete this exam.
The trick will be to get involved right away with one of the problems, try some things, and then pace yourself: Focus on doing things that you know how to do, don’t spend too much time trying to do something that you do not understand well, and be sure to leave yourself adequate time to write up your answers. Remember, your answers to each question can be brief and can refer to calculations in the spreadsheet. Please make it easy for me to follow your arguments.
5. I want more time! Sorry. See points 3 and 4 just above. Any consequence resulted from the late submission is solely your responsibility. If you want to send me what you have done after the deadline and then continue working on the problems for your own edification, I will be happy to look over that further work (ungraded) and provide feedback.
6. Good luck! I look forward to reading your answers. — Qiang
3
1. Logistic Regression: Company Bankruptcy (25 points)
In the Excel file named CompanyBankruptcy.xlsx (downloadable on eLearning from where you access this exam), we collected data on 74 companies that have either gone bankrupt or haven’t. The data set also contains five frequently quoted accounting ratios for each company:
1) WC/TA – working capital to total assets
2) RE/TA – retained earnings to total assets
3) EBIT/TA – earnings before interest and taxes to total assets
4) MVE/TA – market value of equity to total assets
5) S/TA – sales to total assets
Please analyze this data set by answering each of the following questions. Write your answers to these questions on the Answer Sheet for this problem (provided separately) and attach the appropriate documentation to the Answer Sheet.
a) (6 points) Exploratory analysis:
(1) Create a pivot table that shows the average of each ratio, broken down by the Yes/No values in
the variable “Bankrupt” (Column G in the data file).
(2) Use ASP to create five box plots with Y-axis showing each of the five ratios and X-axis showing
Yes/No of the variable “Bankrupt” (Column G in the data file).
(3) Based on these information, does any ratio seem to have an effect on whether a company goes
bankrupt? If so, name them?
b) (1 points) Partition the data set.
c) (10 points) Use logistics regression to classify companies as bankrupt or not, using all five of the accounting ratios.
(1) Report the coefficient for each accounting ratio.
(2) Report the Confusion Matrix and Error Report on validation set.
(3) Report the lift chart on validation set.
(4) Does this model do a good job of classifying? Are any of the ratios insignificant in the model?
d) (8 points) Experiment with logistics regression that use only two of the accounting ratios. Which pair classifies about as well as in Part c), but with both ratios significant? For that pair,
(1) Report the coefficient for each accounting ratio.
(2) Report the Confusion Matrix and Error Report on validation set.
(3) Report the lift chart on validation set.
4
2. Decision Tree: HGA, Inc. (25 points)
Human Gaming Art, Incorporated (HGA) is considering the development and launch of a new 4D virtual reality (VR) gaming chair that will be used as an online video game platform. The launch is scheduled for two years from now, which is also about the time when HGA predicts its competitors will be ready to launch competing products.
Product development costs are estimated at $5 million. (All the numbers in this problem are given in present value, so no need to discount.) The development (i.e., the design and prototype) can be completed within one year from now.
However, HGA is unsure of whether its engineering department will come up with a superior design, i.e., a “winning product” that is appealing to consumers and that technically outshines the competition. One year from now, all companies that plan to enter this market will have to demonstrate their products at the industry’s trade fairs. At that time, judging by the reaction of industry analysts and by comparing its product to those shown by the competition, HGA will be able to assess whether it has a winning product. HGA estimates the probability that its product will indeed be a superior design at 30%.
There is also a significant uncertainty about the size of the market for online VR gaming platforms. For simplicity, HGA is modeling this uncertainty as two scenarios, “large market” and “small market,” with probabilities 60% and 40%, respectively. This uncertainty will only be resolved after the product is launched two year from now. The estimated present value of the profit contribution from the product over its life-cycle in each of the four scenarios is given in the table below, in million USD.
To calculate the net profit in each scenario, the cost of development and the cost of construction of the production facility must be subtracted from the amounts given in the table. Current planning requires that the construction of the production facility start immediately, even though the product is still in development. The construction of the facility represents an expenditure of $25 million within the next year.
Write your answers to these questions on the Answer Sheet for this problem (provided separately) and attach the appropriate documentation to the Answer Sheet.
a) (13 points) Build a tree to evaluate the project (i.e., the development and launch of the new product). Find the expected value for the project.
Expected Value: _____________________
Find the risk profile for the project; that is, list all possible values of the project’s net profits and their corresponding probabilities. In particular, what is the probability of incurring no loss.
Pr(net profit ≥ 0): ______________________ (Show how you get this number) Would you recommend that HGA pursue the project as described above? Why or why not?
Large market
Small market
Superior design
100
30
Bad design
10
-10
5
There is strong disagreement in HGA’s engineering department concerning, Pr(superior design), the probability of coming up with a superior design. Harris Walters, HGA’s VP of Engineering, claims that it could be as high as 80%. Based on the tree you constructed, how high does Pr(superior design) needs to be in order for HGA to change its optimal decision?
b). (12 points) Charlie Redden, a summer intern at HGA, suggests that it may be worthwhile to delay the decision to construct the facility for one year, when the quality of the product will be known. Suppose that the facility can be ready at the same cost of $25 million if its construction is delayed for one year. (For simplicity, ignore the effects of discounting this number for an additional year and assume that the previous probability and profit assessments still hold.) Revise the tree you drew in Part a) to include the “delay construction decision” alternative. What is its expected value? (Hint: this is not value of information, but you may want to recall how we draw the tree to calculate value of information.)
Expected value of the “delay construction decision” alternative: ___________________
In reality, there is going to be all sorts of costs associated with delaying the facility construction. What is the most HGA should be willing to pay – in terms of these additional costs – in order to secure with the contractor the option to delay building the production facility for one year?
Maximum HGA should pay in additional costs to
secure the “delay construction decision” alternative: ___________________
6
3. Monte Carlo Simulation: Quentin’s Restaurant (25 Points)
Quentin grew up in Normandy of France and then moved to the US working in hospitality consulting for almost 20 years. Always fascinated about his hometown cuisine, Quentin wants to open a gourmet French restaurant in Dallas. Leveraging his experience in the hospitality industry, Quentin has developed a pro-forma monthly cash flow analysis, which is shown in Exhibit 3.1, to better understand the profitability and feasibility of this business.
Number of Customers
Average Sales
Direct Cost of Sales (per customer) Gross Margin
Server Payroll
Other Fixed Costs
Total Operating Expenses
Profit
$
2000 $25.00 30% 35,000.00
$10 $6,000 $26,000
per customer
of average Sales
=(Number of Customers)*(Average Sales)*(1-Direct Cost Percentage)
per customer
=(Server Payroll per Customer)*(Number of Customers)+(Other Fixed Costs) =(Gross Margin)-(Total Operating Expenses)
Exhibit 3.1: Monthly Cash Flow for Quentin’s Restaurant
$ 9,000.00
Unfortunately, just about every number in this analysis is uncertain. Quentin wants to build a Monte Carlo simulation model to better understand the risks of this venture. From his interaction with restaurant owners in other cities, Quentin estimates probability distributions for the variables in his pro-forma analysis as follows:
• Number of customers per month: This is very hard to predict. Quentin is optimistic about the demand but recognizes the uncertainty and assigns a lognormal distribution with mean 2,500 and standard deviation 800.
• Average sales per customer: Quentin’s prices will be set according to his estimate of what customers are willing to pay. At this point, he is uncertain about the average sales per customer and assigns a normal distribution with a mean of $30.00 per customer and a standard deviation of $7.00.
• Direct cost of sales per customer as a percentage of Average Sales: This includes the costs of food served to customers as well as other incidental expenses (e.g., laundry), which are expected to be proportional to the average sales. Quentin assigns uniform distribution between 20% and 60% to this percentage.
• Server Payroll costs per customer: Quentin assigns a triangular distribution with a most likely value of $10, an optimistic value of $7 and a pessimistic of $15.
• Other fixed costs: Quentin has identified a location in norther suburb of Dallas and estimates that rent, utilities, and insurance for that location will cost about $8,000 dollars per month. He is quite confident about this and does not feel the need to model it as uncertain.
7
Build a simulation model of this venture to answer the following questions. For all questions, write your answers on the Answer Sheet for this problem (provided separately) and submit appropriate documentation with the answer sheet.
a) What is the estimated expected profit per month? What is the probability that Quentin will lose money for a given month? What about the probability of making more than $20,000 per month with this restaurant? (10 points)
In his original analysis, Quentin assumed that he has enough capacity in his restaurant to meet all demand. Upon reflection, he realizes that the size of the restaurant may limit the number of customers that he can serve and that his probability distribution for the number of customers represents actual demand rather than the number of customers he can actually serve. With his planned location (call it Location 1), he could serve at most 3,000 customers per month. If demand exceeds this level, customers would be turned away. This capacity constraint would limit the upside potential associated with very high demands. Thinking through this, he decides to reconsider two of the other locations he had considered previously to see how they might compare in light of the uncertainty about demand. The maximum capacity (in terms of customers per month) and fixed costs (rent, utilities and insurance, etc) for the locations are shown in Exhibit 3.2. These three locations are all comparable in other respects and the assumptions about demand (= the number of potential customers) and average sales, direct costs of sales and payroll are all as above.
Exhibit 3.2: Candidate Locations for Quentin
Location
1 2 3
Maximum Capacity (Customers per month)
3000
2000
5000
Fixed Cost ($ per month)
6000
4000 10000
b) Revise your previous spreadsheet model and set it up as a parametrized simulation model (with location being the parameter). Run the three simulations, one for each location. What are the expected profits for these three locations? Based on this, which location is the best? (10 points)
c) Suppose Quentin is risk averse. To select a location, he needs to make the tradeoff between expected profit versus the risk associated with each location. Draw an overlay chart of the profits for the three locations based on your simulation in Part c) to determine if one location is riskier than the others. Can you rule out any of the locations for Quentin? Namely, is there any location is dominated by another location in terms of profitability? (Hint: drawing the overlay chart in cumulative view with smooth line may be helpful!) (5 points)
8
4. Optimization: South Grill Kitchen (25 points total)
The South Grill Kitchen (SGK) is experiencing tremendous growth in the demand for its barbecue grills. They produce four kinds of grills: discount and premium charcoal grills and discount and premium gas grills. In the coming year, the potential fulfillment requirement from its customers (retailers like Home Depot, Lowe’s, and Wal-mart) sums to a total of 300,000 grills of the type specified in Exhibit 4.1.
Exhibit 4.1: SGK’s Fulfillment requirement
Type
Discount Charcoal
Premium Charcoal Discount Gas Premium Gas
Total
Fulfillment Requirement
300,000
SGK’s production takes place in Texas and is limited by the availability of labor hours in three departments: Production, Assembly, and Packaging. Exhibit 4.2 below provides the hours available in each department and processing time required in each department by one grill of each type. The unit costs for each grill (mainly material and overheads, but not including any labor charges) are also shown.
Discount Charcoal Premium Charcoal Discount Gas Premium Gas
Available hours
Production
Hours Required Assembly
Packaging
Unit Costs ($ per grill)
Exhibit 4.2: SGK’s Capacity and Production Costs
.08 .12 .05 .10 .15 .05 .20 .25 .05 .25 .30 .05
Labor costs are $35 per hour, including benefits. Fearing the reaction of its labor force and local community, SGK will not hire, fire, or reassign labor from one department to another. That is, SGK pays for all the available hours in each department whether or not they are used.
Answer the following questions on the Answer Sheet for this problem (provided separately) and attach the appropriate documentation to the answer sheet, if necessary.
a) (2 points) Is it possible for SGK to meet the entire fulfillment requirement using existing manufacturing capacity in their current assignments? If so, how? If not, why not?
b) (10 points) SGK wants to develop a production plan to meet all the fulfillment requirements at the lowest possible cost. So SGK has been negotiating with a manufacturer based in Mexico that has available capacity. This manufacturer has agreed to supply SGK with any combination of grills at
9
120,000 80,000 70,000 30,000
25 40 75 110
25,000 40,000 20,000
costs described below. The grills would then be produced, assembled, packed, and shipped to SGK by the Mexican manufacturer with no labor required by SGK.
Exhibit 4.3: Unit Procurement Costs from Mexico
Discount Charcoal Premium Charcoal Discount Gas Premium Gas
Unit Costs ($ per grill)
Given the availability of the Mexican manufacturer, what is the optimal production mix? That is, how many grills of each type should they make and how many should they buy from the manufacturer in Mexico? What’s the corresponding optimal cost?
c) (3 points) Suppose the sales force could sell more Premium Charcoal grills to SGK’s customers. At least how much do they need to charge for additional units of this kind of grill? How many more units can they offer at this price?
d) (2 points) If SGK is able to reassign 3000-hour labor from one department to another department, should SGK make this reassignment? If so, how? If not, why?
e) (8 points) Now suppose that SGK has to commit its current manufacturing capacity at Texas to certain types of grills. For each type of grills, if not committed, SGK does not produce that type and uses the Mexican supplier to fulfill the demand requirement; once committed, SGK needs to fulfill its entire requirement, i.e., no partial fulfillment is allowed. What are those types of grills that SGK should commit its Texas capacity to? How much is the total cost in this case? What is the incremental cost of making such commitment? Formulate an integer program to help SGK make the commitment decision.
10
33 50 92 130