MGMT 3115 EXAM 1 Cal State East Bay By completing and submitting the answer to the exam, the student fully understands the university's policy on academic dishonesty and agree to abide by this policy. Policy on Academic Dishonesty: By enrolling in this class the student agrees to uphold the standards of academic integrity described in the catalog at http://www.csueastbay.edu/ecat/current/i-120grading.html#section12. Cheating will result in: 1) a zero score and the loss of all grading options; and/or 2) an "F" grade; and/or 3) referral to the Academic Vice President for expulsion from the University. Notice: 1. Please answer all of the questions in the attached excel file. Submit the excel file only. 2. Submit the excel file with all your answer in the BlackBoard. 3. Name the excel file as LastName_FirstInition_Exam1. (E.g., Wang_L_Exam1) 4. If you are unable to run the Solver, take a screenshot of the solver parameters window and paste it on the corresponding spreadsheet. (In this case, I can still give you partial credit even if your answer was wrong.) Part I: Linear Programing (65 pts in total) 1. Linear Programing problem - formulation and solving process (30 pts) A furniture manufacturer produces two types of tables (country and contemporary) using three types of machines. The time required to produces the tables on each machine is given in the following table. Machine Country Contemporary Router Sander Polisher 1.5 3.0 2.5 2.0 4.5 1.5 Total Machine Time Available per Week 1,000 2,000 1.500 Country tables sell for $350 and contemporary tables sell for $450. Management has determined that at least 20% of the tables made should be country and at least 30% should be contemporary. a) Formulate an LP model. Notice: please show clearly the definition of decision variables, objective function, and each one of the constraints. Please write the complete formulation in the same worksheet in the excel file. (10 pts) b) Create a spreadsheet model for this problem and solve it using Solver. Please take a screenshot when you set up the Solver window, then paste it in the same worksheet. (15 pts) c) What is the optimal amount for each type of tables and the optimal total profit? (Highlight the answers in the worksheet). (5 pts) 2. Linear Programing problem - formulation and solving process (35 pts) A financial planner wants to design a portfolio of investments for a client. The client has $400,000 to invest and the planner has identified four investment options for the money. The following requirements have been placed on the planner. No more than 30% of the money in any one investment, at least one half should be invested in long-term bonds, which mature in six or more years, and no more than 40% of the total money should be invested in B or C since they are riskier investments. The planner has developed a LP model based on the data in this table and the requirements of the client. The objective is to maximize the total return of the portfolio. Let Investment Return A B C D 6.45% 8.5% 9.00% 7.75% Years to Maturity 6 5 8 4 Rating 1-Excellent 3-Good 4-Fair 2-Very Good X1 = Dollars invested in A X2 = Dollars invested in B X3 = Dollars invested in C X4 = Dollars invested in D a) Please formulate this problem. Show clearly the objective function and constraints functions using the decision variables defined above. Please write the complete formulation in the same worksheet in the excel file. (5 pts) b) Create a spreadsheet model for this problem and solve it using Solver. Please take a screenshot when you set up the Solver window, then paste it in the same worksheet. (15 pts) c) What is the optimal amount for each type of tables and the optimal total profit? (Highlight the answers in the worksheet). (10 pts) Part II: Network modeling problem. (35 in total) 3. Multiple choice questions. (All are about the network modeling.) (3 pts each, 15 pts in total) 1) Demand quantities for demand nodes in a transshipment problem are customarily indicated by a. positive numbers. b. negative numbers. c. imaginary numbers. d. either positive or negative numbers. 2) The number of constraints in network flow problems is determined by the number of a. nodes. b. arcs. c. demands. d. supplies. 3) In a transshipment problem, which of the following statements is a correct representation of the balance-of-flow rule if Total Supply < Total Demand? a. Inflow Outflow Supply or Demand b. Inflow + Outflow Supply or Demand c. Inflow Outflow Supply or Demand d. Inflow + Outflow Supply or Demand 4) An oil company wants to create lube oil, gasoline and diesel fuel at two refineries. There are two sources of crude oil. Consider arc 2-4. The per unit shipping cost of crude B from source 2 (node 2) to refinery 2 (node 4) is $11 and the yield is 85 percent. The following flowchart depicts this problem. What is the balance of flow constraint for node 7 (Diesel)? a. X35 + X36 + X37 = 75 b. X37 + X47 75 c. 90 X37 + .95 X47 = 75 d. X37 + X47 X36 X35 X45 X46 75 5) How many arcs are required to make a spanning tree in a network with n nodes and m arcs? a. n b. n 1 c. m d. m 1 4. Formulation and solving process problem. (20 pts) A company needs to ship 100 units from Roanoke to Washington at the lowest possible cost. The costs associated with shipping between the cities are: From/To Roanoke Lexington Charlottesville Lexington 50 Washington 50 30 Charlottesville 80 40 a) Draw the network representation of this problem. You may either draw the figure anywhere you want to and copy the screenshot, or insert \"shape\" in Excel to draw directly in the Excel file. (5 pts) b) Create a spreadsheet model for this problem and solve it using Solver. Please take a screenshot when you set up the Solver window, then paste it in the same worksheet. Please Use the decision variables defined as follows. (15) Units to ship From To X12 X13 X23 X24 X34 1. ROA 1. ROA 2. LEX 2. LEX 3. CHA 2. LEX 3. CHA 3. CHA 4. WAS 4. WAS