Computer Science Business Analysis for Managers Questions

HOMEWORK (for practice and discussion): Ch 8 # 12, 13, 20, 21, 24; Ch 9# 6, 8, 9, 10

Ch 13 # 6, 7, 13, 14; Ch 15 # 8, 9

Chapter 16 # 3, 5, 6, 8, 10, 13

chapter 8

12. Using the data in the Excel file Student Grades, apply the Excel Regression tool using the midterm grade as the independent variable and the final exam grade as the dependent variable. Interpret all key regression results, hypothesis tests, and confidence intervals in the output.

13. Using the Excel file Weddings, apply the Regression tool using the wedding cost as the dependent variable and attendance as the independent variable. a. What is the regression model? b. Interpret all key regression results, hypothesis tests, and confidence intervals in the output. c. If a couple is planning a wedding for 175 guests, how much should they budget?

20. The Excel file World Tour Sales provides data on sales (£) and the number of radio, TV, and newspaper ads promoting the world tour for a group of countries. Develop simple linear regression models for predicting sales as a function of the number of each type of ad. Compare these results to a multiple linear regression model using both independent variables. State each model and explain R-Square, Significance F, and p-values.

21. Using the data in the Excel file Home Market Value, develop a multiple linear regression model for estimating the market value as a function of both the age and size of the house. State the model and explain R2 , Significance F, and p-values.

24. Using the data in the Excel file Credit Card Spending, develop a multiple linear regression model for estimating the average credit card expenditure as a function of both the income and family size. Predict the average expense of a family that has two members and an income of $188,000 per annum, and another that has three members and an income of $39,000 income per annum.

Chapter 9

6. For the data in the Excel file Airport Passengers, do the following: a. Develop a spreadsheet for forecasting the number of airport passengers using a simple three-period moving average. b. Compute MAD, MSE, and MAPE error measures.8. The Excel file Closing Stock Prices provides data for four stocks and the Dow Jones Industrial Average over a one-month period. a. Develop a spreadsheet for forecasting each of the stock prices and the DJIA using a simple twoperiod and three-period moving average. b. Compute MAD, MSE, and MAPE and determine whether two or three moving average periods is better.

9. The Excel file Stock Prices provides data for four stocks and the stock exchange average over a 12-day period. a. Use Excel’s Data Analysis Exponential Smoothing tool to forecast each of the stock prices using simple exponential smoothing with a smoothing constant of 0.3. b. Compute the MAD, MSE, and MAPE for each of the models. c. Does a smoothing constant of 0.1 or 0.5 yield better results?

10. For the data in the Excel file Airport Passengers, do the following: a. Develop a spreadsheet for forecasting the number of airport passengers using simple exponential smoothing with smoothing constants from 0.1 to 0.9 in increments of 0.1. b. Using MAD, MSE, and MAPE as guidance, find the best smoothing constant.

chapter 13

6. Burger Office Equipment produces two types of desks, standard and deluxe. Deluxe desks have oak tops and more-expensive hardware and require additional time for finishing and polishing. Standard desks require 70 board feet of pine and 10 hours of labor, whereas deluxe desks require 50 board feet of pine, 20 square feet of oak, and 18 hours of labor. For the next week, the company has 5,000 board feet of pine, 750 square feet of oak, and 400 hours of labor available. Standard desks net a profit of $250, and deluxe desks net a profit of $350. All desks can be sold to national chains such as Staples or Office Depot. a. Identify the decision variables, objective function, and constraints in simple verbal statements. b. Mathematically formulate a linear optimization model.

7. A paper mill produces three grades of paper: X, Y, and Z. The mill has a budget of €100,000 to invest in the production of these three different types of paper. The cost per ton and expected profit over the next two years is given in the table. Paper Grade X Y Z Cost/ton €10 €12 €7 Profit/ton €30 €40 €20 a. Identify the decision variables, objective function, and constraints in simple verbal statements. b. Mathematically formulate a linear optimization model.

13. Implement the linear optimization model that you developed for Burger Office Equipment in Problem 6 on a spreadsheet and use Solver to find an optimal solution. Interpret the Solver Answer Report, identify the binding constraints, and verify the values of the slack variables by substituting the optimal solution into the model constraints.

14. Dion’s Décor shop has three bestseller products. Dion has a budget of $2,500 to purchase these products from an outstation wholesale warehouse. The cost and the profits made from the sale of each of these products are given in the table. Product A B C Purchase price $56 $35 $98 Profit $28 $19 $42 Use Solver to find an optimal solution. Interpret the Solver Answer Report, identify the binding constraints, and verify the values of the slack variables by substituting the optimal solution in the model constraints

chapter 15

8. Figure 15.35 shows the Solver Sensitivity Report for the Burger Office Equipment scenario from Problems 6 and 13 in Chapter 13. Using only the information in the Sensitivity Report, answer the following questions, explaining what information you used in the Sensitivity Report. a. Explain the reduced cost associated with deluxe desks. b. If 25% of the pine is deemed to be cosmetically defective, how will the optimal solution be affected? c. The shop supervisor is suggesting that the workforce be allowed to work an additional 50 hours at an overtime premium of $18/hour. Is this a good suggestion? Why or why not? ▶▶ Figure 15.33 Solver Sensitivity Report for Valencia Products Problem ▶▶ Figure 15.34 Solver Sensitivity Report for ColPal Products Problem Chapter 15 Optimization Analytics 625 ▶▶ Figure 15.35 Solver Sensitivity Report for Burger Office Equipment Problem d. If the unit profit for standard desks is increased to $280, how will the optimal solution and total profit be affected? e. If the unit profit of standard desks is only $190, how will the optimal solution and total profit be affected?

9. Figure 15.36 shows the Solver Sensitivity Report for the student investment scenario from Problems 7 and 14 from Chapter 13. Using only the information in the Sensitivity Report, answer the following questions, explaining what information you used in the Sensitivity Report. a. How much would the return on stock B have to be for the optimal solution to invest fully in that stock? b. How much would the return on stock C have to increase in order to invest fully in that stock? c. Explain the value of the shadow price for the total investment constraint. If the student could borrow $1,000 at 8% a year to increase her total investment, what would you recommend and why?

*3. The DoorCo Corporation is a leading manufacturer of garage doors. All doors are manufactured in their plant in Carmel, Indiana, and shipped to distribution centers or major customers. DoorCo recently acquired another manufacturer of garage doors, Wisconsin Door, and is considering moving its wood door operations to the Wisconsin plant. Key considerations in this decision are the transportation, labor, and production costs at the two plants. Complicating matters is the fact that marketing is predicting a decline in the demand for wood doors. The company developed three scenarios: a. Demand falls slightly, with no noticeable effect on production. b. Demand and production decline 20%. c. Demand and production decline 40%. The following table shows the total costs under each decision and scenario. Slight Decline 20% Decline 40% Decline Stay in Carmel $1,000,000 $800,000 $840,000 Move to Wisconsin $1,100,000 $950,000 $750,000 What decision should DoorCo make using each of the following strategies? a. aggressive strategy b. conservative strategy c. opportunity-loss strategy

*5. As the marketing manager of a major perfume company, you are considering using either one of two ingredients, Neroli oil or Orris root, in the newest perfume to be launched by the company. The cost to use Orris root is much more expensive in comparison to Neroli oil, but the company might not gain a substantial amount of business if it doesn’t use Orris root as an ingredient. You have developed the following payoff table, estimating the company’s first year profits. Estimated 1st Year Profit (in $ million) Neroli Oil Orris Root Low Demand 2 –5 High Demand 4 10 What decision will you make using each of the following strategies? a. aggressive strategy b. conservative strategy c. opportunity-loss strategy

*6. For the DoorCo Corporation decision in Problem 3, compute the standard deviation of the payoffs for each decision. What does this tell you about the risk in making the decision?

*8. For the perfume company decision in Problem 5, compute the standard deviation of the payoffs for each decision. What does this tell you about the risk in making the decision?

10. For the DoorCo Corporation decision in Problem 3, suppose that the probabilities of the three scenarios are estimated to be 0.15, 0.40, and 0.45, respectively. Find the best expected value decision.

13. For the DoorCo Corporation decision in Problems 3 and 10, construct a decision tree and compute the rollback values to find the best expected value decision.