代写Analytics、代做c/c++，Java，Python程序设计、data代写

日期：2020-09-03 10:18

Supply Chain Analytics Assignment

Master in Business Analytics

Due Date: 2 PM Tuesday, September 15, 2020

Mark Component: 35% of overall assessment, consisting of six questions totalling 150 points:

Question One 20 marks

Question Two 25 marks

Question Three 25 marks

Question Four 25 marks

Question Five 20 marks

Question Six 20 marks

TOTAL 150

Marking Scheme:

Questions One, Two, Four, Five and Six

The answers shall be self-contained with final conclusions, LP or ILP formulations and working (where

required) and brief explanations stated succinctly for each sub-problem. All code/spreadsheets are to be

provided in an accompanying file or appendix.

Question Three

The report has to be self-contained: It has to include an explanation of the model or models used and the

applied methodology to estimate the parameters of the proposed discrete choice model/models.

The Excel and/or any other code or file developed to solve the problem must be provided. Nevertheless,

it should be possible to understand what has been done by only reading the report.

The overall report and methodology used to solve the revenue management questions has a weight

of 25 points for the assignment. Another 15 points will be given based upon performance: Groups will be

ranked based on the quality of their predictions (the way in which predictions are evaluated is explained

at the end of the problem statement).

The following table provides the points given to each group based on the prediction ranking. The

top group gets the maximum possible, (i.e. 15 marks), and the groups with lowest predicted power obtain

3 marks.

Ranking Marks Ranking Marks

1 15 4 5

2 11 5 4

3 8 6 and less 3

1

QUESTION 1 - Network Problem for Lifezest Drug manufacturing

Lifezest Drug manufacturing company processes four different chemicals in some or all of its 3 plants.

Each month, these chemicals are transported to 4 warehouses which work as a link between the plants

and 6 distributors. The monthly demand from the 6 distributors is as follows:

Demand of chemicals (in thousand litres per month)

Distributor Chemical1 Chemical2 Chemiccal3 Chemical4

Caremark 9.5 8 8 10

MediCraft 8 4 8 2

Greens 4 6 6 3

Walkers 3 1 .5 1.5

HealthAura 5 3 5 5

FirstCare 4.5 3 4 6

The monthly capacity in terms of total litres of chemicals for each of the three Lifezest’s manufacturing

plants is shown below. In addition, if a plant produces any of the 4 chemicals during a month,

there is a setup cost which is also shown below.

Capacity and setup cost of the plants

Plant Capacity Setup Cost

Newham 30,000 750,000

Springfield 72,000 1,800,000

Benloch 50,000 1,000,000

The warehouses at Lifezest have a limit on the amount of litres of chemicals they can handle in a

month. Those monthly capacities are given below.

Warehouse Capacity

Romsey 30,000

Broadford 35,000

Kilmore 34,000

Wallan 30,000

The following tables show the transportation costs (in $ per litre) associated to the chemical tranfer

from plants to warehouses and from warehouses to distributors:

2

Cost($/litre) Romsey Broadford Kilmore Wallan

Caremark 20 33 26 18

MediCraft 16 34 29 33

Greens 24 46 21 48

Walkers 23 19 23 28

HealthAura 18 37 12 14

FirstCare 17 34 43 16

Cost($/litre) Newham Springfield Benloch

Romsey 37 30 41

Broadford 35 29 23

Kilmore 13 41 25

Wallan 22 37 15

Questions

i) Propose an integer linear program to find a minimum cost solution for Lifezest manufacturer.

Provide as well the optimal solution found, which must include the amount of each chemical produced in

each of the plants and how much it is transported between any pair of entities in the network.

[10 marks]

ii) Consider the scenarion in which additional capacity can be leased at any of the plants at the cost

of $35 per litre per month. For example, the capacity of Newham can be increased to 30,001 if we pay

an extra $35 per month and the same for all the other plants. Construct another ILP model taking in

account this extra flexibility. Include the optimal solution, the amount of each chemical produced in each

of the plants, and report how much it is transported between any pair of entities in the network.

[10 marks]

3

QUESTION 2A - Deterministic Inventory Models: In-Light

Part 1 - Problem Description

In-Light is a lighting company that has contracted you to manage the inventory of an LED strip light.

In-Light sells a remarkably steady 700 units a year, each light costs $12 to buy from the wholesaler and

their management team estimates that each light has an associated annual holding cost of 20%. The

wholesaler contracts a transportation company who charges a fixed amount of $82 per delivery. Given

that the wholesaler makes the lights to order, each delivery takes exactly 2 weeks to arrive.

Questions

1. Plot the inventory level over a year for an optimal solution of the standard EOQ model. Make sure

to include the reorder point and label all relevant features describing the evolution of this periodic

function.

[4 marks]

Part 2 - Problem Description

Suppose that In-Light considers setting up their own manufacturing process for LED strip lights. They

estimate that their in-house process can produce up to 1,200 units a year. As a consequence of the

manufacturing process, warehouse space is predicted to become more valuable, with the effect of increasing

the holding costs to 35% per unit. Rather than purchasing lights, each light is estimated to cost $9 to

manufacture. The transportation cost is also replaced by a fixed cost of running each batch, estimated

to be $15.

Questions

1. Plot the inventory level over a year of the optimal solution of the Economic Production Lot Size

model. Include all relevant features.

[4 marks]

2. It is estimated to cost $1,500 to complete the transition. Is switching to in-house manufacturing

profitable? Justify your answer and discuss relevance of the financial planning horizon.

[1 mark]

4

QUESTION 2B - Deterministic Inventory Models: EOQ with

planned lost sales

Problem Description

Consider the EOQ model variation in which we are allowed to stock out. Now, consider a similar situation

where rather than excess demand being placed on back-orders, it is simply lost. Let x be the fraction of

demand that is lost per cycle. In addition to losing money through lost sales, assume that also for every

lost sale, a penalty cost of p is also acquired. Let c be the buying price of one unit. Notation from the

standard EOQ model should be used for the remaining variables and parameters.

Questions

1. Explain why the length of time with positive stock is Q

D

and the length of time with 0 stock is

Q

D

x

1?x

[2 marks]

2. Derive an expression for the total cost per year, f, as a function of Q and x, i.e. f(Q, x) = ? Make

sure to justify your choice.

[6 marks]

3. Show that Q? =

q2DC0

Ch

in this context.

[2 marks]

4. Show that ?f

?x = D(p ? c) ?

√

2DChC0. Explain why f is a linear function of x.

[2 marks]

5. For which value(s) of x gives us an optimal solution if:

(a) D(p ? c) >

√

2DChC0

(b) D(p ? c) <

√

2DChC0

(c) D(p ? c) = √

2DChC0

Explain these results mathematically and interpret these results.

[4 marks]

Hints: When determining an optimal order quantity with the standard EOQ model, we do not need to

consider the product cost, c, as we will always order D units a year. Now, since some demands are lost, the total

cost will depend on c.

5

QUESTION 3 - Revenue Management

Problem description

Consider a large retailer that has 5 products in a specific small category. After several discussions

with the suppliers, and meetings at the marketing department the firm decides to standardize prices

among their different retail branches. Along the past year, each product has been sold at three different

prices depending on the specific period and specific branch. Table 1 shows the three prices for each

product.

Table 2 shows the per unit cost for the retailer. The profit per product sold is the price at which

the item is sold minus its cost.

Price level/Product A B C D E

1 $23 $24 $22 $33 $28

2 $26 $29 $24 $43 $34

3 $29 $37 $32 $51 $37

Table 1: Price per product {A, B, C, D, E} and price level {1, 2, 3}.

Product A B C D E

Wholesale price $10 $11 $9 $14 $12

Table 2: Costs per unit of product.

Historical data

Historical data of the monthly aggregate sales from different branches selling different assortments is

available in the Excel file historical-data-2020.csv. In order to simplify this problem, we assume that the

number of customers that arrive to each of the branches is the same. Without loss of generality will be

assume that the total number of potential buyers per month is 1 (i.e. this is just a scaling). Thus, if 0.2

consumers bought product A at a price of 26, it means that 20 percent of the buyers chose that particular

product at that price.

Each entry of the file shows the assortment of products displayed (a 1 denotes that product was

displayed and 0 otherwise). Next, follows the percentage of customers that bought each of the items

displayed for that specific assortment. Note that the sum of the percentage of consumers that bought a

product in the assortment is not necessarily 100%. This reflects the fact that some customers may decide

not to purchase any of the products that were available (for example they may decide to buy from a

competitor because the prices are too high for them). A hypothetical example of such an entry is shown

in Table 3. As there can’t be more than one price for each product, observe that at each entry of the file,

there is at most one price level per product that has a value 1 whereas all the other classes have a value

of zero.

Consumer behavior

Each consumers that arrives to the store is interested in buying at most one product. Think for example

as an arriving consumer who is interested in purchasing a mobile phone, a flight ticket, a soft drink, or a

snack. The demand of any product depends on the complete set of offered products since customers potentially

make substitutions to an available product if their most preferred product is not available. Such

6

Product A1 A2 A3 B1 B2 B3 C1 C2 C3 D1 D2 D3 E1 E2 E3

Assortment 1 0 0 1 0 0 0 1 0 1 0 0 0 0 0

Percentage 0.45 0 0 0.13 0 0 0 0.045 0 0.36 0 0 0 0 0

Table 3: Hypothetical example of historical data. For assortment {A1, B1, C2, D1} of products displayed,

the percentage of customers that bought each product displayed is 45%, 13%, 4.5% and 36%

respectively.

substitution behavior is captured by a customer choice model that can be thought of as a heterogeneous

distribution over preference lists (or permutations of products). We explain:

Consumers can be partitioned into customer types. Each customer type has associated a particular

preference list and will purchase the most preferable product that is available. If none of her preferable

products are available, the consumers of that specific type will buy nothing. For illustration, consider

a potential consumer named Cecilia who belongs to a consumer type that is associated to the following

preference list: [D1, A1, C1, D2, A2, B1, B2, C2]. For Cecilia, as for any other consumer that belongs to

this customer type, her most preferred product is D1, if that product is not available he will buy A1 if

available, otherwise if product A1 is not available she will buy the first available product in her preference

list. If none of her preferred products are available, that is if the products displayed are contained in

{A3, B3, C3, D3, E1, E2, E3} , Cecilia will leave the store without buying any product.

Notice that Cecilia is rational and therefore, faced at the decision of buying the same product at

different prices, she will prefer the cheapest price. For example, for product A, Cecilia prefers A1 over

A2 and the same for all products and their price points. For simplification, we assume all consumers are

rational and that the distribution of consumer preferences is the same among all branches.

A market research study has found that there are between 40 to 50 different customer types in this

market. Moreover, 20 percent of the customer types have a preference list that has a length of at most

3.

Questions

Write a self-contained report with elaborate answers to the following questions. Max: 3 written pages

(plus the prediction results in a file).

1. Propose a discrete choice model for consumer behavior. Explain the reasons you selected that model

and not others.

2. Estimate the parameters of your proposed model with the historical data, i.e., calibrate your model.

Explain the method or methods you used.

3. In order to test the accuracy of your prediction model, you will have to give the expected percentage

of customers that buy each of the products for the assortments that appear in the file

solution2020.csv (Note: the quality of your predictions will be quantified as the sum of the squared

differences between your predictions and the real observations (Refer to the next section to know

more about the quality of the predictions).

In order to compute the quality of your solutions automatically, enter your results in the same file

solution2020.csv following the exact format of the file. Please rename the file adding your group

number. For example: solution2019group-i where i is your group number. Please do not change

anything of the file solution2020.csv, except the changing the zeros with your market share predictions

(each market share should be between 0 and 1, example: 0.024). Failure to abide to this rules

will result in a point penalization.

4. The retailer has to choose which elements to display for sale and what price each of the products

displayed will have. Based on your model, what assortment of products and prices according to

7

table 1 should the retailer choose to maximize the expected profit? Explain the method by

which you select the assortment.

5. Suppose you find a model that can fit perfectly the data of historical-data-2020.csv. Does this

guarantee that you can find the optimal assortment? If yes explain why. If not, explain whether

you think there is a correlation between market share predictions and revenue.

Quality of Prediction

The quality of your predictions will be quantified as the sum of the squared difference between your

predictions and the real observations. For example, let us consider the assortment in Table 3, that is

assortment {A1, B1, C2, D1} and that your model predicts that a, b, c, d percentage of customers will buy

product A1, B1, C2, D1 respectively. Based on the real observations on 3, the quality of your prediction

for assortment {A1, B1, C2, D1} is quantified as follows:

(a ? 0.45)2 + (b ? 0.13)2 + (c ? 0.045)2 + (d ? 0.36)2

.

That corresponds to the quality of the prediction of one assortment. In order to assess many assortments,

we take the sum of the qualities over all assortments.

8

QUESTION 4 - Inventory Management with Stochastic Demand

The standard newsvendor model assumes that the decision maker knows the probability distribution of

the demand. This is sometimes unrealistic, particularly when there is not much historical data. A much

weaker requirement is to assume that the decision maker only knows the mean and the standard deviation

of the demand distribution (but not the whole demand distribution function).

Imagine a scenario in which the decision maker knows that the demand distribution for a specific

product has a mean of 10,000 and a standard deviation of 2,000. The product selling price (to consumers)

is set to AUD 10. Additionally, assume that there is no salvage value.

Questions

i) If the decision maker mistakenly assumes that demand follows a Normal distribution but the demand

distribution is NOT Normal, the ”optimal” quantity calculated using a Normal distribution may not be

in fact optimal. Provide an example where revenue gap between the optimal solution and the non-optimal

solution calculated by the decision maker assuming a Normal distribution is at least 3 percent.

For this question you must assume that the per unit buying price for the retailer (per unit cost)

is $1. You will need to explicitly show a demand distribution with a mean of 10,000 and a standard

deviation of 2,000 units.

[4 marks]

ii) Now suppose that the demand distribution is either: (1) Symmetric triangular (i.e. with b-a =

c-b); (2) Poisson; (3) Normal. Plot a table varying the buying price (per unit cost) from 0.5 to 9.5 in

50 cents increments (19 scenarios). For each scenario, evaluate the optimal order quantities the decision

maker would choose if it knew the distribution was (1), (2) or (3). Perform a qualitative analysis of what

you observed and provide a high level explanation of the results. For which buying prices do the optimal

quantities from the different distribution assumptions differ the most?

[4 marks]

(iii) Suppose you don’t know the true distribution of demand, but you assume there is 10% chance

it is symmetric triangular; 40% it is Poisson; and; 50% it is Normal. Propose a method to choose the

order quantity and justify it. Additionally, describe at least 2 different objectives that would make sense

to assess the performance of the method.

[4 marks]

(iv) In 1957, Herbert E. Scarf solved a newsvendor problem which originates from some of the ideas

discussed above. Explain in at most one page (and in your own words) what is the exact problem

Herbert E. Scarf studied and its solution. Note: You don’t need to understand the proof of this solution.

You are only asked to understand well the problem statement and what is the solution.

Relevant References

? Herbert E. Scarf, (2002) Inventory Theory. Operations Research 50(1):186-191.

? Scarf, Herbert E., A Min-Max Solution of an Inventory Problem. Santa Monica, CA: RAND

Corporation, 1957.

[8 marks]

9

QUESTION 5 - Inventory Management with Stochastic Demand

Note: The use of Monte Carlo simulation would be helpful for this problem.

United Global Imports (UGI) is a distributor of frozen fish. They distribute 4 kinds of fish: Tuna,

Swordfish, Salmon and Barramundi. On any given day, there is a probability pi that an order will be

placed with UGI for a specific fish, i. If an order is placed, the size of the order will follow either a normal

distribution, or a log normal distribution. Parameters for either distribution are currently unknown. The

daily sales for these fish over the past year are presented in the data file ”UGI fish imports.xlsx”.

Table 4 provides additional information regarding the costs associated with each fish. The holding cost

per year, is calculated to be a percentage of the buying price.

UGI is considering adopting either a continuous or periodic review policy to manage it’s inventory. As

you could imagine, UGI wants to maximise expected profit. Assume that demands will follow the same

distributions this coming year. Every fish that goes on back-order incurs a penalty cost of 80% of its

original buying price. This value is set at this price so that it accounts for the associated goodwill costs

too.

Assume for simplicity that the demand for each fish:

1. Follows the same distribution (i.e. log normal or normal).

2. Is the same across the entire year.

3. Is independent of each other.

Assumptions 2 and 3 apply to the pi

’s too.

Fish Lead Time

(days)

Holding

Costs (%)

Buying

Price

($/Kg)

Selling

Price

($/Kg)

Initial Inventory

(Kg)

Order Cost

($)

Tuna 11 32 22.00 42.50 3850 1050

Salmon 10 36 12.90 26.50 1280 240

Swordfish 12 31 14.50 29.00 240 42

Barramundi 8 32 21.10 38.90 1980 250

Table 4: Additional product data

1. Questions

(a) For each type of Fish, and for each distribution, determine the following:

i. The mean for the daily demand distribution (approximately) (in case an order is placed).

ii. The standard deviation for the daily demand distribution (approximately) (again, only in

case an order is placed).

iii. The expected proportion of days that the customer purchases the product.

iv. The expected demand during the lead time (approximately).

v. The standard deviation of the demand during the lead time (approximately).

[4 marks]

(b) With reference to the actual distributions, discuss the suitability of using either the log-normal

10

or the normal distribution for modelling demand. Which distribution is a better fit? (Hint:

use histograms of the actual demand with reasonable interval sizes and compare with the

distributions you produce. Alternatively, use statistical tests).

[3 marks]

Based on your chosen distribution in question 2, complete the following analysis of the

both the periodic review and continuous review using the same distribution.

2. Periodic review

(a) The first alternative UGI is considering is a Periodic Review policy to replenish stock. Using

a 1 month review period, compute the following for each type of fish:

i. The approximate optimal order up-to point value M

ii. The approximate expected annual profit and its standard deviation

iii. The approximate expected number of fish (in Kg) placed on back order (as a proportion

of total expected sales)

Please explain your procedure to respond to all the questions above. Specifically, explain with

details how you constructed the Monte Carlo simulation.

[6 marks]

3. Continuous Review

(a) Alternatively, management is considering a Continuous Review policy as they believe it will allow

them to respond better to changing stock levels. Use Monte Carlo simulation to determine

for each product:

i. The approximate optimal order quantity

ii. The approximate optimal reorder point (in terms of both inventory position and inventory

on hand)

iii. The approximate safety stock

iv. The approximate expected annual profit and its standard deviation

v. The approximate expected number of fish (in Kg) placed on back order (as a proportion

of total expected sales)

Again, please explain your procedure to respond to all questions above.

[6 marks]

4. Final Conclusion

(a) Assuming that a continuous review policy would incur a 2% decrease in total expected profits

(due to labour costs of constantly checking inventory levels), which of the two policies would

you recommend (continuous or periodic), based on total expected profits?

[1 mark]

11

QUESTION 6 - Assignment Model for electronics supplier

Company A is a delivery company which deals in electronics. It has ties with 10 big firms which place

their orders in bulk at the start of each quarter. The company has 5 warehouses where the electronics

are kept and then delivered to these firms.

In the provided excel file (Question6.xlsx), you will find the location (coordinates) of all the firms

as well as the potential warehouses that the company can use. Using any given warehouse incurs a cost

of $6 per kilometre for a delivery for each customer (to be considered only for one way). You have been

hired by the company to decide which warehouses to use for which firm. Following are the questions you

need to address:

Questions

Write a self-contained report with separate answers for each of the following sub-problems. Each answer

shall outline the decision variables, objective function and constraints for the ILP and the

computed optimal values for the objective function and decision variables. Do not simply state the

mathematical formulation of the ILP on its own, but also briefly include in words what the objective functions

and different constraints mean. In an appendix, include the file containing the code/spreadsheets

implementing the ILPs developed in the report. A spreadsheet package such as Excel should suffice.

1. The distributor has told you to measure distances using the 2-norm (that is, use Euclidean distances)

and that each warehouse can only cover 35 kilometres in either direction. In addition, each firm can

only be assigned to one warehouse. Design an ILP model and solve it to find the optimal strategy to

be used by the company. Specify which warehouses should be used for which firm and the optimal

cost. All demand must be satisfied.

2. Suppose now that your budget is limited to $800 per quarter. What is the maximal coverage you

can achieve for that amount of money in terms of number of firms?

3. Evaluate and plot a graph depicting the relationship between the maximum budget available and

the maximum coverage. What can be inferred from the graph?

[10+5+5=20 marks]

12