Spreadsheet Modeling and Decision Analysis: A Practical Introduction to Business Analytics
7th Edition
ISBN: 9781285418681
Author: Cliff Ragsdale
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 3, Problem 40QP
a)
Summary Introduction
To formulate: A linear programming model for the problem.
b)
Summary Introduction
To develop: A spreadsheet model and solve using solver.
c)
Summary Introduction
To identify: The optimal solution.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Davis Instruments has two manufacturing plants located in Atlanta, Georgia. Product demand varies considerably from month to month, causing Davis extreme difficulty in workforce scheduling. Recently Davis started hiring temporary workers supplied by WorkForce Unlimited, a company that specializes in providing temporary employees for firms in the greater Atlanta area. WorkForce Unlimited offered to provide temporary employees underthree contract options that differ in terms of the length of employment and the cost. The three options are summarized:
The longer contract periods are more expensive because WorkForce Unlimited experiences greater difficulty finding temporary workers who are willing to commit to longer work assignments. Over the next six months, Davis projects the following needs for additional employees:
Each month, Davis can hire as many temporary employees as needed under each of the three options. For instance, if Davis hires five employees in January under Option 2,…
Acadia Logistics anticipates that it will need more distribution center space to accommodate what it believes will be a significant increase in demand for its final-mile
services. Acadia could either lease public warehouse space to cover all levels of demand or construct its own distribution center to meet a specified level of demand,
and then use public warehousing to cover the rest. The yearly cost of building and operating its own facility, including the amortized cost of construction, is $15.00
per square foot. The yearly cost of leasing public warehouse space is $24.50 per square foot.
E Click the icon to view the expected demand requirements.
a. The expected value of leasing public warehouse space as required by demand is S
(Enter your response as a whole number.)
More Info
Requirements (in sq. ft)
Probability
230,000
430,000
630,000
830,000
0.35
0.4
0.2
0.05
Print
Done
Mark Ewing has decided to enter contract with uber service provider in his area. The driver offers a car variety of mileage or distance to be travelled to him. All contracts were to be signed for three years. The first option has a monthly rent of P3,000, with a total mileage allowance of 36,000 kilometers (an average of 12,000 kilometers per year) and a cost of P35 per kilometer for any kilometers over 36,000. The following table summarizes each of the Uber Service Contract offered to him:
3-Year Contract
Monthly Cost
Mileage Allowance
Cost Per Excess Kilometer
Option A
P3,000
30,000
P 35
Option B
P3,500
45,000
P 25
Option C
P4,000
54,000
P 15
Mark has estimated that, during the 3 years of the agreement, there is a 40% chance he will drive an average of 12,000 kilometers per year, a 30% chance he will drive an average of 15,000 miles per year, and a 30% chance that he will drive 18,000 miles per year. In evaluating the options, Mark would…
Chapter 3 Solutions
Spreadsheet Modeling and Decision Analysis: A Practical Introduction to Business Analytics
Ch. 3 - Prob. 1QPCh. 3 - Prob. 2QPCh. 3 - Prob. 3QPCh. 3 - Prob. 4QPCh. 3 - Prob. 5QPCh. 3 - Prob. 6QPCh. 3 - Prob. 7QPCh. 3 - Prob. 8QPCh. 3 - Prob. 9QPCh. 3 - Prob. 10QP
Ch. 3 - Prob. 11QPCh. 3 - Prob. 12QPCh. 3 - Prob. 13QPCh. 3 - Prob. 14QPCh. 3 - Prob. 15QPCh. 3 - Prob. 16QPCh. 3 - Tuckered Outfitters plans to market a custom brand...Ch. 3 - Prob. 18QPCh. 3 - Prob. 19QPCh. 3 - Prob. 20QPCh. 3 - Prob. 21QPCh. 3 - Prob. 22QPCh. 3 - Prob. 23QPCh. 3 - Prob. 24QPCh. 3 - Prob. 25QPCh. 3 - A manufacturer of prefabricated homes has decided...Ch. 3 - Prob. 27QPCh. 3 - Prob. 28QPCh. 3 - Prob. 29QPCh. 3 - Prob. 30QPCh. 3 - Prob. 31QPCh. 3 - Prob. 32QPCh. 3 - Prob. 33QPCh. 3 - Prob. 34QPCh. 3 - Prob. 35QPCh. 3 - Prob. 36QPCh. 3 - Prob. 37QPCh. 3 - Prob. 38QPCh. 3 - Prob. 39QPCh. 3 - Prob. 40QPCh. 3 - Prob. 41QPCh. 3 - Prob. 42QPCh. 3 - Prob. 43QPCh. 3 - A natural gas trading company wants to develop an...Ch. 3 - Prob. 45QPCh. 3 - Prob. 46QPCh. 3 - The CFO for Eagle Beach Wear and Gift Shop is in...Ch. 3 - Prob. 48QPCh. 3 - Prob. 1.1CCh. 3 - Prob. 1.2CCh. 3 - Prob. 1.3CCh. 3 - Prob. 1.4CCh. 3 - Prob. 2.1CCh. 3 - Prob. 2.2CCh. 3 - Prob. 2.3CCh. 3 - Prob. 2.4CCh. 3 - Prob. 2.5CCh. 3 - Kelly Jones is a financial analyst for Wolverine...
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
- Scenario 3 Ben Gibson, the purchasing manager at Coastal Products, was reviewing purchasing expenditures for packaging materials with Jeff Joyner. Ben was particularly disturbed about the amount spent on corrugated boxes purchased from Southeastern Corrugated. Ben said, I dont like the salesman from that company. He comes around here acting like he owns the place. He loves to tell us about his fancy car, house, and vacations. It seems to me he must be making too much money off of us! Jeff responded that he heard Southeastern Corrugated was going to ask for a price increase to cover the rising costs of raw material paper stock. Jeff further stated that Southeastern would probably ask for more than what was justified simply from rising paper stock costs. After the meeting, Ben decided he had heard enough. After all, he prided himself on being a results-oriented manager. There was no way he was going to allow that salesman to keep taking advantage of Coastal Products. Ben called Jeff and told him it was time to rebid the corrugated contract before Southeastern came in with a price increase request. Who did Jeff know that might be interested in the business? Jeff replied he had several companies in mind to include in the bidding process. These companies would surely come in at a lower price, partly because they used lower-grade boxes that would probably work well enough in Coastal Products process. Jeff also explained that these suppliers were not serious contenders for the business. Their purpose was to create competition with the bids. Ben told Jeff to make sure that Southeastern was well aware that these new suppliers were bidding on the contract. He also said to make sure the suppliers knew that price was going to be the determining factor in this quote, because he considered corrugated boxes to be a standard industry item. As the Marketing Manager for Southeastern Corrugated, what would you do upon receiving the request for quotation from Coastal Products?arrow_forwardThe Tinkan Company produces one-pound cans for the Canadian salmon industry. Each year the salmon spawn during a 24-hour period and must be canned immediately. Tinkan has the following agreement with the salmon industry. The company can deliver as many cans as it chooses. Then the salmon are caught. For each can by which Tinkan falls short of the salmon industrys needs, the company pays the industry a 2 penalty. Cans cost Tinkan 1 to produce and are sold by Tinkan for 2 per can. If any cans are left over, they are returned to Tinkan and the company reimburses the industry 2 for each extra can. These extra cans are put in storage for next year. Each year a can is held in storage, a carrying cost equal to 20% of the cans production cost is incurred. It is well known that the number of salmon harvested during a year is strongly related to the number of salmon harvested the previous year. In fact, using past data, Tinkan estimates that the harvest size in year t, Ht (measured in the number of cans required), is related to the harvest size in the previous year, Ht1, by the equation Ht = Ht1et where et is normally distributed with mean 1.02 and standard deviation 0.10. Tinkan plans to use the following production strategy. For some value of x, it produces enough cans at the beginning of year t to bring its inventory up to x+Ht, where Ht is the predicted harvest size in year t. Then it delivers these cans to the salmon industry. For example, if it uses x = 100,000, the predicted harvest size is 500,000 cans, and 80,000 cans are already in inventory, then Tinkan produces and delivers 520,000 cans. Given that the harvest size for the previous year was 550,000 cans, use simulation to help Tinkan develop a production strategy that maximizes its expected profit over the next 20 years. Assume that the company begins year 1 with an initial inventory of 300,000 cans.arrow_forwardLocated in the picturesque Berkshire Mountains of Western Massachusetts, Ski Butternut has been a family-owned, family-oriented ski destination for more than 50 years. The resort includes 22 trails for downhill skiing and snowboarding, two terrain parks for riding, and a dedicated area for snow tubing. Although Ski Butternut hosts some non-ski events during summer and fall, its business goes into high gear when snowy weather arrives, bringing skiers and riders from across Massachusetts, Connecticut, New York, and New Jersey. Matt Sawyer, Ski Butternuts director of marketing, says the primary target market has always been families with young children who are seeking affordable skiing. Everything from the snack-bar menus to the ski-shop merchandise is presented with families in mind. So that parents can have fun in the snow without worry, the resort has a Childrens Center for children who are too young to ski or have no interest. Fifth-graders are invited to ski for free when accompanied by an adult who buys an adult lift ticket. The resort also created two terrain parks for young snowboarders who were clamoring for a more exciting riding experience. Without the terrain parks, Sawyer says, these boarders would have asked their parents to take them to competing mountains in Vermont. Ski Butternuts research shows that first-timers are a particularly important segment, because they tend to have a strong allegiance to the resort where they learn to ski. First-timers typically visit the resort seven times before seeking out more challenging mountains. As a result, Ski Butternut has made teaching first-timers to ski or snowboard one of its specialties. For this market, the resort bundles ski or board rentals, lift tickets, and also offers a wide range of individual and group lessons for all ages and abilities at a value price. Because Ski Butternut has trails for different skill levels, beginners can challenge themselves by changing trails within the resort once they feel confident. Ski Butternut also targets seniors and college students. Knowing that weekends are the busiest period, the resort offers special midweek prices to attract seniors who have free time to ski on weekdays. College students are particularly value-conscious, and they often travel to ski resorts as a group. As a result, Ski Butternut offers weekend and holiday discounts to bring in large numbers of students who would otherwise ski elsewhere. Thanks to Facebook, Twitter, and other social media, students quickly spread the word about special pricing, which enhances Ski Butternuts ability to reach this key segment. In addition, the resort highlights discount pricing for families when targeting specific segments, such as scout troops, military personnel, emergency services personnel, and members of local ski clubs. Another segment Ski Butternut has selected for marketing attention is ski racers. The resort features professional coaching, lessons, and programs for ski racers in the age group of 8 to 20. Sawyer notes that these ski racers are extremely dedicated to training, which means theyre on the slopes as often as possible, a positive for the resorts attendance and revenue. To stay in touch with racers, Ski Butternut has a special website and a dedicated Facebook page. Sawyer conducts up to 1,200 customer surveys every year to better understand who his customers are and what they need. He also compares the results with skiers who visit mountains of a similar size in other areas. Digging deeper, he analyzes data drawn from the ski shops rental business to build a detailed picture of customers demographics, abilities, and preferences. Based on this research, he knows that the typical family at Ski Butternut consists of two children under age 18 who ski or ride, and at least one parent who skis. Because they can obtain so much information from and about their customers, Sawyer and his team are able to make better decisions about the marketing mix for each segment. By better matching the media with the audience, they get a better response from advertising, e-mail messages, and other marketing communications. As one example, they found that 15 percent of the visitors to Ski Butternuts website were using a smartphone to access the site. Sawyer has now created a special version of the site specifically for mobile use and created a text-message contest to engage skiers who have smartphones.16 Of the four categories of variables, which one seems to be the most central to Ski Butternuts segmentation strategy, and why?arrow_forward
- Located in the picturesque Berkshire Mountains of Western Massachusetts, Ski Butternut has been a family-owned, family-oriented ski destination for more than 50 years. The resort includes 22 trails for downhill skiing and snowboarding, two terrain parks for riding, and a dedicated area for snow tubing. Although Ski Butternut hosts some non-ski events during summer and fall, its business goes into high gear when snowy weather arrives, bringing skiers and riders from across Massachusetts, Connecticut, New York, and New Jersey. Matt Sawyer, Ski Butternuts director of marketing, says the primary target market has always been families with young children who are seeking affordable skiing. Everything from the snack-bar menus to the ski-shop merchandise is presented with families in mind. So that parents can have fun in the snow without worry, the resort has a Childrens Center for children who are too young to ski or have no interest. Fifth-graders are invited to ski for free when accompanied by an adult who buys an adult lift ticket. The resort also created two terrain parks for young snowboarders who were clamoring for a more exciting riding experience. Without the terrain parks, Sawyer says, these boarders would have asked their parents to take them to competing mountains in Vermont. Ski Butternuts research shows that first-timers are a particularly important segment, because they tend to have a strong allegiance to the resort where they learn to ski. First-timers typically visit the resort seven times before seeking out more challenging mountains. As a result, Ski Butternut has made teaching first-timers to ski or snowboard one of its specialties. For this market, the resort bundles ski or board rentals, lift tickets, and also offers a wide range of individual and group lessons for all ages and abilities at a value price. Because Ski Butternut has trails for different skill levels, beginners can challenge themselves by changing trails within the resort once they feel confident. Ski Butternut also targets seniors and college students. Knowing that weekends are the busiest period, the resort offers special midweek prices to attract seniors who have free time to ski on weekdays. College students are particularly value-conscious, and they often travel to ski resorts as a group. As a result, Ski Butternut offers weekend and holiday discounts to bring in large numbers of students who would otherwise ski elsewhere. Thanks to Facebook, Twitter, and other social media, students quickly spread the word about special pricing, which enhances Ski Butternuts ability to reach this key segment. In addition, the resort highlights discount pricing for families when targeting specific segments, such as scout troops, military personnel, emergency services personnel, and members of local ski clubs. Another segment Ski Butternut has selected for marketing attention is ski racers. The resort features professional coaching, lessons, and programs for ski racers in the age group of 8 to 20. Sawyer notes that these ski racers are extremely dedicated to training, which means theyre on the slopes as often as possible, a positive for the resorts attendance and revenue. To stay in touch with racers, Ski Butternut has a special website and a dedicated Facebook page. Sawyer conducts up to 1,200 customer surveys every year to better understand who his customers are and what they need. He also compares the results with skiers who visit mountains of a similar size in other areas. Digging deeper, he analyzes data drawn from the ski shops rental business to build a detailed picture of customers demographics, abilities, and preferences. Based on this research, he knows that the typical family at Ski Butternut consists of two children under age 18 who ski or ride, and at least one parent who skis. Because they can obtain so much information from and about their customers, Sawyer and his team are able to make better decisions about the marketing mix for each segment. By better matching the media with the audience, they get a better response from advertising, e-mail messages, and other marketing communications. As one example, they found that 15 percent of the visitors to Ski Butternuts website were using a smartphone to access the site. Sawyer has now created a special version of the site specifically for mobile use and created a text-message contest to engage skiers who have smartphones.16 How is Ski Butternut applying behavioristic variables in its segmentation strategy? Explain your answer.arrow_forwardLocated in the picturesque Berkshire Mountains of Western Massachusetts, Ski Butternut has been a family-owned, family-oriented ski destination for more than 50 years. The resort includes 22 trails for downhill skiing and snowboarding, two terrain parks for riding, and a dedicated area for snow tubing. Although Ski Butternut hosts some non-ski events during summer and fall, its business goes into high gear when snowy weather arrives, bringing skiers and riders from across Massachusetts, Connecticut, New York, and New Jersey. Matt Sawyer, Ski Butternuts director of marketing, says the primary target market has always been families with young children who are seeking affordable skiing. Everything from the snack-bar menus to the ski-shop merchandise is presented with families in mind. So that parents can have fun in the snow without worry, the resort has a Childrens Center for children who are too young to ski or have no interest. Fifth-graders are invited to ski for free when accompanied by an adult who buys an adult lift ticket. The resort also created two terrain parks for young snowboarders who were clamoring for a more exciting riding experience. Without the terrain parks, Sawyer says, these boarders would have asked their parents to take them to competing mountains in Vermont. Ski Butternuts research shows that first-timers are a particularly important segment, because they tend to have a strong allegiance to the resort where they learn to ski. First-timers typically visit the resort seven times before seeking out more challenging mountains. As a result, Ski Butternut has made teaching first-timers to ski or snowboard one of its specialties. For this market, the resort bundles ski or board rentals, lift tickets, and also offers a wide range of individual and group lessons for all ages and abilities at a value price. Because Ski Butternut has trails for different skill levels, beginners can challenge themselves by changing trails within the resort once they feel confident. Ski Butternut also targets seniors and college students. Knowing that weekends are the busiest period, the resort offers special midweek prices to attract seniors who have free time to ski on weekdays. College students are particularly value-conscious, and they often travel to ski resorts as a group. As a result, Ski Butternut offers weekend and holiday discounts to bring in large numbers of students who would otherwise ski elsewhere. Thanks to Facebook, Twitter, and other social media, students quickly spread the word about special pricing, which enhances Ski Butternuts ability to reach this key segment. In addition, the resort highlights discount pricing for families when targeting specific segments, such as scout troops, military personnel, emergency services personnel, and members of local ski clubs. Another segment Ski Butternut has selected for marketing attention is ski racers. The resort features professional coaching, lessons, and programs for ski racers in the age group of 8 to 20. Sawyer notes that these ski racers are extremely dedicated to training, which means theyre on the slopes as often as possible, a positive for the resorts attendance and revenue. To stay in touch with racers, Ski Butternut has a special website and a dedicated Facebook page. Sawyer conducts up to 1,200 customer surveys every year to better understand who his customers are and what they need. He also compares the results with skiers who visit mountains of a similar size in other areas. Digging deeper, he analyzes data drawn from the ski shops rental business to build a detailed picture of customers demographics, abilities, and preferences. Based on this research, he knows that the typical family at Ski Butternut consists of two children under age 18 who ski or ride, and at least one parent who skis. Because they can obtain so much information from and about their customers, Sawyer and his team are able to make better decisions about the marketing mix for each segment. By better matching the media with the audience, they get a better response from advertising, e-mail messages, and other marketing communications. As one example, they found that 15 percent of the visitors to Ski Butternuts website were using a smartphone to access the site. Sawyer has now created a special version of the site specifically for mobile use and created a text-message contest to engage skiers who have smartphones.16 What role do geographic variables play in Ski Butternuts segmentation and targeting?arrow_forwardComfort Plus Inc. (CPI) manufactures a standard dining chair used in restaurants. The demand forecasts for quarter 1 (January–March) and quarter 2 (April–June) are 3700 chairs and 4200 chairs, respectively. CPI has a policy of satisfying all demand in the quarter in which it occurs. The chair contains an upholstered seat that can be produced by CPI or purchased from DAP, a subcontractor. DAP currently charges $12.50 per seat, but has announced a new price of $13.75, effective April 1. CPI can produce the seat at a cost of $10.25. CPI can produce up to 3800 seats per quarter. Seats that are produced or purchased in quarter 1 and used to satisfy demand in quarter 2 cost CPI $1.50 each to hold in inventory, but maximum inventory cannot exceed 300 seats. Define the decision variables needed to model this problem. Give the objection function. Write the constraints. What is the final answer? Explain in detail.arrow_forward
- please answer in 30 mins. do no attempt if not sure.arrow_forwardDemand for stereo headphones and music players for joggers has caused Nina Industries to grow almost 50 percent over the past year. The number of joggers continues to expand, so Nina expects demand for headsets to also expand, because, as yet, no safety laws have been passed to prevent joggers from wearing them. Demand for the players for this year was as follows: MONTH DEMAND (UNITS) January 4,150 February 4,250 March 3,950 April 4,350 May 4,950 June 4,650 July 5,250 August 4,850 September 5,350 October 5,650 November 6,250 December 5,950 a. Using linear regression analysis, what would you estimate demand to be for each month next year? Using a spreadsheet, follow the general format in Exhibit 3.8. (Do not round intermediate calculations. Round your answers to 2 decimal places.) b. To be reasonably confident of meeting demand, Nina decides to use 3 standard errors of estimate for safety. How many additional units should be held to meet this…arrow_forward) O'Donnell & Joyce purchases components from three suppliers. Components purchased from Supplier A are priced at €7 each and used at the rate of 18,000 units per month. Components purchased from Supplier B are priced at €5 each and are used at the rate of 4,500 units per month. Components purchased from Supplier C are priced at €9 each and used at the rate of 1000 units per month. Currently, O'Donnell & Joyce purchases a separate truckload from each supplier. As part of its JIT drive, O'Donnell & Joyce has decided to aggregate purchases from the three suppliers. The trucking company charges a fixed cost of €550 for the truck with an additional charge of €120 for each stop. Thus, if O'Donnell & Joyce asks for a pickup from only one supplier, it charges €670; from two suppliers, it charges €790; and from three suppliers, it charges €910. What replenishment strategy would you suggest for O'Donnell & Joyce to minimize annual costs? Assume an annual holding cost of 25…arrow_forward
- A circus is scheduled to appear in a city on a given date. The profits obtained are heavily dependent on the weather which can be classified as "good" or" bad". The circus owners may choose to setup operations in a large open field that is centrally located or rent a small building to stage a small version of the circus. The small building is not expected to be adversely affected by bad weather – thus will not affect the circus for it is well secure and has covered parking for the guests. The following shows the profits of the options and states of nature: States of nature Decision alternatives Set up in field Rent small building | Probability Good Bad $14,500 $5,000 -$15,000 $4,000 P(G)=0.5 P(B)=0.5 The circus owners may choose to delay the decision until the day before the event is due. At this time they can obtain the one-day weather report (free) which is usually reliable. This delay will however increase their set up cost by $1000.00 or if they choose to rent, the rental cost will…arrow_forwardDescribe situations in which the single-period model is appropriate and solve typical problems.arrow_forwarda) For an upcoming red carpet evening, a company is selling tickets at $60 per person at a large theatre which has a capacity of 10,000 people. Each attendant is expected to buy $12 of food and merchandise at the film evening. The cost of providing the food and merchandise is estimated to be $5 per person. All other ancillary services will be provided by the theatre. Initial analysis indicates that the ancillary cost of providing food and merchandise, as well as the staff needed to handle ticket sales, may be described as a semi-variable cost. Data on these costs and tickets sold from three similar events held at the venue have been collected and are tabulated below: Tickets Sold Cost ($) 2100 6640 3824 11284 4650 13525 Use the high-low method to estimate the total cost function relating to these ancillary costs. b) The company will be renting the theatre which will host the upcoming red carpet evening. The budgeted fixed cost of both renting the theatre and paying the…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Practical Management ScienceOperations ManagementISBN:9781337406659Author:WINSTON, Wayne L.Publisher:Cengage,
Purchasing and Supply Chain ManagementOperations ManagementISBN:9781285869681Author:Robert M. Monczka, Robert B. Handfield, Larry C. Giunipero, James L. PattersonPublisher:Cengage Learning
Practical Management Science
Operations Management
ISBN:9781337406659
Author:WINSTON, Wayne L.
Publisher:Cengage,
Purchasing and Supply Chain Management
Operations Management
ISBN:9781285869681
Author:Robert M. Monczka, Robert B. Handfield, Larry C. Giunipero, James L. Patterson
Publisher:Cengage Learning