Problem 1: What-if and Goal-seeking analysis
George is planning to set up a new hair salon in a trendy inner city Melbourne suburb. George estimates that his fixed annual costs (which include rent, loan interest, electricity expenses, etc.) will be $150,000. He is planning on employing a total of five hair stylists, who will each work 1,600 hours per year. He plans to charge a fixed price for haircuts ($35 for men, $60 for women), and estimates that 65% of his customers will be women. He is having difficulty determining the pay structure for the hair stylists, and is considering the following three possibilities: Scenario 1: Fixed hourly rate of $40 per hour.
Scenario 2: Fixed hourly rate of $20 per hour + a commission on each haircut of 20% of the cost of the haircut.
Scenario 3: No fixed hourly rate, but a commission on each haircut of 30% of the cost of the haircut.
George would like to know the following:
• Under each of the above scenarios, what would be the break-even point in the number of haircuts; i.e., how many haircuts must the salon deliver in order to just cover total costs?
• In Scenario 2 (i.e., fixed hourly rate + commission), assuming the salon would deliver a total of 5,000 hairstyles for the year, what would the commission need to be in order for the total costs to be the same as per Scenario 1?
Create an Excel spreadsheet that George can use as a decision support tool to answer his questions. The spreadsheet should be designed such that George would be able to use the spreadsheet without any additional documentation..
Problem 2: Portfolio Planning using optimization
Gerry has just obtained a job in portfolio planning at a newly created investment company. His manager has given him the responsibility of investing $10 million, and he must maximise the expected return of the investment over the next year. He has four investment alternatives available to him. The expected return for each of these alternatives, together with the maximum amount he is able to invest in each of these alternatives, is given in the following table.
There are some additional constraints on how the funds can be invested:
• a maximum of 35% of the funds may be placed in stocks;
• a minimum of 25% of the funds is to be placed in cash;
• the combined amount in bonds and stocks cannot exceed the combined amount in cash and property;
• all of the available $10 million must be invested; and
• each investment must be in multiples of $10,000.
Part 1: Set this problem up as a linear programming model in Excel, and use Solver to determine how the $10 million should be invested. What is the overall return (in dollars terms)? What is the overall return as a percentage of the $10 million invested?
Part 2: The expected return rates for the investment alternatives given in the table above are only estimates. Assuming that the funds will be invested as per your solution in Part 1, use at least 100 simulations to investigate the distribution in overall return that would be expected under the assumption that each of the returns in the above table is normally distributed about the expected return with a standard deviation of 10% for cash, 10% for listed property, 10% for Australian Bonds, and 50% for Stocks.
Use a chart (e.g., a histogram) to show the distribution of the returns in your simulations. Is the overall return normally distributed? What is its standard deviation? Provide the 95% confidence interval for the mean return.
Problem 3: A Monte Carlo Simulation Problem
Harry owns a bookshop next door to George's hair salon. Inside the bookshop is a small area where customers can purchase a cup of coffee and snacks while they sit down and browse through books. Harry has recently started selling chocolate muffins, which have been very popular with the customers.
Harry purchases the muffins from a wholesaler at $2.00 per muffin, and sells them to his customers at $6.00 per muffin. Sometimes Harry has muffins left at the end of the day. Fortunately, he is still able to sell these leftovers at $0.50 per muffin. However sometimes he has more customers than muffins, and he fears that in such cases he will not only lose out on the profit of $4.00/muffin, but may lose potential book-buying customers to his competitor up the street, who has also started selling snacks and refreshments in his store.
Harry knows that you are doing a course in decision support systems, and has asked you to help him determine the optimal number of muffins to order each day. Fortunately, Harry has kept records over the time he has been selling the muffins, and estimates that the daily demand for muffins can be approximated by a normal distribution with a mean of 75, and a standard deviation of 15. Use Excel to create a simulation model to assist Harry in determining the optimal number of muffins to order daily. You should produce a chart showing how his expected profit depends on the number of muffins he orders. From this chart estimate the optimal number of muffins to order.
Problem 4: Mining a Bank Marketing dataset
You have just started working at a bank, and your boss has recently become interested in data mining, and particularly the opportunities that it might provide for direct marketing of some new investment products that his bank has created. Your boss knows that you have taken a course in decision support systems that included a component on data mining, and he would like you to provide him with some information on data mining and its use in direct marketing. He has referred you to the following paper, which he recently became aware of, but, given his lack of background knowledge in this area, finds difficult to understand: "Using Data Mining for Bank Direct Marketing: An Application of the CRISP-DM Methodology", by Moro, Laureano and Cortez (2011). He would like you to access the datasets used in this paper, apply a number of data mining algorithms to this data, and to write a report on your investigation and findings.