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
Videos
Textbook Question
Chapter 3, Problem 17QP
Tuckered Outfitters plans to market a custom brand of packaged trail mix. The ingredients for the trail mix will include Raisins, Grain, Chocolate Chips, Peanuts, and Almonds costing, respectively, $2.50, $1.50, $2.00, $3.50, and $3.00 per pound. The vitamin, mineral, and protein content of each of the ingredients (in grams per pound) is summarized in the following table along with the calories per pound of ingredient:
The company would like to identify the least costly mix of these ingredients that provides at least 40 grams of vitamins, 15 grams of minerals, 10 grams of protein, and 600 calories per two pound package. Additionally, they want each ingredient to account for at least 5% and no more than 50% of the weight of the package.
- a. Formulate a LP model for this problem.
- b. Implement your model in a spreadsheet and solve it.
- c. What is the optimal mix and how much is the total ingredient cost per package?
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Two items are omitted from each of the following three lists of cost of goods sold data
from a manufacturing company income statement. Determine the amounts of the missing items, identifying them by letter.
Finished goods Inventory,June1
$61,600
$46,200
(e)
Cost of goods manufactured
329,000
(c )
484,800
Cost of finished goods available for sales
(a)
$260,400
$540,000
Finished goods Inventory ,June30
72,800
61,600
(f)
Cost of goods sold
(b)
(d)
$513,600
Hair Zone manufactures a brand of hair-styling gel. It is considering adding a modified version of the product – a foam that provides stronger hold. Hair Zone’s variable costs and prices to wholesalers are:
Current hair gel New foam productUnit selling price 2.00 2.25Unit variable costs 0.85 1.25
Hair Zone expects to sell 1 million units of the new styling foam in the first year after introduction, but it expects that 60 per cent of those sales will come from buyers who normally purchase Hair Zone’s styling gel. Hair Zone estimates that it would sell 1.5 million units of the gel if it did not introduce the foam. If the fixed cost of launching the new foam will be €100,000 during the first year, should Hair Zone add the new product to its line? Why or why not?
The Research and Development Division of the Progressive Company has been developing four possible new product lines. Management must now make a decision as to which of these four products actually will be produced and at what levels. Therefore, an operations research study has been requested to find the most profitable product mix.
A substantial cost is associated with beginning the production of any product, as given in the first row of the following table. Management’s objective is to find the product mix that maximizes the total profit (total net revenue minus start-up costs).
Let the continuous decision variables x1, x2, x3, and x4 be the production levels of products 1, 2, 3, and 4, respectively. Management has imposed the following policy constraints on these variables:
1. No more than two of the products can be produced.
2. Either product 3 or 4 can be produced only if either product 1 or 2 is produced.
3. Either 5x1 + 3x2 + 6x3 + 4x4 = 6,000
or 4x1 + 6x2 + 3x3 + 5x4 = 6,000.…
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
- Recycled Content—This LEED requirement encourages use of recycled materials including post-consumer recycled content and pre-consumer content. Supplementary cementitious materials, such as fly ash, silica fume, and slag cement, are pre-consumer recycled content. Recycled concrete as aggregate qualifies as postconsumer recycled content. Generally, reinforcing bars, manufactured from recycled steel, are considered post-consumer recycled content.arrow_forwardYou are responsible of developing the six-month aggregate production plan at Sodas Galore, a manufacturer of soft drinks. Your company makes three types of sodas: regular, diet and super-caffeinated. All three types are made using the same production process, and the switching costs can be ignored as they are so minimal. The S&OP team case created the following forecast of demand for the next six months. In addition to the sales forecast, the company has also developed planning values that are also shown in the next table. Month Sales Forecast (cases) Softdrinks Planning Values January 24,000 Current workforce 8 workers 32,000 Average monthly output per worker 32,000 Inventory holding cost 46,000 Regular wage rate February 4,000 cases per month $.30 per case per month $20.00 per hour March April May 60,000 Regular production hours/month 44,000 Overtime wage rate 160 hours June $30.00 per hour 240,000 Hiring cost $1,000 per worker $1.15 per case Total Subcontracting cost Firing/layoff…arrow_forwardUse the table below to answer the following question(s). Fiberia Accessories, a clothing retailer, is planning to introduce a new line of sweaters as part of the winter collection for $65 with an inventory of 1500. The main selling season is 60 days between November and December. The store then sells the remaining units in a clearance sale at 65 percent discount. Out of the 60 main retail days, Fiberia sells the sweaters at full retail price for only 45 days, while giving a discount of 25 percent for the remaining 15 days. The demand functions a, and b are given as 79.5 and 1.1 respectively. Marked Down Pricing Model for Fiberia Accessories's new sweater Data Retail Price Inventory Selling Season (days) Days at Full Retail Intermediate Markdown Clearance Markdown Demand Function A B $65 1500 60 45 25 percent |65 percent 79.5 1.1 Compute the everyday sales throughout the discount sales period.arrow_forward
- Rochor & Co has the following sales forecast for the next quarter: April, 20,000 units; May, 24,000 units; June, 28,000 units. Sales totalled 16,000 units in March. The March finished goods inventory was 4,000 units. End-of- month finished goods inventory levels are planned to be equal to 20 percent of the next month's planned sales. How many units would be produced in April? Group of answer choices 20,000 units 20,800 units 5,600 units 4,800 unitsarrow_forwardSarah Smith is the marketing manager for Activa. She decides to run a sales promotion to boost the brand's business and is interested in evaluating the success of the promotion. The following is key information for her review. Brand price to retailer: Brand price to consumer: Brand gross profit per case: S 3.87 Brand Marginal contribution: Sales forecast for November 2019: 24,600 cases Sales during November 2019 promotion period: 32,150 cases $ 9.50 per case $ 1.29 per 8 oz. package 23% Describe how the marketing manager will determine the "promotion lift" for the brand and indicate what the "lift" will be based on the information provided above. Show your work for this problem's answer.arrow_forwardThe Batu Pahat Kayak Company has these awesome new kayaks they are going to introduce to the market. They are a new company and need help in determining pricing, costs and how many kayaks they will need to sell in a month to break even. They are looking to you to help them determine if the selling price and costs will help them to reach their goals. They give you the following information shown in Table Q4(b) to work with; COST (RM) 500 NO. ITEMS Price per kayak Variable cost per kayak Fixed cost per month 1 2 225 7,700 (i) Determine the breakeven point. Batu Pahat Kayak has a few investors who are interested in getting a return on their investment. They have talked with your supervisor, and between them all, would like to get RM 30,000 a month in profit to divide between them. Calculate how many kayaks need to be sold in order to get the investors their return. (11 (ii1) Determine how much in sales do we need.arrow_forward
- Shoes Ltd. manufactures three items: Sneakers, Takkies and Sandals. The table below contains information regarding these products: Sneakers (R/ unit) Takkies (R/ unit) Sandals (R/ unit) Material cost 60 75 40 Labour cost 24 68 42 Variable overhead cost 7 12 16 Fixed overhead cost 36 23 11 Selling price per unit 870 990 650 Volume of production 12 000 28 500 31 200 Fixed overheads are allocated to products based on direct labour hours. Direct labour hours are constrained and therefore limited to 140 000 hours. The direct labour rate is R25 per hour. w Required: Q.3.1 Calculate the contribution margin per labour hour and then rank the products in order of profitability. Q.3.2 Calculate the number of units of each product that needs to be manufactured toarrow_forwardSuppose the government imposes a per-unit tax on the buyers of instant noodles. Does it necessarily mean that the buyers have to bear the entire tax burden? Explain with a diagram. With the above diagram, explain the impact of the per-unit tax on the consumer surplus, producer surplus and total surplus.arrow_forwardThe following information was extracted from the books of Babel Company for the production of rubber products for the year 2020: The number of units sold of product (A) amounted to 60 units The selling price of one unit of product (A) is 100,000 dinars The variable cost of producing one unit of product (A) is 35,000 dinars The fixed cost of the product line (A) is 1,000,000 dinars annually, find the following: break-even point (quantities), break-even point (values), contribution margin per unit of product (A), percentage of safety limit, operating incomearrow_forward
- Explain two approaches a seller can use to estimate variable consideration, and when each approach is likely to be more appropriate.arrow_forwardDescribe the product Package of Primer Inc. by Identifying the tangible and intangible (service) components.arrow_forwardDiscuss the plans that provide for variable incentives linked to a standard expressed as a time period per unit of production.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Purchasing and Supply Chain ManagementOperations ManagementISBN:9781285869681Author:Robert M. Monczka, Robert B. Handfield, Larry C. Giunipero, James L. PattersonPublisher:Cengage Learning
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
Inventory Management | Concepts, Examples and Solved Problems; Author: Dr. Bharatendra Rai;https://www.youtube.com/watch?v=2n9NLZTIlz8;License: Standard YouTube License, CC-BY