Revenue optimization problem - Fashion retailer session

Solution Preview

---

Hi Please find the attached Excel. I have used excel solver and simulation for revenue maximization problem. Also about the airline problem there is a tweak to the normal process. The demand under discount and under full fare follows different distributions. Hence I have used the seat matrix to calculate the critical ratio. We can also used simulation or other approaches but NEWS VENDOR method is often used to solve such problems.

Problem 1:

This is a revenue optimization problem, where we are trying to maximize the revenues from a fashion retail item over a period of 15 weeks. The demand for the item follows two distinct demand curves:
 Early session demand (week 1 to 4): d1(p) = *(3500-10p), where is an unknown parameter that ranges uniformly in the interval [0.6, 1.4]
 Late session demand (week 5 to 15): d2(p) = *(3000-25p)

a) In this part, we assume that the demand is deterministic where the unknown scale parameter is 1.

This is an optimization problem, solved using Excel solver. The decision variables of the problem are:
 Price during early session demand P1
 Price during late session demand P2

The demand during the two sessions is calculated using the following equations:
D1 = 3500-10P1
D2=3000-25P2

Since, total number of units available for sale is 2000 only and the salvage value at the end of the horizon period for an unsold item is $40 per unit, the objective function for the maximization problem is defined as below:
Maximize Total Revenue
TR = P1*D1 for D1>2000
P1*D1+P2*D2 for D1 < 2000 and D1+D2>=2000
P1*D1+P2*D2+(2000-D1-D2)*40 for D1+D2<2000

We use the Excel solver to solve the maximization problem by maximizing the total revenues by changing the variables P1 and P2.

Problem 1:

This is a revenue optimization problem, where we are trying to maximize the revenues from a fashion retail item over a period of 15 weeks. The demand for the item follows two distinct demand curves:
 Early session demand (week 1 to 4): d1(p) = *(3500-10p), where is an unknown parameter that ranges uniformly in the interval [0.6, 1.4]
 Late session demand (week 5 to 15): d2(p) = *(3000-25p)

a) In this part, we assume that the demand is deterministic where the unknown scale parameter is 1.

This is an optimization problem, solved using Excel solver. The decision variables of the problem are:
 Price during early session demand P1
 Price during late session demand P2

The demand during the two sessions is calculated using the following equations:
D1 = 3500-10P1
D2=3000-25P2

Since, total number of units available for sale is 2000 only and the salvage value at the end of the horizon period for an unsold item is $40 per unit, the objective function for the maximization problem is defined as below:
Maximize Total Revenue
TR = P1*D1 for D1>2000
P1*D1+P2*D2 for D1 < 2000 and D1+D2>=2000
P1*D1+P2*D2+(2000-D1-D2)*40 for D1+D2<2000

We use the Excel solver to solve the maximization problem by maximizing the total revenues by changing the variables P1 and P2.

The solver output is reproduced here:
Microsoft Excel 14.0 Answer Report
Worksheet: [Revenue Management.xlsx]1a
Report Created: 1/12/2011 12:11:41 PM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: GRG Nonlinear
Solution Time: 0.016 Seconds.
Iterations: 3 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Convergence 0.0001, Population Size 100, Random Seed 0, Derivatives Forward, Require Bounds
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative

Objective Cell (Max)
Cell Name Original Value Final Value
$E$16 Maximize Total revenue $ 0 $ 351,607.14

Variable Cells
Cell Name Original Value Final Value Integer
$E$9 Price during early session demand (P1) $ 0 $ 210.71 Contin
$E$10 Price during late session demand (P2) $ 0 $ 95.71 Contin

Constraints
NONE

The maximum revenues are achieved when the prices are fixed as:
P1 = $210.71
P2=$95.71

The value of maximum revenues is $351,607.14

b) In this part, we relax the deterministic demand assumption for the session 1.
Thus,
 D1= *(3500-10*P1), where is an unknown parameter that ranges uniformly in the interval [0.6, 1.4]
 D2=3000-25*P2

Here, the demand for the first session is uncertain. Depending upon the uncertainty factor, the demand for the first session will have an impact of whether the inventory left at the end of the first session will be undersold or oversold. We are using different prices for selling the fashion items in two demand phases and hence using the two different demand functions with different slopes, the expected value of the revenues will not be same as the revenues obtained in part 1. Instead of solving this problem mathematically, the best way to develop the distribution for the revenues is to make use of the simulation.

I used 5000 iteration simulation to calculate the expected revenue when the early session demand is uncertain. The simulation is made operational as below:
• Generate 5000 random numbers using RAND() function in Excel. Values paste these numbers.
• Calculate the corresponding  value using the following formula:
o = RAND()*0.8+0.6
values varies uniformly between 0.6 to 1.4, hence the total range is 0.8. So, we multiply the random number generated in above step by 0.8 and then add 0.6 to obtain a uniform distribution which varies uniformly between 0.6 and 1.4.
• Next, we use the Data Table feature of Excel to calculate the revenues for the corresponding values.

The distribution of revenues under uncertainty is as below:

The descriptive statistics for the total revenue are shown in following table:

Mean $344,210.93
Median $351,819.54
Max $415,663.26
Min $256,532.49
Standard Deviation $ 46,217.29

Since we have higher revenue losses when the uncertainty is on the negative side (alpha factor lower than 1), the expected revenues under uncertainty are lower than that in case of deterministic demand. The revenues range between $256,532.49 to $415,663.26 with an ...