Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Business Analytics (MindTap Course List)
8th Edition
ISBN: 9781305947412
Author: Cliff Ragsdale
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 4, Problem 5QP
Summary Introduction
To determine: The sensitivity report using solver.
a)
Summary Introduction
To determine: The smallest value for X3.
b)
Summary Introduction
To determine: The optimal objective function for the given condition.
c)
Summary Introduction
To determine: The optimal objective function when the RHS of 1st constraint increases to 7.
d)
Summary Introduction
To determine: The optimal objective function when the RHS of 1st constraint decreases to 1.
e)
Summary Introduction
To determine: Whether the current solution remain optimal under the given condition.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Answer the following questions based on the Tornado and Sensitivity graphs given below.
Which input variables are the most and the least impactful variables? Please mention two for each case.
How the objective function changes by changing the most impactful input variable?
Solve this problem in three ways;
1. Graphical
2. Excel solver
3. Sensitivity analysis/ report.
a) What will happen to the optimal solution if the unit profit on x increase by 300?
b) what will happen happen to the optimal solution if the unit profit on y decreases by 230?
c) What will happen to the optimal solution if the unit profit on both x and y change to 700?
d) Complete the sensitivity report with upper and lower limit.
1. The JAV Company manufactures two types of lamps; Special lamp and regular lamp. Each special lamp requires 4 pounds of brass and each regular lamp requires 8 pounds of brass. During each production period, the company's brass supply limited to 640 pounds. Each special lamp requires 6 hours of milling time in the machines and each regular lamp requires 2 hours of milling time in the machine, The company's machine are available only for 360 hours in each production period. Each special lamp requires 5 light bulbs that must be imported from…
Consider the following linear programming problem:
Maximize 6x+5y (OBJ)
Subject to x+y≤6 (1)
2x+y≤8 (2)
y≤5 (3)
x,y≥0
What is the optimal solution to this problem? What is the optimal value of the objective function?
Solve this model by using graphical analysis based on the Corner Point Solution Method.
Answer questions below.
2.1. Clearly plot and label the constraints. Show your calculations for drawing constrain line. The solution without calculation will not be accepted.
2.2. Develop and shade the feasible region. Use graph paper.
2.3. Compute all the corner points or extreme points and their coordinates (i.e. the values of x and y). The solution without calculation will not be accepted.
2.4. Determine the optimal solution (i.e. the values of x and y) using corner points method.
Why your solution is optimal?
2.5 Compute the value of the objective function at the optimal…
Chapter 4 Solutions
Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Business Analytics (MindTap Course List)
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, management and related others by exploring similar questions and additional content below.Similar questions
- The eTech Company is a fairly recent entry in the electronic device area. The company competes with Apple. Samsung, and other well-known companies in the manufacturing and sales of personal handheld devices. Although eTech recognizes that it is a niche player and will likely remain so in the foreseeable future, it is trying to increase its current small market share in this huge competitive market. Jim Simons, VP of Production, and Catherine Dolans, VP of Marketing, have been discussing the possible addition of a new product to the companys current (rather limited) product line. The tentative name for this new product is ePlayerX. Jim and Catherine agree that the ePlayerX, which will feature a sleeker design and more memory, is necessary to compete successfully with the big boys, but they are also worried that the ePlayerX could cannibalize sales of their existing productsand that it could even detract from their bottom line. They must eventually decide how much to spend to develop and manufacture the ePlayerX and how aggressively to market it. Depending on these decisions, they must forecast demand for the ePlayerX, as well as sales for their existing products. They also realize that Apple. Samsung, and the other big players are not standing still. These competitors could introduce their own new products, which could have very negative effects on demand for the ePlayerX. The expected timeline for the ePlayerX is that development will take no more than a year to complete and that the product will be introduced in the market a year from now. Jim and Catherine are aware that there are lots of decisions to make and lots of uncertainties involved, but they need to start somewhere. To this end. Jim and Catherine have decided to base their decisions on a planning horizon of four years, including the development year. They realize that the personal handheld device market is very fluid, with updates to existing products occurring almost continuously. However, they believe they can include such considerations into their cost, revenue, and demand estimates, and that a four-year planning horizon makes sense. In addition, they have identified the following problem parameters. (In this first pass, all distinctions are binary: low-end or high-end, small-effect or large-effect, and so on.) In the absence of cannibalization, the sales of existing eTech products are expected to produce year I net revenues of 10 million, and the forecast of the annual increase in net revenues is 2%. The ePIayerX will be developed as either a low-end or a high-end product, with corresponding fixed development costs (1.5 million or 2.5 million), variable manufacturing costs ( 100 or 200). and selling prices (150 or 300). The fixed development cost is incurred now, at the beginning of year I, and the variable cost and selling price are assumed to remain constant throughout the planning horizon. The new product will be marketed either mildly aggressively or very aggressively, with corresponding costs. The costs of a mildly aggressive marketing campaign are 1.5 million in year 1 and 0.5 million annually in years 2 to 4. For a very aggressive campaign, these costs increase to 3.5 million and 1.5 million, respectively. (These marketing costs are not part of the variable cost mentioned in the previous bullet; they are separate.) Depending on whether the ePlayerX is a low-end or high-end produce the level of the ePlayerXs cannibalization rate of existing eTech products will be either low (10%) or high (20%). Each cannibalization rate affects only sales of existing products in years 2 to 4, not year I sales. For example, if the cannibalization rate is 10%, then sales of existing products in each of years 2 to 4 will be 10% below their projected values without cannibalization. A base case forecast of demand for the ePlayerX is that in its first year on the market, year 2, demand will be for 100,000 units, and then demand will increase by 5% annually in years 3 and 4. This base forecast is based on a low-end version of the ePlayerX and mildly aggressive marketing. It will be adjusted for a high-end will product, aggressive marketing, and competitor behavior. The adjustments with no competing product appear in Table 2.3. The adjustments with a competing product appear in Table 2.4. Each adjustment is to demand for the ePlayerX in each of years 2 to 4. For example, if the adjustment is 10%, then demand in each of years 2 to 4 will be 10% lower than it would have been in the base case. Demand and units sold are the samethat is, eTech will produce exactly what its customers demand so that no inventory or backorders will occur. Table 2.3 Demand Adjustments When No Competing Product Is Introduced Table 2.4 Demand Adjustments When a Competing Product Is Introduced Because Jim and Catherine are approaching the day when they will be sharing their plans with other company executives, they have asked you to prepare an Excel spreadsheet model that will answer the many what-if questions they expect to be asked. Specifically, they have asked you to do the following: You should enter all of the given data in an inputs section with clear labeling and appropriate number formatting. If you believe that any explanations are required, you can enter them in text boxes or cell comments. In this section and in the rest of the model, all monetary values (other than the variable cost and the selling price) should be expressed in millions of dollars, and all demands for the ePlayerX should be expressed in thousands of units. You should have a scenario section that contains a 0/1 variable for each of the binary options discussed here. For example, one of these should be 0 if the low-end product is chosen and it should be 1 if the high-end product is chosen. You should have a parameters section that contains the values of the various parameters listed in the case, depending on the values of the 0/1 variables in the previous bullet For example, the fixed development cost will be 1.5 million or 2.5 million depending on whether the 0/1 variable in the previous bullet is 0 or 1, and this can be calculated with a simple IF formula. You can decide how to implement the IF logic for the various parameters. You should have a cash flows section that calculates the annual cash flows for the four-year period. These cash flows include the net revenues from existing products, the marketing costs for ePlayerX, and the net revenues for sales of ePlayerX (To calculate these latter values, it will help to have a row for annual units sold of ePlayerX.) The cash flows should also include depreciation on the fixed development cost, calculated on a straight-line four-year basis (that is. 25% of the cost in each of the four years). Then, these annual revenues/costs should be summed for each year to get net cash flow before taxes, taxes should be calculated using a 32% tax rate, and taxes should be subtracted and depreciation should be added back in to get net cash flows after taxes. (The point is that depreciation is first subtracted, because it is not taxed, but then it is added back in after taxes have been calculated.) You should calculate the company's NPV for the four-year horizon using a discount rate of 10%. You can assume that the fixed development cost is incurred now. so that it is not discounted, and that all other costs and revenues are incurred at the ends of the respective years. You should accompany all of this with a line chart with three series: annual net revenues from existing products; annual marketing costs for ePlayerX; and annual net revenues from sales of ePlayerX. Once all of this is completed. Jim and Catherine will have a powerful tool for presentation purposes. By adjusting the 0/1 scenario variables, their audience will be able to see immediately, both numerically and graphically, the financial consequences of various scenarios.arrow_forwardAnother way to derive a demand function is to break the market into segments and identify a low price, a medium price, and a high price. For each of these prices and market segments, we ask company experts to estimate product demand. Then we use Excels trend curve fitting capabilities to fit a quadratic function that represents that segments demand function. Finally, we add the segment demand curves to derive an aggregate demand curve. Try this procedure for pricing a candy bar. Assume the candy bar costs 0.55 to produce. The company plans to charge between 1.10 and 1.50 for this candy bar. Its marketing department estimates the demands shown in the file P07_47.xlsx (in thousands) in the three regions of the country where the candy bar will be sold. What is the profit-maximizing price, assuming that the same price will be charged in all three regions?arrow_forwardSeas Beginning sells clothing by mail order. An important question is when to strike a customer from the companys mailing list. At present, the company strikes a customer from its mailing list if a customer fails to order from six consecutive catalogs. The company wants to know whether striking a customer from its list after a customer fails to order from four consecutive catalogs results in a higher profit per customer. The following data are available: If a customer placed an order the last time she received a catalog, then there is a 20% chance she will order from the next catalog. If a customer last placed an order one catalog ago, there is a 16% chance she will order from the next catalog she receives. If a customer last placed an order two catalogs ago, there is a 12% chance she will order from the next catalog she receives. If a customer last placed an order three catalogs ago, there is an 8% chance she will order from the next catalog she receives. If a customer last placed an order four catalogs ago, there is a 4% chance she will order from the next catalog she receives. If a customer last placed an order five catalogs ago, there is a 2% chance she will order from the next catalog she receives. It costs 2 to send a catalog, and the average profit per order is 30. Assume a customer has just placed an order. To maximize expected profit per customer, would Seas Beginning make more money canceling such a customer after six nonorders or four nonorders?arrow_forward
- Below tables show the Answer Report and the Sensitivity Report of the optimization Based on these two tables, answer the following questions. Write the objective What is the z value (objective function’s value) Identify the sensitivity ranges for the objectivearrow_forwardHeller Manufacturing has two production facilities that manufacture baseball gloves. Production costs at the two facilities differ because of varying labor rates, local property taxes, type of equipment, capacity, and so on. The Dayton plant has weekly costs that can be expressed as a function of the number of gloves produced TCD(X) = X2 − X + 4 where X is the weekly production volume in thousands of units and TCD(X) is the cost in thousands of dollars. The Hamilton plant's weekly production costs are given by TCH(Y) = Y2 + 2Y + 3 where Y is the weekly production volume in thousands of units and TCH(Y) is the cost in thousands of dollars. Heller Manufacturing would like to produce 9,000 gloves per week at the lowest possible cost. (a) Formulate a mathematical model that can be used to determine the optimal number of gloves to produce each week at each facility. min s.t. = 9 X, Y ≥ 0 (b) Use Excel Solver or LINGO to find the…arrow_forwardA portfolio manager summarizes the input from the macro and micro forecasters in the following table: Micro Forecasts AssetExpected Return (%)BetaResidual Standard Deviation (%) Stock A 22 1.4 53 Stock B 21 1.8 61 Stock C 19 0.7 58 Stock D 16 1.1 46 Macro Forecasts AssetExpected Return (%)Standard Deviation (%) T-bills 7 0 Passive equity portfolio 16 20 Calculate the following for a portfolio manager who is not allowed to short sell securities. If allowed to short sell securities, the manager's Sharpe ratio is 0.4647. a. What is the cost of the restriction in terms of Sharpe’s measure? (Do not round intermediate calculations. Enter your answer as decimals rounded to 4 places.) cost of restriction= b. What is the utility loss to the investor (A = 1.9) given his new complete portfolio? (Do not round intermediate calculations. Round your answers to 2 decimal places.)arrow_forward
- KrazySockzThe KrazySockz Company is known for their colorful yetcomfortable socks. They have forecasted their demand for the nextsix months as follows: Month 1 demand is 30,000, month 2demand is 50,000, month 3 demand is 20,000, month 4 demand is10,000, month 5 demand is 15,000 and month 6 demand is 20,000.At the beginning of month 1 they have 15,000 pairs of socks onhand. KrazySockz currently has 40 production employees. Each productionemployee costs the company the company $4,000 per month ($3,200 salary and $800in other costs) per month. Production employees can work up to 160 hours permonth before they must be paid overtime. Overtime is paid at a rate of $30 per hourand production employees can not work more than 30 hours per month overtime. Ittakes 15 minutes of labor and $0.75 of raw material to produce a pair of socks.KrazySockz can hire or fire employees each month. Hiring a production employeeincurs a cost of $3,500 and firing a production employee costs $4,500. At the end…arrow_forwardConsider the Answer and Sensitivity Reports at the end of the questionnaire to answer the following questions Objective Function : Max Z = 3x1 +2x2 Sensitivity Ranges : C1 (3) Upper limit 3 +∞ = ∞ Lower limit 3-2 = 1 C2 (2) Upper limit 4+2 = 6 Lower limit 2-3.5 = -1.5 What are the constraint quantity values? Identify the sensitivity ranges for the constraint quantity Are there any unused resources? If so, which ones and what are the amount of unused resources?arrow_forwardA political candidate is planning his media budget for an upcoming election. He has $90,500 to spend. His political consultants have provided him with the following estimates of additional votes as a result of the advertising effort: -for every small sign placed by the roadside, he will garner 20 additional votes. -for every large sign placed by the roadside, he will garner 30 additional votes. -for every thousand bumper stickers placed on cars, he will garner 15 additional votes. -for every hundred personal mailings to voters, he will garner 40 additional votes. -for every radio ad heard, he will garner 485 additional votes. The costs for each of the advertising devices, along with the practical minimum and maximum that should be planned for each, are shown on the following table. How should the candidate plan to spend his campaign money? Advertising Medium Cost Minimum Maximum Bumper stickers (thousands) $30…arrow_forward
- The Tennessee Jack Distillery produces custom-blendedwhiskey. A particular blend consists of rye and bourbonwhiskey. The company has received an order for a minimumof 400 gallons of the custom blend. The customer specifiedthat the order must contain at least 40 percent rye and notmore than 250 gallons of bourbon. The customer also speci-fied that the blend should be mixed in the ratio of two partsrye to one part bourbon. The distillery can produce 500 gal-lons per week, regardless of the blend. The production man-ager wants to complete the order in one week. The blend issold for $12 per gallon. The distillery company’s cost pergallon is $4 for rye and $2 for bourbon. The company wantsto determine the blend mix that will meet customer require-ments and maximize profits.a. Formulate and solve a linear programming model forthis problem.b. Solve the problem using the graphical method.arrow_forwardOperations Research. A company produces two models of calculators at different places. In one day place A can produce 140 of model 1 and 35 of model 2, while place B can produce 60 of model 1 and 90 of model 2. If the company needs to produce at least 420 of model 1 and 315 of model 2. Assume it costs 1200$ per day to operate Place A and 900$ per day for place B. consider x and y to be the numbers of operating days for places A and B respectively. Formulate a linear programming problem and solve it in order to minimize the total cost.arrow_forwardDouble Tree Hilton is a hotel that provides two types of rooms with three rental classes: Super Saver, Deluxe, and Business. The profit per night for each type of room and rental class is as follows: Rental Class Super Saver Deluxe Business Room Type I $30 $35 $50 Type II $20 $30 $40 Double Tree's management makes a forecast of the demand by rental class for each night in the future. A linear programming model developed to maximize profit is used to determine how many reservations to accept for each rental class.The demand forecast for a particular night is 130 rentals in the Super Saver class, 60 in the Deluxe class, and 50 in the Business class. (These demands are the maximum numbers to accept for each rental class. The actual reservation may be less.) Double Tree has 100 Type I rooms and 130 Type II rooms. 1. Question text How many decision variables are there in your model? What is the maximum profits($)? What is the number of…arrow_forward
arrow_back_ios
arrow_forward_ios
Recommended textbooks for you
Practical Management ScienceOperations ManagementISBN:9781337406659Author:WINSTON, Wayne L.Publisher:Cengage,
Practical Management Science
Operations Management
ISBN:9781337406659
Author:WINSTON, Wayne L.
Publisher:Cengage,