# Instructions: (10 points) Read the following scenario and complete the problem us1 (5 Points) Given

Instructions: (10 points) Read the following scenario and complete the problem us1 (5 Points) Given the following infromation on costs and revenues, use Goal Seekfind the product volume that would result in a profit or loss of $0 (i.e., the Break-Evpoint).Fixed Cost###riable Cost $ 20.00Price $ 30.00ven VolumeTotal Cost###al Revenue $-ofit or Loss###2. Capture a screen shot of your Goal Seek dialog before you clik OK and paste it inTo do this, once you have specified all the parameters, hold down the “Alt” key andThis copies only the top-most dialog to the Clipboard.Now right-click on cell B27 and choose “Paste” to place the picture in the spreadshPlace Clipboard Image here3. (5 Points) Answer: What is the minimum number of units that we need to sell inunitste the problem using Goal Seek.es, use Goal Seek to(i.e., the Break-EvenOK and paste it in the space below.the “Alt” key and press the “PrtScn” key on your keyboard.e in the spreadsheet.we need to sell in order to make a profit?Instructions: (20 Points) Read the following scenario and complete the Decision TrAn analyst who works for a company which produces equipment for the foodprocessing industnew type of processor and to make recommendations to the company’s board.They could use gas or electric power sources but for technical reasons each power source woul1. (10 Points) In the Decision Tree below, fill in the color coded cells as follows:Color KeyEMVNOTE: I expect you to use Excel formulas to calculate the EMV’s. FailDecision (place the number you choose in this color cell)Probability (do not change these numbers)Payoffs in Millions (do not change these numbers)0.75Success0Develop Electric0.25Failure0.6SuccessDevelop Gas0.4Failure10State your solution below:1. (5 points) Which processer should we develop and why?2. (5 Points) What should we do if that processor fails and why?te the Decision Tree. State your answer below the tree in the designated area.odprocessing industry has been asked to consider the development of aoard.h power source would require a fundamentally different design.ells as follows:late the EMV’s. Failure to do so will result in a loss of points.Millions100.3Success6Modify Design0.7Failure-7Abandon Project-3150.8Success10Modify Design0100.2Failure-12Abandon Project-7Instructions: (20 points) Read the following scenario first and then complete the pThe Style and Comfort Furniture Manufacturing Company wishes to determine its production scincluding sofas, love seats, recliners, and coffee tables. The profit contribution for selling each isItem Sofa Love SeatReclinerCoffee TableProfit $ 120 $ 105 $ 150 $ 73The quarterly production budget is set at $180,000.Each unit of a sofa, love seat, recliner, and coffee table costs the following:Item Sofa Love SeatReclinerCoffee TableCost $ 400 $ 300 $ 500 $ 150The sales forecasts indicate that the potential sales volume is limited to the following:Item Sofa Love SeatReclinerCoffee Tablees Limits200150100400There is an aggregate of 800 machine hours available and 1,200 labor hours available.Item: Sofa Love SeatReclinerCoffee Tableurs/Unit212.20.75urs/Unit2.5231The management also imposed the policy that there must be at least 30 love seats manufactureLet x1 be the Sofas, x2 be the Love Seats, x3 be the Recliners, and x4 be the CoffeeNow let’s stateMaximize profit = 120*x1 + 105*x2 + 150*x3 + 73*x4Now let’s state the constraints mathematically:Budget 400×1 + 300×2 + 500 x3 + 150 x4 < 180,000s Volume x1 < 200s Volume x2 < 150s Volume x3 < 100s Volume x4 < 400Capacity 2×1 + 1×2 + 2.2×3 + .75×4 < 800Capacity 2.5×1 + 2×2 + 3×3 + 1×4 < 1200rement 3 x2 > 30Here is the spreadsheet model for the problem. YOU DO NOT HAVE TO CHANGE ANSofas Love SeatReclinerCoffee Tableit profit $ 120 $ 105 $ 150 $antitiesConstraiBudgets Volumes Volumes Volumes VolumeCapacityCapacityirement73RightHandSofas Love SeatReclinerCoffee TableInequality Side400300500150 <=1800001000 <=2000100 <=1500010 <=1000001 <=400212.20.75 <=8002.5231 <=12000100 >=30TotalProfitActualUsageHere is what you need to do!!(5 Points) Set up the formulas in cells I42 and I46:I53. Set up the constraints in SolvChoose to display the Answer Report and the Sensitivity Report.Answer the following questions based on those reports.1 (5 Points) What is the optimal solution? Note, it is OK to report fractional v2 (5 Points) Specify the Slack for the following constraints. Explain what thatConstraintSlack Interpretation of SlackBudgeta Sales VolumeHour Capacity3 (5 Points) Explain what the Shadow Price means for the Recliner Sales Volumn complete the problem using Solver. State your answers below the output in the designatne its production schedule for the next quarter. The company produces four types of furniture,on for selling each is as follows:following:available.e seats manufactured.x4 be the Coffee TablesVE TO CHANGE ANYTHING HERE!constraints in Solver and run it (be sure to choose the Simplex LP method).eport fractional values.Explain what that means with respect to the problem.ecliner Sales Volume constraint only.ut in the designated area.furniture,Instructions: (20 points) Follow the instructions given at each step in order to comConsider the following data:Product CategoryProduct NamePurchase SellingCost PriceProfitBlueLeisure Moon$75.29 $105.41 $30.121 (5 Points) Use COUNTIF() to determine theAlso calculate the probability of each categoProduct CategoryFrequencyProbabilityLeisureBluffMtn. Breaker $375.00 $495.00###Leisure Breeze $89.95 $130.95 $41.00Mtn.RoadBreezeLeisure LE$109.95 $149.95 $40.00ChildrenRoadClassic109$207.49 $290.49 $83.00ChildrenMtn.Mtn.Mtn.HybridCoolest100Eagle 1Eagle 2Eagle 3Eagle 7RoadElegant210$281.52 $394.13Childre GreennRiderMtn.$69.99 $97.98 $27.99$410.01 $574.01###$401.11 $561.54###$350.52 $490.73###$150.89 $211.46 $60.57Total2 (5 points) Using the above Frequencies, creLabel the X-axis with the Product Category n###Delete the legend and include an appropriat$95.47 $133.66 $38.19Place the bar chart in the space provided beJettyBreaker $455.95 $649.95Childre RednRiderHybrid###$15.00 $25.50 $10.50RunbleHybrid nd 2000 $180.95 $255.99 $75.04Childre Runkidnder 100 $50.95 $75.99 $25.04RoadRunroad 1000 $250.95 $350.99###Resize the bar chart to fit into this sp3 (5 points) Using Excel Functions, determine each of the following:a.Find theofaverdeviationPurchaseCost:greater than$300.Cost is less than$100.What can you tell me about our product inveCost is between$100 and $300.4 (5 points) Consider the following data:Product CategoryAverage ProfitLeisure $37.04Mtn.###Road $98.55Children $25.43Hybrid $67.81Create a pie chart for the Average Profit.Delete the legend and add data labels that show the category name and percent. Add an apprPlace the pie chart in the space provided below.Resize the Pie chart to fit this spaceWhat can you tell me about the distribution of the Average Profit?p in order to complete the problems.F() to determine the frequency of each Product Categorybility of each category occurring using Excel formulas.ove Frequencies, create a VERTICAL bar chart.Product Category names and the Y-axis with Frequency.clude an appropriate title.e space provided below.hart to fit into this space.out our product inventory based on this bar chart?ercent. Add an appropriate title.Instructions: (30 points) Follow the steps outlined below in order to perfom a RegrFirstTotalTotalYear Produc Promoti TotalReciept tiononalBooksCosts Costs Sales(Million (Million (Million (Millions)s)s)s)$85.10 $ 8.50 $ 5.104.70### $12.90 $ 5.808.80$50.20 $ 5.20 $ 2.10 15.10### $10.70 $ 8.40 12.20$54.80 $ 3.10 $ 2.90 10.60$30.30 $ 3.50 $ 1.203.50$79.40 $ 9.20 $ 3.709.70$91.00 $ 9.00 $ 7.605.90### $15.10 $ 7.70 20.80$89.30 $10.20 $ 4.507.90Consider the data given here regarding movieWhat affects the sales of movie tickets (i.e., Firegression analysis for the three independentBased on the Multiple Regression Results, Ansa) (5 Points) What is the Multiple R and what does tMultiple R =b) (5 Points) What is the Adjusted R Square and whaAdjusted R Square =c) (5 Points) Test the Hypothesis: H0:b1 =Based on an a = 0.05, what is your decision and whaSignificance F =Decision:Conclusion:d) (5 Points) Now look at the individual p-values. WhIndependent VariableTotal Production CostsTotal Promotional CostsTotal Book SalesKeep?(Yes/No)p-valuee) (5 Points) What would you do to make this modelf) (5 Points) Based on part (e), rerun the model andBased on these results, what is the equation of the rBe sure to use the names of the variables, not X1, X2First Year Receipts =to perfom a Regression/Multiple Regression Analysis.regarding movie ticket sales.vie tickets (i.e., First Year Reciepts)? To answer this question, run a multipleree independent variables. Label the tab “Multiple Regression”.ssion Results, Answer the Following:e R and what does that tell you about the model so far?d R Square and what does that tell you about the model so far?: H0:b1 = b2 = b3 = 0, H1: at least one b ? 0 using the Significance F p-value.ur decision and what do you conclude?vidual p-values. Which independent variables should we keep in the model and why?to make this model better?Why or why not?erun the model and label the resulting worksheet “Reduced Regression”.he equation of the regression line.variables, not X1, X2, â€¦