Bus309: Excel

7. A toy manufacturer makes stuffed kittens and puppies that have relatively lifelike motions. There are three different mechanisms which can be installed in these "pets." These toys will sell for the same price regardless of the mechanism installed, but each mechanism has its own variable cost and setup cost. Profit, therefore, is dependent upon the choice of mechanism and upon the level of demand. The manufacturer has in hand a forecast of demand that suggests a 0.2 probability of light demand, a 0.45 probability of moderate demand, and a probability of 0.35 of heavy demand. Payoffs for each mechanism-demand combination appear in the table below. Construct the appropriate decision tree to analyze this problem. Analyze the tree to select the optimal decision for the manufacturer.

Q AutoSave .orr ' 9 v {F} 2;; !l G) a Individual Course Project SP2023 (1)v Home Insert Draw Page Layout Formulas Data Review View Automate Developer New Tab Q Tell me I Calibri (Body) 11 i 4 Format as Table v B I U v v I (I; v A V Alignment Number Editing Analyze Automate Reference Translator Solver W Cell Styles V Data Content v A A / v [II Conditional Formatting v Egg v p v alE I _I_ 1 Module A - Decision Making Tools (10 Pts) A toy manufacturer makes stuffed kittens and puppies that have relatively lifelike motions. There are three different mechanisms which can be installed in these "pets." These toys will sell for the same price regardless of the mechanism installed, but each mechanism has its own variable cost and setup cost. Profit, therefore, is dependent upon the choice of mechanism and upon the level of demand. The manufacturer has in hand a forecast of demand that suggests a 0.2 probability of light demand, a 0.45 probability of moderate demand, and a probability of 0.35 of heavy demand. Payoffs for each mechanism-demand combination appear in the table below. Construct the appropriate decision tree to analyze this problem. Analyxe the tree to select the optimal decision for the manufacturer. Lomxiasuuawn >->' >-o 12 Wind-up . , . . Demand . Pneumatlc action Electronic action actlon Light I $250,000 $90,000 ($100,000) Moderate I 400,000 440,000 400,000 Heavy I 650,000 740,000 780,000 | Instructions Q1-Ch3 oz-cm , Ms os-cms Q6-Ch17 33 7;? Accessibility: Investigate Q7-ModA QQ-ModD Q10-ModF + @ E -( )+1oo% Ready Q AutoSave or: ' 9 v {F} a Individual Course Project SP2023 (1)v Home Insert Draw Page Layout Formulas Data Review View Automate Developer New Tab Q Tell me ' Cl Comments i i 3 Share I i::m A H Eiv 9v * Ii 5'35 B I U v F:E Format as Table v i v i (I; v A V Alignment Number Editing Automate Reference Translator Solver W Cell Styles v Content Rienzi Farms grows sugar cane and soybeans on its 500 acres of land. An acre of soybeans brings a $1000 contribution to overhead and prot; an acre of sugar cane has a contribution of $2000. Because of a government program no more than 200 acres may be planted in soybeans. During the planting season 1200 hours of planting time will be available. Each acre of soybeans requires 2 hours, while each acre of sugar cane requires 5 hours. The company seeks maximum contribution (profit) from its planting decision. a. State the objective functions and constraints b. What combination of sugar cane and soybeans will maximize contribution? (You may use Excel Solver or graphical method) |:l Instructions Q1 -cn3 oz-cm '03-, hS' os-cms os-cm Q7ModA 33 7;? Accessibility: Investigate QQ-ModD Q10-ModF + @ E -( )+1oo% Ready AutoSave OFF O . .. Individual Course Project SP2023 (1) ~ Home Insert Draw Page Layout Formulas Data Review View Automate Developer New Tab Tell me Comments Share Calibri (Body) 11 A A Conditional Formatting v Format as Table v Paste BIUV A Alignment Number Cells Editing Analyze Automate Reference Translator Solver Cell Styles v Data Content D19 X V fx B D E F G H M N Q R S U Module D - Waiting-Line Models (10 Pts) Customers arrive at Rich Dunn's Styling Shop at a rate of 3 per hour, distributed in a Poisson fashion. Rich can perform haircuts at a rate of 5 per hour, distributed exponentially. a) Find the average number of customers waiting for haircuts b) Find the average number of customers in the shop . c) Find the average time a customer waits until it is his or her turn. d) Find the average time a customer spends in the shop e) Find the percentage of time that Rich is busy 12 Instructions Q1-Ch3 Q2-Ch4 Q3-Ch8 Q4-Ch14 Q5-Ch16 Q6-Ch17 Q7-ModA Q8-ModB Q9-ModD Q10-ModF + Ready Accessibility: Investigate + 100%AutoSave OFF O . .. Individual Course Project SP2023 (1) ~ Home Insert Draw Page Layout Formulas Data Review View Automate Developer New Tab Tell me Comments Share Calibri (Body) 11 A A Conditional Formatting v Format as Table v Paste BIUV A Alignment Number Cells Editing Analyze Automate Reference Translator Solver Cell Styles v Data Content B21 X V fx B C D G H K M N O Q R S Module F - Simulation (10 Pts) Phantom Controls monitors and repairs control circuit boxes on elevators installed in multistory buildings in downtown Chicago. The company has the contract for 108 buildings. When a box malfunctions, Phantom installs a new one and rebuilds the failed unit in its repair facility in Gary, Indiana. The date for failed boxes over the last 2 years is shown in the following table: 6 Number of Failed Boxes per Probability Month 0 0.10 0.14 10 0.26 11 .20 12 .18 13 0.12 14 15 Simulate 2 years (24 months) of operation for Phantom and determine the average number of failed boxes per month from the 16 simulation. Was it common to have fewer than 7 failures over 3 months of operation? (Start your simulation at the top of the 10th column of Table F.4 on page 795, RN = 37, and go down in the table.) Instructions Q1-Ch3 Q2-Ch4 Q3-Ch8 Q4-Ch14 Q5-Ch16 Q6-Ch17 Q7-ModA Q8-ModB Q9-ModD Q10-ModF + Ready Accessibility: Investigate + 100%