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 3, Problem 6QP
Summary Introduction
To develop: A spreadsheet model for the problem and solve it using solver.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solutionStudents have asked these similar questions
Create spreadsheets and use Solver to determine the correct volumes to be produced to minimize cost for the following problem. Your company has two trucks that it wishes to use on a specific contract. One is a new truck the company is making payments on, and one is an old truck that is fully paid for. The new truck’s costs per mile are as follows: 54₵ (fuel/additives), 24₵ (truck payments), 36₵ (driver), 12₵ (repairs), and 1₵ (misc.). The old truck’s costs are 60₵ (fuel/additives), 0₵ (truck payments), 32₵ (rookie driver), 24₵ (repairs), and 1₵ (misc.). The company knows that truck breakdowns lose customers, so it has capped estimated repair costs at $14,000. The total distance involved is 90,000 miles (to be divided between the two trucks).
Cantel Company produces cleaning compounds for both commercial and household customers. Some of these products are produced as part of a joint manufacturing process. For example, GR37, a coarse cleaning powder meant for commercial sale, costs $1.90 a pound to make and sells for $2.00 per pound. A portion of the annual production of GR37 is retained for further processing in a separate department where it is combined with several other ingredients to form SilPol, which is sold as a silver polish, at $5.00 per unit. The additional processing requires 1/4 pound of GR37 per unit; additional processing costs amount to $3.90 per unit of SilPol produced. Variable selling costs for SilPol average $0.50 per unit. If production of SilPol were discontinued, $1,000 of costs in the processing department would be avoided. Cantel has, at this point, unlimited demand for, but limited capacity to produce, product GR37.
Â
Required:
1. Calculate the minimum number of units of SilPol that would have to be…
standard mix of Peanut/Raisin Delite to put up. The deluxe mix has .68 pounds of raisins and .32 pounds of peanuts, and the standard
mix has 0.5 pounds of raisins and 0.5 pounds of peanuts per bag. The shop has 80 pounds of raisins and 50 pounds of peanuts to
work with.
Peanuts cost $0.75 per pound and raisins cost $1.5 per pound. The deluxe mix will sell for $2.9 for a one-pound bag, and the standard
mix will sell for $2.55 for a one-pound bag. The owner estimates that no more than 110 bags of one type can be sold.
a. If the goal is to maximize profits, how many bags of each type should be prepared? (Round your answers down to a whole
number.)
Deluxe
Standard
b. What is the expected profit? (Round your answer to two decimal places.)
Expected profit
Chapter 3 Solutions
Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Business Analytics (MindTap Course List)
Ch. 3 - Prob. 1QPCh. 3 - Prob. 2QPCh. 3 - Prob. 3QPCh. 3 - Prob. 4QPCh. 3 - Prob. 5QPCh. 3 - Prob. 6QPCh. 3 - Refer to question 19 at the end of Chapter 2....Ch. 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 - Prob. 17QPCh. 3 - Tuckered Outfitters plans to market a custom brand...Ch. 3 - Prob. 19QPCh. 3 - Prob. 20QPCh. 3 - Prob. 21QPCh. 3 - Prob. 22QPCh. 3 - Prob. 23QPCh. 3 - Prob. 24QPCh. 3 - Prob. 25QPCh. 3 - Prob. 26QPCh. 3 - A manufacturer of prefabricated homes has decided...Ch. 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 - Prob. 44QPCh. 3 - A natural gas trading company wants to develop an...Ch. 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
- Lemingtons is trying to determine how many Jean Hudson dresses to order for the spring season. Demand for the dresses is assumed to follow a normal distribution with mean 400 and standard deviation 100. The contract between Jean Hudson and Lemingtons works as follows. At the beginning of the season, Lemingtons reserves x units of capacity. Lemingtons must take delivery for at least 0.8x dresses and can, if desired, take delivery on up to x dresses. Each dress sells for 160 and Hudson charges 50 per dress. If Lemingtons does not take delivery on all x dresses, it owes Hudson a 5 penalty for each unit of reserved capacity that is unused. For example, if Lemingtons orders 450 dresses and demand is for 400 dresses, Lemingtons will receive 400 dresses and owe Jean 400(50) + 50(5). How many units of capacity should Lemingtons reserve to maximize its expected profit?arrow_forwardIf a monopolist produces q units, she can charge 400 4q dollars per unit. The variable cost is 60 per unit. a. How can the monopolist maximize her profit? b. If the monopolist must pay a sales tax of 5% of the selling price per unit, will she increase or decrease production (relative to the situation with no sales tax)? c. Continuing part b, use SolverTable to see how a change in the sales tax affects the optimal solution. Let the sales tax vary from 0% to 8% in increments of 0.5%.arrow_forwardFive to six times a year, Kicker puts on tent sales in various cities throughout Oklahoma and the surrounding states. The tent sales are designed to show Kicker customers new products, engender enthusiasm about those products, and sell soon to be out-of-date products at greatly reduced prices. Each tent sale lasts one day and requires parking lot space to set up the Kicker semitrailer; a couple of show cars; a disc jockey playing music; a tent to sell Kicker merchandise, distribute brochures, and so on. Last year, the Austin, TX tent sale was held in a corner of the parking lot outside the city exhibition hall where the automotive show was in progress. Because most customers were interested more in the new model cars than in the refurbishment of their current cars, foot traffic was low. In addition, customers did not want to carry speakers and amplifiers all the way back to where they had originally parked. Total direct costs for this tent sale were $14,300. Direct costs…arrow_forward
- A company makes three types of candy and packages them in three assortments. Assortment I contains 4 cherry, 4 lemon, and 12 lime candies, and sells for a profit of $4.00. Assortment Il contains 12 cherry, 4 lemon, and 4 lime candies, and sells for a profit of $3.00. Assortment III contains 8 cherry, 8 lemon, and 8 lime candies, and sells for a profit of $5.00. They can make 5,200 cherry, 4,000 lemon, and 6,000 lime candies weekly. How many boxes of each type should the company produce each week in order to maximize its profit (assuming that all boxes produced can be sold)? What is the maximum profit? Select the correct choice below and fill in any answer boxes within your choice. OA. The maximum profit is $ when boxes of assortment 1. boxes of assortment II and assortment III are produced. OB. There is no way for the company to maximize its profit boxes ofarrow_forwardCox Electric makes electronic components and has estimated the following for a new design of one of its products: Fixed Cost = $7,000 Material cost per unit = $0.15 Labor cost per unit = $0.10 Revenue per unit = $0.65 Production Volume = 12,000 Per-unit material and labor cost together make up the variable cost per unit. Assuming that Cox Electric sells all it produces, build a spreadsheet model that calculates the profit by subtracting the fixed cost and total variable cost from total revenue, and answer the following questions.  (a) Construct a one-way data table with production volume as the column input and profit as the output. Breakeven occurs when profit goes from a negative to a positive value; that is, breakeven is when total revenue = total cost, yielding a profit of zero. Vary production volume from 5,000 to 50,000 in increments of 5,000. In which interval of production volume does breakeven occur?   to  units (b) Use Goal Seek to find the exact breakeven point.…arrow_forwardConsider the following short case as you respond to the question:Abruzzi's Italian Kitchen is a small family restaurant with an administrative staff of four people. Giuseppi Abruzzi is the chief executive officer; his wife Maria is the chief financial officer. Their son Antonio is responsible for all transactions dealing with kitchen supplies, including the raw materials for menu items; their son Carlo is responsible for all transactions dealing with dining room supplies, such as eating utensils. The restaurant's administrative practices have developed over the ten years of its existence with minimal reliance on information technology and formal procedures and a stronger emphasis on interpersonal relationships with a few reliable vendors. Once a month, Antonio places orders for kitchen supplies with one of four vendors based on price; the supplies are usually received within ten calendar days. On receipt of the supplies, Antonio pays the vendor with a company check. Carlo follows a…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,