SUMMATIVE ASSIGNMENT 3 – BUSI4AY15
Business Analytics
Masters Programmes 2024/25
For this assignment, you will be provided a data set in Excel and an Excel answer sheet. At the bottom of this assignment, you will find a list of exercises to execute on the data set provided. You are input your answers into your Excel answer sheet and submit it on Learn Ultra.
SUBMISSION INSTRUCTIONS
FORMAT
You are to submit the Excel answer sheet with the file name adequately changed as instructed. You are not to alter the structure of the Excel answer sheet. The Excel file should be kept in the .xlsx format.
MARKING GUIDELINES
The number of marks carried by each question in the exercise is indicated clearly in this assignment.
PLAGIARISM AND COLLUSION
Note that your data set is unique to you, correspondingly, the answers that you will obtained will also be unique to you.
Students suspected of plagiarism, either of published work or the work of other students, or of collusion will be dealt with according to School and University guidelines.
SPECIFIC INSTRUCTIONS
1. You will be able to find your data set for this assignment in the “Data Sets” folder. All of the files in this folder are named “yourZnumber_Number1_Number2.xlsx”. You are to find the data set corresponding to your Z number. Download this data set.
2. You are strongly advised to save a copy of the data set in a safe location, in the unlikely but potential event that you accidentally overwrite the data during the process of your analysis.
3. Note that all of your colleagues have been provided different datasets, and consequently will arrive at different correct answers for the assignment. As such, please ensure that you use the data set that corresponds to your Z number and not the data sets of any of your colleagues.
4. In the assignment folder, you will also find an Excel file that is labelled “yourZnumber_SA3.xlsx”. This is your answer sheet, which you use to fill your responses in for each question and which you will submit on Learn Ultra. Change yourZnumber in the file name to your Z number right now.
5. As your assignment is machine-graded, any minute error in your file name will render it unreadable by the machine and will lead to a complete loss of marks, so please ensure that the file name is correct.
6. The Excel answer file contains two columns. The first column lists the question numbers to which an answer is expected in the Excel answer file. In the second column, you are to key in your answer to the corresponding question there. Do not alter the structure of the Excel file, namely, do not add new rows or columns or key in any values outside of the demarcated area.
7. You will be required to key different types of answers in a specific form.
a. For numerical answers, please leave your answers with at least 3 significant figures (in other words, with at least 3 non-zero digits, e.g. you may reflect “12.345” as “12.3” and “0.01234” as “0.0123”). If your numerical answer is a whole number, leave them as such (e.g. you may leave “3” as “3” as opposed to “3.00” that is in 3 significant figures).
b. If you are required to report probabilities, for example, p-values, if the numerical value is smaller than 0.0001 or 1e-4, please report the value as 0. If you are asked to report probabilities or proportion or percentages, please reflect the value in decimals (e.g. report 78% as 0.78). Never reflect your answer as a fraction (e.g. for 1/3, instead use 0.333 in 3 significant figures).
c. For multiple choice questions in this assignment, feel free to leave your responses in any of large or small capitals.
8. Wrong answers have a potential to carry partial marks.
9. This assignment comprises a total of 18 questions, amounting to a total of 100 points.
ASSIGNMENT QUESTIONS
In this assignment, we are going to consider a company that produces six types of products (Products A, B, C, D, E and F) and serves five different markets (Markets V, W, X, Y and Z). The operating circumstance of the company is described as follows:
· Each product has a cost to produce and will fetch a different given revenue when sold in each specific market.
· The company incurs per unit logistical costs for transporting a type of product to each market.
· There is a total production capacity per month at the factory that is measured in the units of effort, with each product costing a particular amount of effort to produce.
· Each market demands a certain number of each of the products every month. It is assumed that demand does not need to be met, but cannot be exceeded, ie., the demand reflects the maximum number of each product that can be sold in that market.
Every month, the company is required to decide on the production level of each product and the logistical plan to transport how many of each product to each market to be sold.
Parameters
The following table shows the cost of producing each product and the revenue it will generate at each market.
|
|
A
|
B
|
C
|
D
|
E
|
D
|
|
Cost
|
1.1
|
1.5
|
1.3
|
1.2
|
1.0
|
1.4
|
|
Product
|
Market
|
|
V
|
W
|
X
|
Y
|
Z
|
|
A
|
3.2
|
2.6
|
2.7
|
3.0
|
3.4
|
|
B
|
2.7
|
2.9
|
3.4
|
3.2
|
2.6
|
|
C
|
2.9
|
3.3
|
2.7
|
3.0
|
3.1
|
|
D
|
3.1
|
3.4
|
3.1
|
2.8
|
2.8
|
|
E
|
3.1
|
3.4
|
3.2
|
3.4
|
2.6
|
|
F
|
3.3
|
2.5
|
3.0
|
3.5
|
3.4
|
The following table shows the per unit cost to transport a product to each market.
|
Product
|
Market
|
|
V
|
W
|
X
|
Y
|
Z
|
|
A
|
1.0
|
1.4
|
0.7
|
0.6
|
1.1
|
|
B
|
1.0
|
1.5
|
0.9
|
0.8
|
0.6
|
|
C
|
0.8
|
0.9
|
1.5
|
0.8
|
0.7
|
|
D
|
0.9
|
0.5
|
1.2
|
0.7
|
0.6
|
|
E
|
1.0
|
0.6
|
1.4
|
1.2
|
0.7
|
|
F
|
0.8
|
1.4
|
0.6
|
1.3
|
1.3
|
The following table shows the effort require to produce each product. The total available effort per month at the factory is 30 units of effort.
|
|
A
|
B
|
C
|
D
|
E
|
F
|
|
Effort
|
0.0038
|
0.0031
|
0.0036
|
0.0032
|
0.0033
|
0.0030
|
The historical demand observed for each product in each market is captured in the data set that is provided to you. In your data set, each row represents the demand observed in a month in a particular market. As such, the data set has three columns: ‘product’ indicating the product, ‘market’ indicating the market, and ‘demand’ the observed demand for that product and that market in that month. For every pair of product and market, there are 50 records of the demand over 50 months, hence amounting to a total of 1,500 data points.
To verify that you have downloaded the correct data set, please calculate the mean and variance of the demand across the 50 months for Product A and Market V. In the file name of your data set, you will see “yourZnumber_Number1_Number2.xlsx”. Numbers 1 and 2 would be the mean and variance respectively that you had calculated.
Do not proceed if these numbers are not correct! Verify with your tutor if they are incorrect.
In this assignment, we shall assume that the company is interested in maximizing their profits. This is understood as the revenue minus the costs. The only source of the revenue is when products are sold at each of the markets. The costs comprise both production costs and transportation costs. The company is constrained by total production in the factories, in the units of effort, and the demand for the products at each of the markets cannot be exceeded.
Use
to denote the decision variable of how many of Product P (P = A, B, C, D, E or F) is to be transported to be sold at Market M (M = V, W, X, Y or Z). (The letter at the top will always represent the market and the letter at the bottom will always represent the product, e.g. , ,…)
Assignment
Question 1
8 points
How many decision variables are there in this optimization problem?
Choose the most appropriate answer from the following responses and key in one of A, B, C or D into your Excel file.
A There are 36 decision variables, where 30 (=6 products X 5 markets) arise from the decisions of how many of each product to transport to each market; and 6 arising from how many of each product to produce.
B There are 35 decision variables, where 30 (=6 products X 5 markets) arise from the decisions of how many of each product to transport to each market; and 5 arising from how many products are sold at each market.
C There are 41 decision variables, where 30 (=6 products X 5 markets) arise from the decisions of how many of each product to transport to each market; 6 arising from how many of each product to produce; and 5 arising from how many product are sold at each market
D There are 30 decision variables, where 30 (=6 products X 5 markets) arise from the decisions of how much of each product to transport to each market. How many of each product to produce can be inferred from these decisions.
Question 2
3 points
The total number of product A that is produced can be expressed in the following linear manner in terms of the decision variables, where the ‘?’ (question marks) and [Q2] are some constants (known numbers), that are not necessarily the same.
What number should go into [Q2]?
Question 3
3 points
Building on Question 2, the total cost of production of Product B can be expressed in the following linear manner in terms of the decision variables, where the ‘?’ (question marks) and [Q3] are some constants (known numbers), that are not necessarily the same.
What number should go into [Q3]?
Question 4
3 points
Building on Question 3, the total cost of production across all products can be expressed in the following linear manner in terms of the decision variables, where the ‘?’ (question marks) and [Q4] are some constants (known numbers), that are not necessarily the same.
What number should go into [Q4]?
Question 5
3 points
The total transportation cost of all products to all of the markets can be expressed in the following linear manner in terms of the decision variables, where the ‘?’ (question marks) and [Q5] are some constants (known numbers), that are not necessarily the same.
What number should go into [Q5]?
Question 6
3 points
The total revenue generated by all products in all of the markets can be expressed in the following linear manner in terms of the decision variables, where the ‘?’ (question marks) and [Q6] are some constants (known numbers), that are not necessarily the same.
What number should go into [Q6]?
Checkpoint 1: Objective function
Based on your response to Questions 4, 5, and 6, you should be able to write out your objective function, which is the revenue minus the total cost (comprising production cost and transportation cost).
Question 7
3 points
The constraint that the total effort required to manufacture all of the products at the factory can be expressed in the following linear manner in terms of the decision variables, where the ‘?’ (question marks), [Q7] and [Q8] are some constants (known numbers), that are not necessarily the same.
What number should go into [Q7]?
Question 8
3 points
Looking at the same equation in Question 7, what number should go into [Q8]?
Question 9
8 points
For the constraint limiting the number of each product sold under the demand, for now, we will use the average demand that you would be able to calculate from your data set.
What is mean demand for Product D in Market X?
Question 10
3 points
The constraint that the quantity of Product A that is transported to Market V should not exceed the demand for Product A at Market V can be expressed in the following linear manner in terms of the decision variables, where the ‘?’ (question marks), [Q10] and [Q11] are some constants (known numbers), that are not necessarily the same.
What number should go into [Q10]?
Question 11
8 points
Looking at the same equation in Question 10, what number should go into [Q11]?
Question 12
8 points
Apart from the non-trivial constraints (which constrains all of the x-variables above zero), how many constraints would your optimization model have?
Choose the most appropriate answer from the following responses and key in one of A, B, C or D into your Excel file.
A We would have 2 constraints, one constraining the total quantity of products by the effort and one constraining the total quantity of products by the demand.
B We would have 6 constraints, one constraining the total quantity of products by the effort and 5 constraining the total quantity of products by the demand at each of the markets.
C We would have 11 constraints, 6 constraining the total quantity of products by the effort for each of the products and 5 constraining the total quantity of products by the demand at each of the markets.
D We would have 31 constraints, one constraining the total quantity of products by the effort and 30 constraining the total quantity of products by the demand at each of the markets for each of the products.
Checkpoint 2: Constraints and optimization model
Based on your response to Questions 7 to 12, you should be able to write out the full optimization model.
Question 13
21 points
Solve for the optimal decisions x for the company. What is the optimal profit that the company can earn?
Question 14
3 points
What is the optimal number of Product D to transport to Market Z?
Question 15
3 points
What is the optimal total quantity of Product E to produce at the factory?
Question 16
6 points
Generate a sensitivity report for your optimization model.
It turns out that the total effort required for production in the factory is measured in FTE (full time equivalents). In other words, the total effort of 30 actually reflects 30 workers. Management is currently considering the possibility of hiring an additional worker part time worker at 0.5 FTE to bring the total number of workers to 30.5. Without solving a new model, what is the highest monthly salary that should be offered to this worker so that hiring the worker would be cost effective?
If it is not possible to infer this value from the sensitivity report, key in 999 into your answer sheet.
Question 17
3 points
In building our optimization model, we have assumed that the demand is the average demand. However, in the data set, we actually do observe a range of values of the demand. It might be necessary to see what is the impact on the optimal decision of using the average demand.
What is the fifth smallest demand of Product C in Market Y observed in the data set?
Question 18
8 points
Based on the sensitivity report and without solving a new model, what is the consequence on the optimal profits that the company earns should the demand for Product C in Market Y not be the average demand, but rather, only the 10th percentile amongst all historic demand observed?
Choose the most appropriate answer from the following responses and key in one of A, B, C or D into your Excel file.
A There would be no impact on the optimal profits because at present, the demand constraint for Product C in Market Y is not tight and the change in the demand is within the allowable decrease.
B Optimal profits will fall, however, it is unclear by how much, because the demand constraint for Product C in Market Y is at present not tight, but the change in the demand lies outside the allowable decrease.
C Optimal profits will fall and the fall is given per unit by the shadow price, because the demand constraint for Product C in Market Y is at present tight, and the change in demand is within the allowable decrease.
D Optimal profits will fall, however, it is unclear by how much, because the demand constraint for Product C in Market Y is at present tight, but the change in the demand lies outside the allowable decrease.