Name Akula Padma Priya RN PGP12101 Section B

Indian Institute of Management Rohtak

Term –II
Mid Term Examination
Course Title : DATA ANALYTICS
Type of Test : OPEN BOOK/LAPTOP/DATA/OTHER MATERIAL
Duration : 150 minute
Weightage : 50%
Date of Exam : November 12, 2021 MM: 60
Please Note:
1. There are FIVE questions in all. Use the answer sheets given after each question for
writing your answers.
a. Start each answer from a fresh page.
b. You may add or delete pages as per your answer requirements
2. All answers should be analytical, precise and complete.
3. Write all Computational steps and other related information asked in the question in
your answer sheets only. [Evaluation on the basis of only answer sheet ]
4. Submit the EXCEL File Solution along with the answer sheet.
a. To submit your exam, make a folder named Name_ID_Section (e.g.
Nayan_PGP012001_A)
b. Save and zip/rar your answer sheets and all related excel files in the folder and
Email it to the email id given to you for submission.
5. Evaluation done only on the basis of Answer sheet. Please write all steps carefully
6. You may paste or share screen shots of your excel sheet (step by step) in the answer
sheets, if required.

Q1. The Geeta Parkashan Publishers have been offered to publish Namendra Modi’s
autobiography. The manager at Geeta Parkashan wants to understand the proposal
profitability amid the uncertainty between the demands of Mr. Modi’s autobiography.
The company is expected to give a one-time royalty payment of ₹ 50 Lakhs to Mr. Modi.
The book will be published in hard binded cover as well as soft paperback cover version. The
fixed cost of publishing the hard cover copy is ₹ 1 Lakh. The variable cost of producing one
hardcover book is ₹100. Each hardcover copy is sold at ₹700 and the manager is expecting to
sell around 10, 00,000 copies of hard cover autobiography. Similarly, the fixed cost of
publishing the paperback version is ₹ 10,000. The variable cost of publishing one paperback
copy is ₹25. Price of paper back copies are often fixed at 1/3rd of the hard cover copies at
Geeta Parkashan. As per the past ten years data, number of paperback copies sold for
autobiographies are double that of the hard cover copies. This will be used to estimate the
paperback demand from the estimated hard cover demand. (Hint: Find profit from hard cover
sales and soft cover sales first without considering royalty payments. Then find total profits
adjusting for royalty payments) (Q1.xlsx)
Write each computational step in your copy and paste logical excel computation if required.
Submit your Excel sheet also.
a) Determine how the publisher’s before tax profit will vary as the hard cover book
demand is expected to vary from 100,000 copies to 10,00,000 copies (use step
increment of 100,000) – and the ratio of paper back demand to hard cover demand
varies from 1:1 through 3:1 (use step increment of 0.2 starting from 1 to 3) [5]
Explanation:
For this question apart from the given data the demand for soft cover as a product of demand
for hard cover and the multiplier. And it was given selling price of soft cover is 1/3rd of the
selling price of hard cover. The profit for soft and hard cover is calculated by sales price with
cost per unit and the number of sold for each category less the variable and fixed cost. The
sum of both soft and hard copy cost is then added to get final profit. From this royalty
payment is reduced to get final profit.
To get the profit for varying demand and the ratio given is calculated using the data table
under what if by changing the rows(demand) as well as columns (ratio).
 b) If manager wishes to revise the royalty payment of ₹ 50 Lakhs to Mr. Modi, what
royalty they should pay to earn net before tax profit of 100 crore. [5]
Explanation:
For this I have simply subtracted the 100 crores from the total profit to get the new revised
payment to make

c) In a given uncertain environment about people sentiments about Mr. Modi – create
summary for the following three scenarios to understand the impact of uncertain
inputs of before tax profits. [5]
 a. Negative sentiment – Demand for hardcover copies will be 100,000 copies. Royalty
payment given will be ₹ 10 Lakhs.
b. Positive sentiment – Demand for hardcover copies will be 20, 00,000 copies. Royalty
payment given will be ₹ 60 Lakhs
c. Neutral sentiment – is the one already jotted by the manager in first three paragraphs.

Explanation:
For this question we need to add 3 scenarios in the scenario manager under the data> what if
Analysis to find the best scenario out of the 3.I have added all 3 scenario and from the
summary by changing the given variables and changing the profit.
Q2. Mitti Cool Company has ten skilled potters. The company produces three categories of
products – earthen refrigerators, terracotta art and craft items, and kitchen wares. This month,
the demand of the three product categories are as follows (Q2.xlsx)
Products This Month Demand Average Profit per Unit
Earthern Refrigerators 100 ₹ 3000
Art and Craft Items 2000 ₹ 65
Kitchen Ware 1000 ₹ 350

Each of the ten potters have different skills. The skill and the productivity in terms of number
of hours it takes in creating one unit of product is given in the table below:
Potter ID Earthern Art and Craft Items Kitchen Ware
Refrigerators
Potter 1 25 X 3
Potter 2 X 2 3.5
Potter 3 X 1 X
Potter 4 35 3 4
Potter 5 45 3 3
Potter 6 X X 0.5
Potter 7 15 X X
Potter 8 X 0.5 5
Potter 9 30 1 1
Potter 10 32 X 2

Each potter can work up to 170 hours per month.

a. What and how much should Mitti Cool produce so as to maximize its monthly profit?
[3]
b. Formulate the constraints and the objective. [4]
c. Comment on the linearity and smoothness of the constraints and objective function
clearly; and field values and setting options you will choose on the solver. [3]
Assume fractional number of units can be produced. Assume there is abundant supply of
raw material and only constrained resource is the potters’ time (potter-hour).
Write each computational step in your copy and paste logical excel computation if required.
Submit your Excel sheet also.
Explanation:
We Are going to use excel solver in this question
Step1: make a table with all potter from 1 to 10 and all products Earthern Refrigerators Art
and Craft Items and Kitchen Ware , and then mark all values zero

Step 2: calculate the total no. of product in the cell D30:F30

Calculate the total profit by using the sumproduct : =SUMPRODUCT(D30:F30,D33:F33)
Lets go to solver now in the data tab :
Set the objective function as the toal profit values and then
max as we want maximum profit form this problem
the decision variable will be the values in the tables.D19:F28
Constraints:
Write the constraints as given
reason for constraints
Some cells are kept zero like D20,D21,F21etc because in the given question the potters are
not making these product and given X athus marked zero,
The time taken by each potter will be less than equal to available time as they cannot exceed
that time
The total product will be less than equal to the demand as demand will be always gereater
than the product made

Use Simplex LP
Hit solve and get the answer
Thus the profit that is maximum will be $347508.3333.
Q3. The sheet Q3.xlsx contains the distance (in miles) between all NBA arenas. You can
start from New York OR Chicago (must fix any one) and want to visit each arena once and
return to New York OR Chicago (starting and ending station must be same) respectively. In
between you must visit Seattle immediately after Dallas. You are not allowed to repeat any
city. If you do not follow proper order for starting and ending point then you are penalized by
$5000 and also if you do not follow the desired sequence (Seattle immediately after Dallas)
then you are penalize by $ 4000. In what order should you visit the cities to minimize the
total distance. Write all computational step with proper logic (step wise) and submit solved
excel sheet also. [10]
Explanation:
The first step here is to form the table with 3 columns – 1st column having numbers 1 to 30,
next column having random numbers from 1 to 30 and last column with the city names
matching the values in the second column. We use index function for this purpose.
These columns will act as our initial values.

Next we add a column to find the distance between the 2 consecutive cities- this is done by
finding the answers from the table using index and the 2 adjacent orders. For example for
distance for 3rd and 4th cities visited – in the 4th row we will enter index to check for the
order of 3rd and 4th row- acting as row and column parameters for the index function.

Next we add a row at the bottom to give the answer to total of the sum of distances

Now we will make the penalties-

There are 2 types of penalties –
1) If starting is not the row number of Chicago or new York- give penalty of 5000
2) If the difference between Dallas and Seattle is not 1 give penalty of 4000- since
anyways new York or Chicago needs to be 1 – we do not need to worry about dallas being
last and seattle being 1.
Final objective function is sum plus the penalties.
This question we try to minimize our total distance using evolutionary method since the
function is non-linear and non smooth – using index functions

1) Objective – sum plus penalties – we add penalties as we want to minimize

2) Constraints – all the the order values need to be different we use diff option- ensure
we have deselected the ignore integers option
3) Changing variables- the order values
 Q4. Company Nebula manufactures and sells the oxygen cylinder with kit (April, 2021
Covid situation in India) to different hospitals in the city. Company operates only two
departments: Manufacturing and Sales department. Currently, there are 20 workers
employed in the company and these workers are skilled enough that they can be rotated
across both the departments. The company manager is attempting to schedule work for 20
workers. Each of these workers will work for four consecutive days; hence can be
assigned to one of the following schedules,
Schedule Days Worked
1 Monday-Thursday
2 Tuesday-Friday
3 Wednesday-Saturday
4 Thursday-Sunday
5 Friday-Monday
6 Saturday-Tuesday
7 Sunday-Wednesdays

Each worker will be assigned for the week to either the manufacturing department or
sales department. Each worker’s satisfaction with his/her assignment has been provided in
sheet Q4.xlsx. For instance, if worker 5 is assigned to manufacturing, he/she gives a perfect
10 rating to work schedule 3. Both the departments are operational on all days of week. The
manufacturing department needs at least 6 workers each day for its proper functionality while
the sales department needs at least 5 workers each day. Schedule the workers to maximize
their satisfaction and meet the needs of both the departments. In case, the minimum
worker need of any department does not meet, it has a penalty of 50 units associated with the
shortage of one worker. Also, you will need to know which schedules satisfy the need of
minimum number of workers required for different days of week. For instance, workers who
start work on Monday, Friday, Saturday and Sunday (i.e. under schedule 1, 5, 6, and 7) will
be available to work on Monday. Similarly, the workers who have started work on Saturday,
Sunday, Monday and Tuesday (i.e. assigned under schedule 6, 7, 1 & 2) will be available to
work on Tuesday (As indicated in the Table named as
Number_of_employees_on_duty_on_each_day_of_week). (Mention each possible
computation steps with proper formula (in terms of better understanding) in your answer
sheet, marking based on what you mentioned logically in your answer sheet) [15]
Explanation:

● As we are provided with schedule of sale and manufacturing along with satisfaction
we will first find out the number of workers in manufacturing and sales department
individually on each day (ie. Monday, Tuesday etc).
● For doing so we will use the table containing satisfaction and number of employees
on duty on each day of week table
● For finding total manufacturing workers on Monday we will use
=SUM(COUNTIFS(F29:F48,$A$2,G29:G48,L30:O30)) this formulation
● Here we are using the countif to find condition and count number of manufacturing
workers working on Monday and sum is used to find total number.
● Similarly we will find for Sales and drag it down to apply for all the days of the week.
● Now we will find the shortage using the formula
=SUM(IF(P30>$Q$27,0,$Q$27-P30),IF(Q30>$Q$28,0,$Q$28-Q30))
● Total penalty will be sum of shortage =SUM(R30:R36)
● And total will be 50*penalty = 150
Q5. I want to fill my bag with items that give me maximum benefit on my tour .The weight
in Kilogram and benefit of each item are given in as per below table (Q5.xlsx).

Weight(KG
Item Number Item Benefit )
1 Flashlight 8 1
2 Cereal 5 3
3 Cake 8 2
4 Water 4 1
5 Juice 5 3
6 Bug Spray 8 2
7 Umbrella 5 2
8 Knife 9 2
9 Cheese 3 2
10 Chicken 4 3

The bag can hold at most 15 Kilogram. At least one drink (water or Juice) and at least one
protein (cheese or Chicken) must be packed .How can I obtain maximum benefit from items
in my Bag. Write each computational steps in your copy with solution. Submit sol excel file
also. [Hint: SUM function on specific items may be used to capture “at least” constraint] [10]

Explanation:
We know that total weight of the bag needs to be less than equal to 15 (cell: D24)
The no. of each item is taken as 1, the total calculated if sumproduct of no. of items and their
weights in F20=SUMPRODUCT(E9:E18,F9:F18), Calculating total benefit by sumproduct
of benefits, weights and no. of items F21=SUMPRODUCT(D9:D18,E9:E18,F9:F18)
Taking into account minimum requirements, for drink= sum of water & juice and for
protein=sum of cheese & chicken.

Using GRG nonlinear in solver to maximize the benefits in F21, by changing the no.of items
in F9:F18
Adding constraints:
no. of items to be integers
Total weight of the bag <= 15
Minimum requirements of drinks & protein >=1
We get total benefits =118, at 6 knives, at 1 cheese and 1 water