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 3QP
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 solution
Students have asked these similar questions
Tokyo Company needs the resources indicate below to produce its products
Product
Labor (hr./unit)
Material (Ib./unit)
Profit (S/unit)
$3
2.
2.
3.
4.
Tokyo Company has a daily labor capacity of 240 available hours and a daily supply of 400 pounds
of material. The general linear programming formulation for this problem is as follows
maximize 2 3 + Sxy + 2
subject to
Sx + 2 + 4xy s 240
4x + Gry + 3y s 400
Management has developed the following set of goals, arranged in order of their importance to
the firm
(1) Because of recent labor relations difficulties, management wants to avoid underutilization of
normal production capacity
(2) Management has established a satisfactory profit level of S500 per day
(3) Overtime is to be minimized as much as possible
(4) Management wants to minimize the purchase of additional materials to avoid handling and
storage problems.
I need a detailed assistance to solve the following problem in: Operations Analysis.
1) A trucking company maintains an inventory of trucks that varies monthly. The ending inventory of trucks during the first 8 months of the year (January to August) were 26, 38, 31, 22, 13, 9, 16, 5, respectively. The monthly inventory holding cost is proportional to the monthly ending inventory. Trucks incur the following costs
Each truck costs the company $8,000.
Interest rate on the cost of capital is 20% per annum (annually).
Each truck incurs a storage cost of 3% per annum of the truck cost.
Each truck incurs a liability insurance cost of 2% per annum of the truck cost. The carrying cost is the total of all costs (capital, storage and insurance.)
What is the monthly carrying cost per truck?
What is the total carrying cost incurred by the company over the 8 months (January to August)?
What is the estimated average annual carrying cost?
A dietitian has been asked by the athletic director to develop a snack that athletes can use in their training programs. The dietitian intends to mix two seperate products to make the snack. The following information (on the table below):
Product A costs Php20 per ounce, while Product B costs Php10 per ounce. Determine the optimal quantities of each product that will give the minimized cost.
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
- Assume the demand for a companys drug Wozac during the current year is 50,000, and assume demand will grow at 5% a year. If the company builds a plant that can produce x units of Wozac per year, it will cost 16x. Each unit of Wozac is sold for 3. Each unit of Wozac produced incurs a variable production cost of 0.20. It costs 0.40 per year to operate a unit of capacity. Determine how large a Wozac plant the company should build to maximize its expected profit over the next 10 years.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_forwardLemingtons 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_forward
- Two poultry farms supply companies with chicken feeds. The unit costs of shipping from the farms to the companies are given on the table below. The farm's goal is to minimize the cost of meeting customer's demands. For questions a and b; (a) Generate a mathematical model for finding the least cost way of shipping chicken feeds from the farms to the companies. (b) if the demand of company number 2 increased by 3 units. By how much would the costs increase? Show solution. (c). Solve the total cost using the solver add-in in excel.arrow_forwardB. For each of the following, you are to set up a linear program and solve the problem using the Excel Solver. 5. Freshly Baked Hub has 3 warehouses and 3 branches store. The following table shows the shipping costs between each warehouses and store, the warehouse capabilities and store capacities. Branch Store To Capabilities From Y A P 350 P 400 P 280 7,000 B P 197 P 510 P 370 8,000 P 470 P 630 P 285 12,000 Сарacity 6,000 14,000 7,000 27,000 Linear Program: Solver Solution: х1 x2 х3 х4 х5 х6 х7 х8 х9 Decision: Constraints: Total Sign Capability 1 2 3 4 5 Minimize C: Warehousearrow_forwardQ: The Morton Supply Company produces clothing, footwear, and accessories for dancing and gymnastics. They produce three models of pointe shoes used by ballerinas to balance on the tips of their toes. The shoes are produced from four materials: cardstock, satin, plain fabric, and leather. The number of square inches of each type of material used in each model of shoe, the amount of material available, and the profit/model are shown below: Material (measured in square inches) Model 1 Model 2 Model 3 Material Available Cardstock 12 10 14 1200 Satin 24 20 15 2000 Plain fabric 40 40 30 7500 Leather 11 11 10 1000 Profit per model $50 $44 $40 a) Generate the Sensitivity Report in EXCEL for the model that you built. Interpret, in context, the Shadow Price for the Leather and the Satin constraints. Include units in your interpretations.arrow_forward
- Q: The Morton Supply Company produces clothing, footwear, and accessories for dancing and gymnastics. They produce three models of pointe shoes used by ballerinas to balance on the tips of their toes. The shoes are produced from four materials: cardstock, satin, plain fabric, and leather. The number of square inches of each type of material used in each model of shoe, the amount of material available, and the profit/model are shown below: Material (measured in square inches) Model 1 Model 2 Model 3 Material Available Cardstock 12 10 14 1200 Satin 24 20 15 2000 Plain fabric 40 40 30 7500 Leather 11 11 10 1000 Profit per model $50 $44 $40 a) Clearly state the decision variables, objective function and constraints. The objective function and constraints should be written as inequality or equality formulas. b) Generate the Answer Report using SOLVER in EXCEL. Then, clearly answer what is the optimal solution? What is the resulting profit?arrow_forwardQ: The Morton Supply Company produces clothing, footwear, and accessories for dancing and gymnastics. They produce three models of pointe shoes used by ballerinas to balance on the tips of their toes. The shoes are produced from four materials: cardstock, satin, plain fabric, and leather. The number of square inches of each type of material used in each model of shoe, the amount of material available, and the profit/model are shown below: Material (measured in square inches) Model 1 Model 2 Model 3 Material Available Cardstock 12 10 14 1200 Satin 24 20 15 2000 Plain fabric 40 40 30 7500 Leather 11 11 10 1000 Profit per model $50 $44 $40 a) Suppose the feasible region from a set of constraints in a linear programing problem is given in the shaded region of the attached graph. If the objective function is to minimize Cost = 9x + 4y, what would be the optimal solution? What is the resulting minimized cost? SOLVER is not used in this question. Explain your reasoning.arrow_forwardQ: The Morton Supply Company produces clothing, footwear, and accessories for dancing and gymnastics. They produce three models of pointe shoes used by ballerinas to balance on the tips of their toes. The shoes are produced from four materials: cardstock, satin, plain fabric, and leather. The number of square inches of each type of material used in each model of shoe, the amount of material available, and the profit/model are shown below: Material (measured in square inches) Model 1 Model 2 Model 3 Material Available Cardstock 12 10 14 1200 Satin 24 20 15 2000 Plain fabric 40 40 30 7500 Leather 11 11 10 1000 Profit per model $50 $44 $40 a) Generate the Answer Report using SOLVER in EXCEL. Then, clearly answer what is the optimal solution? What is the resulting profit?arrow_forward
- 1) Provide a schematic diagram for this alloy blending problem. (Indicate the problem data and the controllable variables/decision variables on the diagram). 2) Formulate a Linear Programming (LP) model that can be applied to determine the optimal input mix the foundry should smelt.arrow_forwardA product is produced at three plants and shipped to three warehouses. Plant capacity, warehouse demands and transportation costs per unit are shown in the table. Plant Warehouse A B C Plant Capacity 1 20 16 26 500 2 10 14 8 500 3 12 18 6 400 Warehouse Demands 200 600 600 a) Find an initial feasible solution and calculate the cost for this solution. b) If improvement can be made, make one step improvement to the solution in part a, and calculate the cost for the improved solution.arrow_forwardA retail has 8 stores supplied from 4 suppliers, where each supplier supplies different goods. Delivery from the Supplier is in trucks with a capacity of 40,000 units at a cost of $1100 per load plus $100 per delivery to the store. Inventory costs are $0.2 per unit per year. The Supply Chain Manager is considering whether to use direct shipping or Milk Run delivery for 4 stores for each truck in one shipment a) if annual sales in each store are 1000,000 units, which shipping method provides the lower cost b) if the annual sales in each store are 200,000 units, which shipping method provides the lower costarrow_forward
arrow_back_ios
SEE MORE QUESTIONS
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,