Sem: Dec-2024 Maximum Marks: 50 Examination: End Exam Date: 8.12.24 Duration: 2.5hrs
Programme code: 18 Programme: MBA for Working Executive	Class: FY	Semester: III Batch- 2023-24
College: K. J. Somaiya Institute of Management	Name of the department/Section/Center: Business Analytics
Course Code: 117P18C302	Name of the Course: Decision Science
Instructions: There are a total of 4 questions. All questions are compulsory. There is internal choice in Q1 and Q3. You will be assessed for your abilities to formulate the problem, model it in excel, solve it with Solver, and interpret the results. Make suitable assumptions if required and state them. Write all relevant answers and interpretations in your excel sheet with sufficient details to enable a fast evaluation of your answers. Use Excel and Solver as required and keep saving the file every ten minutes or so. Make only 1 Excel file with different worksheets pertaining to each question. Name the files as “Roll Number_Name_MBAWE01”

Question No.	Max. Marks
Q1	The Marketing Department of Everest Company has collected information on the problem of advertising for its products. This relates to the advertising media available, the number of families expected to be reached with each alternative, cost per advertisement, the maximum availability of each medium and the expected exposure of each one (measured as the relative value of one advertisement in each of the media): The information is as given here: Other information and requirements: i. The advertising budget is Rs 70,000 ii. At least 40,000 families should be covered. iii. At least 2 insertions be given in Sunday edition of a daily but not more than 4 ads should be given on the TV. Formulate the above problem as a linear programming problem if the company wishes to allocate media such that it maximise the expected exposure. Interpret the binding constraints in the context of the problem. OR Management Sciences Associates (MSA) is a marketing and computer research firm based in Washington, D.C., that handles consumer surveys. One of its clients is a national press service that periodically conducts political polls on issues of widespread interest. In a survey for the press service, MSA determines that it must fulfill several requirements in order to draw statistically valid conclusions on the sensitive issue of new U.S. immigration laws: Survey at least 2,300 U.S. households in total. Survey at least 1,000 households whose heads are 30 years of age or younger. Survey at least 600 households whose heads are between 31 and 50 years of age. Ensure that at least 15% of those surveyed live in a state that borders on Mexico. Ensure that no more than 20% of those surveyed who are 51 years of age or over live in a state that borders on Mexico. MSA decides that all surveys should be conducted in person. It estimates that the costs of reaching people in each age and region category are as follows: MSA would like to meet the 5 sampling requirements at the least possible cost. Formulate the given problem as a linear programing problem and obtain the optimal number of surveys to be conducted for every region under the given age categories. Interpret the binding constraints in the context of the problem.	15
Q2	Adirondack Savings Bank (ASB) has $1 million in new funds that must be allocated to home loans, personal loans, and automobile loans. The annual rates of return for the three types of loans are 7% for home loans, 12% for personal loans, and 9% for automobile loans. The bank’s planning committee has decided that at least 40% of the new funds must be allocated to home loans. In addition, the planning committee has specified that the amount allocated to personal loans cannot exceed 60% of the amount allocated to automobile loans. The formulation for the problem is given below. Solve the above LPP and obtain the Answer and Sensitivity Report to answer the following questions How much should be allocated to each type of loan? What is the total annual return? What is the annual percentage return? If the interest rate on home loans increased to 9%, would the amount allocated to each type of loan change? Explain. Simultaneous to the above, personal loans drop by 2%, how would this impact the optimal solution? Suppose the total amount of new funds available was increased by $10,000. What effect would this have on the total annual return? Explain.	10
Q3	Tropicsun is a leading grower and distributor of fresh citrus products with three large citrus groves scattered around central Florida in the cities of Mt. Dora, Eustis, and Clermont. Tropicsun currently has 275,000 bushels of citrus at the grove in Mt. Dora, 400,000 bushels at the grove in Eustis, and 300,000 bushels at the grove in Clermont. Tropicsun has citrus processing plants in Ocala, Orlando, and Leesburg with processing capacities to handle 200,000, 600,000, and 225,000 bushels, respectively. Tropicsun contracts with a local trucking company to transport its fruit from the groves to the processing plants. The trucking company charges a flat rate for every mile that each bushel of fruit must be transported. Each mile a bushel of fruit travels is known as a bushel-mile. The following table summarizes the distances (in miles) between the groves and processing plants: Further, the route from Eustis to Orlando is blocked because of construction. Considering the transporting constraints, solve to determine how many bushels to ship from each grove to each processing plant to minimize the total number of bushel-miles the fruit must be shipped. Identify the plant that does not receive its full capacity and by how much is it deficit. OR A market research firm’s three clients each requested that the firm conduct a sample survey. Four available statisticians can be assigned to these three projects; however, all four statisticians are busy, and therefore each can handle only one client. The following data show the number of hours required for each statistician to complete each job; the differences in time are based on experience and ability of the statisticians. Statistician Client A B C 1 150 210 270 2 170 230 220 3 180 230 225 4 160 240 230 Solve the above assignment problem and obtain the optimal assignment. If Statistician 4 is unavailable, how does the assignment change? Which scenario is more optimal?	10
Q4	A nutraceutical company is planning a promotional program for one of its health supplements. The promotional program envisages a cost of ₹3000000 per month if the monthly demand for the supplement is estimated to not exceed 50000 packs, or ₹1000000 per month if the monthly demand for the supplement is estimated to exceed 50000 packs. The production cost of one pack of the health supplement is uniformly distributed between ₹230 to ₹270 and its selling price is ₹850 per pack. The monthly demand for the health supplement is estimated to be normally distributed with a mean value of 50000 and a standard deviation of 2000. Simulate the profit for 60 months (5 years) & determine The average monthly promotional program cost The average monthly production cost The average monthly profit. Probability that the demand will be less than 50,000 packs. Probability of receiving a profit greater than 30,000,000.	15

********************** All the Best *********************