Question: Use Excel charts to visualize the solution

> A part-time professor of a business program at a university in Germany always tells her students that one of the first questions they should ask their new employers is whether the organization has a good customer program. Why do you think the professor e

> In this chapter, we noted the importance of defining and analyzing a problem prior to attempting to find a solution. Consider this example: One of the earliest operations research groups during World War II was conducting a study on the optimum utilizati

> A tablet manufacturer is preparing to set the price of a new model. It examines the demand and cost structure, and determines the following model to represent the relationship between demand and price D = 50 + 2P The finance department estimates that the

> Discuss the use of business analytics in sports, such as tennis, cricket, swimming, and football. Identify as many opportunities as you can for each.

> Define range names for all the entities in the Hotel Overbooking Model spreadsheet and apply them to the formulas in the model.

> Define range names for all the entities in the Crebo Manufacturing Model spreadsheet and apply them to the formulas in the model.

> Define range names for all the data and model entities in the Break-Even Decision Model spreadsheet and apply them to the formulas in the model.

> Create box-and-whisker charts for each type of cost of living adjustment in the Excel file Cost of Living Adjustments.

> For the Base Data in the Excel file Credit Risk Data, use the Histogram chart to develop a histogram with a bin width of 5 for the number of months as a customer of the bank.

> Figure 2.41 shows the 2016 U.S. federal income tax rates for the four different types of filing status. Suppose an individual is single. Write one logical IF function that will determine the correct tax rate for any income that is entered in cell B3 of a

> Elizabeth Burke has received several questions from other PLE managers regarding quality, customer satisfaction, and operational performance. She would like you to summarize some data from the Performance Lawn Equipment Database using statistical tools a

> Use the Histogram chart to construct histograms for weekly usage and waiting time in the Excel file Car Sharing Survey.

> A company forecasts its net revenue for the next three years as $172,800, $213,580, and $293,985. Find the net present value of these cash flows, assuming a discount rate of 4.2%.

> In the Accounting Professionals database, use Excel functions to find a. the maximum number of years of service. b. the average number of years of service. c. the number of male employees in the database. d. the number of female employees who have a CPA.

> Return on investment (ROI) is profit divided by investment. In marketing, ROI is determined as incremental sales times gross margin minus marketing investment, all divided by marketing investment. Suppose that a company plans to spend $3 million to place

> What decisions should be made using the average payoff strategy?

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

> For Peter Cartman’s decision, compute the standard deviation of the payoffs for each decision. What does this tell you about the risk in making the decision?

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

> 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 t

> Suppose that Peter Cartman is deciding whether to invest in a bond mutual fund or a stock fund. Both bond and stock funds are sensitive to changing market conditions. His analysis reveals that when the economy is stable, the bond and stock funds have ear

> The Excel file Airbnb Samples provides 15 sample room rates for five randomly selected Airbnb accommodations. Define range names for each of the samples and then use these range names in formulas to find the average value for each sample.

> 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, Wiscons

> A college football team is trailing 14–0 late in the game. The team just made a touchdown. If they can hold the opponent and score one more time, they can tie or win the game. The coach is wondering whether to go for an extra-point kick or a two-point co

> Consider Peter Cartman’s investment decision scenario. Use the approach described in this chapter to develop your personal utility function for the payoffs associated with this decision. Determine the decision that would result using the utilities instea

> MetaGo is a small metal parts manufacturer. It has two automatic machines (Machine A and Machine B) for daily production processes. Both the machines can produce defective metal parts. Historically, the chance of Machine A producing a defective metal par

> Drilling decisions by oil and gas operators involve intensive capital expenditures made in an environment characterized by limited information and high risk. A well site is dry, wet, or gushing. Historically, 50% of all wells have been dry, 30% wet, and

> Ravex Yacht has developed a new cabin cruiser, which they have earmarked for the medium to large boat market. A market analysis suggests a 30% probability of annual sales being 5,000 boats, 40% probability of 4,000 annual sales, and 30% probability of 3,

> For the payoff table you developed in Problem 1, determine the decision using the aggressive, conservative and opportunity-loss strategies. Data from Problem 1: Use the Outsourcing Decision Model Excel file to compute the cost of in-house manufacturing

> IM Retail deals in various products from a popular cosmetic brand Beau. The cost of stocking and selling a product varies according to whether it is in season or not. In season, the cost of a product is $12 and it sells for $18. After the season ends, th

> Perform a sensitivity analysis of David Lee’s soft drinks stall scenario. Find the optimal order quantity and optimal expected profit for probabilities of a cold weather ranging from 0.2 to 0.8 in increments of 0.2. Plot optimal expected profit as a func

> Many automobile dealers advertise lease options for new cars. Suppose that you are considering three alternatives: 1. Purchase a car outright with cash. 2. Purchase a car with 20% down and a 48-month loan. 3. Lease a car. Select an automobile whose leasi

> Modify the data in the Base Data worksheet in the Excel file Credit Risk Data using IF functions to include new columns, classifying the checking and savings account balances as low if the balance is less than $250, medium if at least $250 but less than

> Dean Kuroff started a business of rehabbing old homes. He recently purchased a circa-1800 Victorian mansion and converted it into a three-family residence. Yesterday, one of his tenants complained that the refrigerator was not working properly. Dean’s ca

> David Lee is a soft drinks stallholder at Cotton Tree Park. He must decide how many soft drink cartons to order for the coming season. Each bottle of soft drink costs $2.50 and is sold for $4.80. No inventory is carried from one season to the next. Bottl

> For Peter Cartman’s investment decision, construct a decision tree and compute the rollback values to find the best expected value decision.

> For the DoorCo Corporation decision, construct a decision tree and compute the rollback values to find the best expected value decision.

> For Peter Cartman’s investment decision, suppose that the probabilities of the scenarios are estimated to be 0.35, 0.25, and 0.40, respectively. Find the best expected value decision.

> For the DoorCo Corporation decision, 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.

> Use the Outsourcing Decision Model Excel file to compute the cost of in-house manufacturing and outsourcing for the following levels of demand: 200, 600, 1,000, and 1,400. Use this information to set up a payoff table for the decision problem.

> Figure 15.35 shows the Solver Sensitivity Report for the Burger Office Equipment scenario. Using only the information in the Sensitivity Report, answer the following questions, explaining what information you used in the Sensitivity Report. a. Explain th

> Figure 15.34 shows the Solver Sensitivity Report for the ColPal Products scenario. Using only the information in the Sensitivity Report, answer the following questions, explaining what information you used in the Sensitivity Report. a. Suppose that the e

> Figure 15.33 shows the Solver Sensitivity Report for Valencia Products. Using only the information in the Sensitivity Report, answer the following questions, explaining what information you used in the Sensitivity Report. a. Explain why the reduced cost

> For the Bicycle Inventory database, use database functions to find the following: a. The total number of leisure bicycles on hand. b. The average purchase cost and selling price of road bicycles.

> Figure 15.32 shows the Solver Sensitivity Report after solving the Crebo Manufacturing. Using only the information in the Sensitivity Report, answer the following questions. a. Explain the value of the reduced cost (- 0.3) for the number of plugs to prod

> Suppose that Sue is considering three stocks in which to invest. She has $20,000 to invest, and so she decides to equally invest in all the three stocks. The expected return is 20% for stock A, 10% for stock B, and 12% for stock C. She would like an expe

> The Body Shop has recently launched two new hand cream varieties: Rose and Lotus. It costs $2.75 to purchase the fragrance needed for each tube of Rose hand cream and $1.56 for each tube of Lotus hand cream. The marketing department has stated that at le

> Obtain the Answer and Sensitivity Reports for Dara’s investment scenario. Develop a set of charts that visualize the key information in these reports.

> Use Excel charts to visualize the solution for the J&M Manufacturing problem using the Solver Sensitivity Report with auxiliary variables.

> Construct a what-if analysis for Foodpanda as the delivering goal varies from 15 to 35 minutes in increments of 5 minutes.

> Analyze the sensitivity of the optimal solution for the local university as the total cost and available lecturers are varied. Write a short summary memo to the university management in which you outline the results and make recommendations.

> Jordan owns a magazine stall and he has 150 old magazines in storage. He is planning to sell some of his magazines with the highest price to gain a profit of $8.00 and some with the lowest price to gain a profit of $4.50. By using the what-if analysis, h

> For the Burger Office Equipment scenario, use the spreadsheet model to answer the following questions by changing the parameters and re-solving the model. Answer each question independently relative to the original problem. a. If 25% of the pine is deeme

> In the Credit Risk Data file, use database functions to find the average amount of savings for each of the situations listed in Problem 16. Data from Problem 16: Apply the Advanced Filter to the Credit Risk Data (Base Data worksheet) to find the followi

> Hahn Engineering would like to increase its return, but the project manager knows that this will require more cash and/or personnel. Conduct a what-if analysis of the optimal solution as the cash limitation varies from $150,000 to $270,000 in increments

> For WoodFurni, solve the model using auxiliary variables. Obtain the Solver Sensitivity Report, and answer the following: a. Explain the sensitivity information for the objective coefficients. What happens if the profit on wardrobes is decreased by $30?

> For the Fruity Juices scenario, formulate and solve a linear optimization model using the auxiliary variable cells method and write a short memo explaining the sensitivity information.

> For Starbucks coffee company, formulate and solve a linear optimization model using the auxiliary variable cells method and write a short memo to the outlet manager explaining the sensitivity information.

> Box Tile Company, use the auxiliary variable technique to handle the bound constraints on grade I and grade III tiles and obtain the Solver Sensitivity Report. Answer the following: a. Explain the sensitivity information for the objective coefficients. W

> Marketing agents have various media alternatives in which to advertise and must determine which to use, the number of insertions in each, and the timing of insertions to maximize advertising effectiveness within a limited budget. Suppose that Lilydale Tu

> Obtain the Solver Sensitivity Report for the Hal Chase portfolio optimization model. Explain how to interpret the Lagrange multipliers for the constraints.

> Figure 15.39 shows the Solver Sensitivity Report for the Markowitz portfolio model. a. Explain how to interpret the Lagrange multiplier value for the target portfolio return. b. Suppose the target return is increased from 10% to 11%. How much is the mini

> For the nonlinear pricing decision model, suppose that the company wants to keep the price at a maximum $300. Note that the solution in Figure 14.19 will no longer be feasible. Modify the spreadsheet model to include a constraint on the maximum price and

> For the Hansel Corporation, obtain the Solver Sensitivity Report and use it to answer the following: a. A labor strike in India leads to a shortage of 20,000 units of additive C. What should the production manager do? b. Management is considering raising

> Apply the Advanced Filter to the Credit Risk Data (Base Data worksheet) to find the following: a. All new car loans obtained for single females. b. All furniture loans obtained for single individuals who rent. c. All education loans obtained by unskilled

> For the ColPal Products scenario, use the spreadsheet model to answer the following question by changing the parameters and re-solving the model. Answer each question independently relative to the original problem. a. Suppose that the exposure for TV a

> For Problem (Shafer Office Supplies), obtain the Solver Sensitivity Report and answer the following questions: a. Suppose that 500 units of extra supply are available (and that the cost of this extra capacity is a sunk cost). To which distribution center

> StraitCarrier and SunShipping, obtain the Solver Sensitivity Report and write a short memo to the directors of both the companies explaining your results.

> For Dara’s investment situation, obtain the Solver Sensitivity Report and interpret the information, making recommendations that Dara might consider for her portfolio.

> For Susan’s cake recipe scenario, obtain the Solver Sensitivity Report and write a short memo to Susan explaining the sensitive information in language that she can understand.

> Model and solve an optimization model for planting corns and carrots. Using the Solver Sensitivity Report, answer the following questions, explaining what information you used in Sensitive Report. a. What impact will changing the required minimum number

> Model and solve an optimization model for the Morton Supply Company. Obtain the Solver Sensitivity Report for your solution, and clearly explain all the key information in language that the production manager would understand.

> Bangs Leisure Chairs. The Solver Sensitivity Report for the optimal solution is shown in Figure 15.38. Clearly explain the information it provides.

> Dion must now purchase at least 5 units of Product A, 15 units of Product B, and 10 units of Product C to fulfill his demand. Use Solver to find an optimal solution and compare the Solver solution with the result you found earlier using the Solver Sensit

> Use the Sensitivity Report for the Camm Textiles scenario (Figure 15.37) to answer the following: a. Explain the reduced cost (0.14) for Fabric 2 Purchased in terms of the original data, and why it makes sense. b. Explain the shadow price for the dobbie

> Use the Excel file Credit Risk Data that was introduced in this chapter to perform the following activities: a. Compute the combined checking and savings account balance for each record in the Base Data worksheet. Then sort the records by the number of m

> Obtain the Solver Sensitivity report for the GAC transportation model. a. What must the unit shipping cost be to make it attractive to ship from Marietta to Cleveland instead of from Minneapolis? b. Why are the Allowable Increases for all demand constrai

> For the Valencia Products scenario, use the spreadsheet model to answer the following questions by changing the parameters and re-solving the model. Answer each question independently relative to the original problem. a. If the unit profit for SpeedBuste

> Hatch Financial, which recently absorbed another firm, is now downsizing and must relocate five information systems analysts from recently closed locations. Unfortunately, there are only three positions available for five people. Salaries are fairly unif

> Mix&Match Ltd., a small fertilizer manufacturer, produces two active formulae of fertilizer mixtures. The mixtures must contain a minimum quantity of four different nutrients: 45 grams of nutrient A, 25 grams of nutrient B, 10 grams of nutrient C, an

> Steven is a 20-year-old male who suffers from chronic malnutrition. His doctor advices him to follow a dietary plan that will help him recover efficiently. He would need to take at least 400 grams of protein, 350 milligrams of iron, 300 milligrams of zin

> Panasonic Corporation is a multinational electronics corporation headquartered in Osaka, Japan. In the face of tight competition in the electronics industry and to meet the high level of demand, Panasonic’s factory operates 24 hours a d

> ACTN Partners, an accounting firm, recently received project offers from three different companies, A, B, and C, to analyze and prepare accounting data. The companies need the analysis and report to be completed in a month’s time. Due t

> For the Brewer Services scenario described in this chapter, suppose that five permanent employees are hired. Find an optimal solution to minimize the number of part-time employees needed.

> Suppose the distances that Mohan Raja, a mechanical technician for Canon who repairs and services photocopy machines, travels between five different companies that rent photocopy machines from Canon are as follows: Set up and solve a traveling salesperso

> An IT support group at Thomson State College has seven projects to complete. The time each will take and project deadlines (both given in number of days) are shown next. a. Sequence the projects to minimize the average lateness. b. Sequence the projects

> Filter the data in the Cereal database to obtain the cereals that are of type C. What are the average calories and total sales for this type of cereal?

> Dilan is a property investor. Recently he has been interested to invest in three different types of property: a shoplot, a condominium, and a double-story house. The shoplot is a high-risk investment, often resulting in heavy losses but occasionally comi

> ToyDoc is a major toy manufacturer. Its financial advisor is analyzing the effect of the direct material cost of plastic used to make the toys on profits earned for two of their main products. These data are available in the Excel file ToyDoc. Suppose th

> Many manufacturing situations, for example, the production of such large and complex items as aircraft or machines, exhibit a learning effect in which the production time per unit decreases as more units are produced. This is often modeled by a power cur

> We noted that the least-squares coefficients in a regression model are found by minimizing the sums of squares of the errors, as given in equation. This is a nonlinear optimization problem. Using the Home Market Value data, set up a spreadsheet model to

> ElectroMart wants to identify a location for a warehouse that will ship to five retail stores. The coordinates and annual number of truckloads are given here. Develop and solve a model to find the best location, assuming that straight-line distances can

> A franchise of a chain of H&M, a Swedish multinational clothing retail company, wants to determine the best location to attract customers from three shopping malls. The coordinates of the three shopping malls are as follows: The population shop at On

> In the hotel pricing problem, suppose that the hotel is considering adding suites to its room mix. Based on an analysis of local competitors, suites can sell for a rate of $180, and they expect to sell 20 per day to business travelers. The price elastici

> For the pricing decision model, suppose that the company wants to keep the price at a maximum of $500. Note that the solution in Figure will no longer be feasible. Modify the spreadsheet model to include a constraint on the maximum price and solve the mo

> Intel, the largest semiconductor manufacturing company in the world, sells two major types of computer chips, type A and type B. The sales of these two chips are not independent of each other (in economics, we call these substitutable products because if

> The demand for organic chicken meat is quite sensitive to price. Typically, there is an inverse relationship between its demand and price; when price decreases, demand increases and vice versa. One major organic chicken supplier found that when the price

> Use Excel’s filtering capability in the Car database to extract a. all Japanese cars. b. all cars having more than 400 horsepower. c. all cars having less than 8 cylinders and weighing more than 3,500 kilograms.

> A manufacturer of kitchen appliances is preparing to set the price on a new blender. Demand is thought to depend on the price and is represented by the model D = 2,000 - 3P The accounting department estimates that the total costs can be represented by C

> Foodpanda is an online food delivery brand headquartered in Berlin, Germany, and operating in over 10 countries. It is establishing a new business to serve customers in Selangor, a state on the west coast of Peninsular Malaysia, covering cities like Peta