# OR_HW4

**Repository Path**: yangd_74/or_hw4

## Basic Information

- **Project Name**: OR_HW4
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2024-10-15
- **Last Updated**: 2024-10-15

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# LINEAR PROGRAMMING - HOMEWORK 4
阮黄阳 - 数学系 数31 - 2023080149
## A. PROBLEM
> A bicycle manufacturer is determining its production schedule for the next 3 months. Assume that it costs this company $\$150$ to manufacture each bicycle. At the end of each month, a holding cost of $25 per bicycle left in inventory is incurred. No more than 40 bicycles can be stored in inventory at any point in time. Monthly demands for bicycles are projected to be as follows: 150 in month 1; 175 in month 2; 165 in month  3.  Assume  that  at  the  beginning  of  month  1,  10  completed  bicycles  are  in  inventory. Finally, this company can produce up to 170 bicycles per month

>> a. Formulate  a  linear  programming   (LP)   model to   find   a   cost-minimizing production schedule that meets all demands on time. 

>> b. Model the problem in Excel and solve it using Excel Solver.

>> c. Create a Python package to model and solve the same problem using Gurobi, using  the  Excel  file  in  b)  as  the  input  data  file. Write  your  solution  to  a  newsheet  of  the  same  Excel  file  and  rename  the  sheet  as  “Solution_Gurobi.” Properly name your column headers and row labels (if any) and make sure that your solution is readable.

## B. MODELLING
### 0. Assumption
- (1) Assume that in the $t$ month, this company can sell product produced in the $t$ month. If not, there is no way to meet demands of the first month.
- (2) Since there is always a demand for new bicycles, it is assumed that bicycles can be sold immediately after production.
### 1. Data
|  Symbol  |                                    |Unit        |
|----        |-----                                      |-----       |
|$C$         |Manufacturing cost of each bicycle         |dollar/bike |
|$I_f$       |Inventory fee of each bicycle for a month  |dollar/month|
|$I_0$       |Inventory at the start of the first month  |bike/monnth |
|$I_q$       |Inventory quota                            |bike/month  | 
|$D_t$       |Demand for bicycles in the $t$ month       |bike/month  |
|$M_q$       |Manufacturing quota                        |bike/month  |


### 2. Decision Variables
|  Symbol  |                                    |Unit        |
|----        |-----                                      |-----       |
|$I_t$       |Inventory at the end of the $t$ month      |bike/monnth |
|$M_t$       |Manufacturing plan for the $t$ month       |bike/month  |
|$S_t$       |Number of bicycles sold in the $t$ month       |bike/month  |

### 3. Objective Funtion
We need to minimize cost containing producing and inventory, so that the objective function is
$$ 
\min Z = C\cdot\sum_{t=1,2,3}M_t + I_f\cdot\sum_{t=1,2,3}I_t
$$
### 4. Constraints
#### 4.1. Meet All Demands
The model is required to meet all demands at all time, which derived
$$
S_t = D_t\space,\space\space \forall\space t = 1,2,3
$$
#### 4.2. Monthly Manufacturing Quota
We know that "this company can produce up to 170 bicycles per month"
$$
M_t \leq 170 \space,\space\space \forall\space t = 1,2,3
$$
### 4.3. Monthly Inventory Quota
We know that "no more than 40 bicycles can be stored in inventory at any point in time", with assumption (2) it can be understood as "there is no more than 40 bicycles can be stored in inventory at the end of each month".
$$
I_t \leq 40 \space,\space\space \forall\space t = 1,2,3
$$
#### 4.4. Relationship Between $I_t$, $M_t$ and $S_t$
Obviously, we have 
$$
I_t = M_t + I_{t-1} - S_t \space,\space\space \forall\space t = 1,2,3
$$
Note: $I_0$ is inventory at the start of the first month, which is $10$.

### 5. Model
Combine all above sections, we have the required model
$$ 
\min Z = C\cdot\sum_{t=1,2,3}M_t + I_f\cdot\sum_{t=1,2,3}I_t 
$$

subject that

$$
\begin{cases}
S_t = D_t\space,\space\space &\forall\space t = 1,2,3\\
M_t \leq 170 \space,\space\space &\forall\space t = 1,2,3\\
I_t \leq 40 \space,\space\space &\forall\space t = 1,2,3\\
I_t = M_t + I_{t-1} - S_t \space,\space\space &\forall\space t = 1,2,3 \\
M_t, \space I_t \geq 0 \space,\space\space &\forall\space t = 1,2,3
\end{cases}
$$
Using given data, we have

$$ 
\min Z = 150 \cdot (M_1 + M_2 + M_3) + 25 \cdot (I_1 + I_2 + I_3)
$$

subject that

$$
\begin{cases}
M_1 &\leq 170 \\
M_2 &\leq 170 \\
M_3 &\leq 170 \\
I_1 &\leq 40 \\
I_2 &\leq 40 \\
I_3 &\leq 40 \\
I_1 &= M_1 + 10 - 150\\
I_2 &= M_2 + I_1 - 175\\
I_3 &= M_3 + I_2 - 160\\
M_1, \space M_2, \space M_3, \space I_1 , \space I_2, \space I_3 &\geq 0
\end{cases}
$$

## C. SOLUTION (by Excel Solver)
### 1. Input Model
![alt text](img/image.png)
### 2. Solver Setup
![alt text](img/image-1.png)
### 3. Solution
After using Excel Solver,
![alt text](img/image-2.png)

Optimized manufacturing plan,
![alt text](img/image-4.png)

## C. SOLUTION (by Gurobipy)
### 1. Run code on Terminal
![alt text](img/image-7.png)
### 2. Save in Excel
![alt text](img/image-3.png)