Need proper solution with calculations

4. [MRP] A manufacturing company produces 2 different products A and B. Three distinct components, i.e., X,Y, and Z, are used in the production of these products. One unit of product A requires 2 units of X,2 units of Y, and 2 units of Z. One unit of product B requires 1 unit of X,3 units of Y, and 2 units of Z. Currently, the company has existing inventory of 0 unit of X,100 units of Y, and 0 units of Z. The company can purchase X at the price of $20 per unit but no more than 300 units due to the supplier's limited capacity constraint. The company can purchase extra units of Y at the price of $30 per unit. The company can purchase Z at the full price of $25 per unit for the first 50 units but the per unit price drops to $20 for any additional unit in excess.of 50 units due to the supplier's quantity discount offer. The market prices of the two products are $200 for A and $240 for B. The company knows that the demand for products A and B are equal to 100 units and 90 units, respectively. Therefore, the company should not produce more than the demand for each product. However, the company incurs a per unit penalty cost of $40 for any unsatisfied demand. The company needs to decide the component purchase plan and the production mix decisions to maximize its profits subject to all the business constraints described above. Formulate the company's problem as an optimization problem, i.e., providing the mathematical expressions for the decision variables, the objective function, and the constraints. No need to solve the problem by Solver. You can hand-write the math formulation, take a photo, b. Consider a retailer's inventory problem over multiple time periods. Assume the per-unit holding cost is $10 and the per-unit backlogging cost is $30. Demand in every time period is normally distributed with mean 100 and standard deviation 10. The beginning inventory of period 1 is equal to 100 . Assume every order placed at the end of each period will arrive at the beginning of the next period. Build the Excel-based Monte Carlo simulation spreadsheet to test the optimal inventory ordering policy that minimizes the total holding and backlogging costs in expectation. Please attach your Excel file in your email submission. |