BUS309: Excel

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 A19 X V fx D E G H M N O Q R Chapter 4 - Forecasting (10 Pts) The quarterly sales for specific educational software over the past three years are given in the following table. a) Compute the four seasonal factors per quarter. b) What is the demand forecast per quarter in year 4 if total forecast for the year (4) is 8,000? Avg. Avg. YEAR 1 YEAR 2 YEAR 3 Period Sea Fact Demand 10 Demand 11 Quarter 1 1,710 1,820 1,830 1,787 2,000 0.89 12 Quarter 2 960 910 1,090 187 2,000 0.49 13 Quarter 3 2,720 2,840 2,900 2,820 2,000 1.41 14 Quarter 4 2,430 2,200 2,590 2,407 2,000 1.20 15 2,000 16 17 18 19 20 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 B16 X V fx C D E F G H K M N O P Q R Chapter 8 - Location Strategies (5 Pts) A manufacturing firm is considering three potential locations for a new parts manufacturing facility. A consulting firm has assessed three sites based on the four factors supplied by management as critical to the location's success. Given the management-supplied factor weights and the consultant team scores, which location should be selected? Scores are based on 50 = best. 8 Location 9 Factor Weight A B C 10 Labor Climate 10 35 15 20 11 Taxes 30 30 40 40 12 Utilities 20 25 20 45 13 Wages 40 10 25 25 14 15 16 17 18 19 20 25 26 27 28 Instructions Q1-Ch3 Q2-Ch4 Q3-Ch8 Q4-Ch14 Q5-Ch16 Q6-Ch17 Q7-ModA Q8-ModB Q9-ModD Q10-ModF + Ready Accessibility: Investigate + 110%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 G12 X V fx C D E F G H K M N O P Q R T Chapter 14 - Material Requirements Planning (10 Pts) There is one order outstanding, to make 80 frame kits. There are 200 legs in inventory. There are no other large items in inventory, and no scheduled receipts. Fasteners are available from the small parts area. 5 Calculate the gross and net requirements to fulfill the outstanding order (fill in the blanks). A-frame 10 Ridge pole Side braces 12 (2) Legs (4) (1) 13 14 Fastener set Fastener set 15 (2) (1) 16 17 Gross On-hand Net Par 18 Requirements Inventory Requirements 19 A-frame 80 Ridge 20 pole Side 21 braces 22 Legs 200 Fastener 440 23 set 24 25 26 27 28 Instructions Q1-Ch3 Q2-Ch4 Q3-Ch8 Q4-Ch14 Q5-Ch16 Q6-Ch17 Q7-ModA 28-ModB Q9-ModD Q10-ModF + Ready Accessibility: Investigate + 100%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 [ Calibri (Body) 11 B I U v A A / v [II Conditional Formatting v Egg v p v 3" I v v 4 Format as Table v v ' (I; v A V Alignment Number Editing Analyze Automate Reference Translator Solver W Cell Styles v Data Content _l_ Discount Mart, a major East Coast retailer, wants to the determine the economic order quantity (see Chapter 12 for E00 formulas) for its halogen lamps. It currently buys all halogen lamps from Specialty Lighting Manufacturers in Atlanta. Annual demand is 2,000 lamps, ordering cost per order is $30, and annual carrying cost per lamp is $12. a) What is the E00? b) What are the total annual costs of holding and ordering (managing) this inventory? c) How many orders should Discoun Mart place with Specialty Lighting per year? Q5-Ch16 Instructions Q1 -Ch3 QZ-Ch4 7 7 78' Q4-Ch14 33 7;? Accessibility: investigate QS-Ch17 Q7ModA QQ-ModD Q10-ModF + @ E -( )+1oo% Ready 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 [ Calibri (Body) 11 B I U v A A / v [II Conditional Formatting v Egg v p v 3" I v v 4 Format as Table v v ' (I; v A V Alignment Number Editing Analyze Automate Reference Translator Solver W Cell Styles v Data Content _[_ Number of breakdowns _ Monthl fre-uen _ Each time the system is down, the firm loses an average of $400 in time and service expenses. They are considering signing a contract for preventive maintenance. With preventive maintenance, the system would be down on average only 0.5 per month. The monthly cost of preventive maintenance would be $200 a month. Which is cheaper, breakdown or preventive maintenance? Why? Show your computations. Instructions Q1-Ch3 QZ-Ch4 7 ,B' 05-Ch16 33 7;? Accessibility: Investigate QG-Ch17 Q7ModA QQ-ModD Q10-ModF + @ E -( )+1oo% Ready 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%