Linear Programming Profit Maximization Model Using Excel

Linear Programming Profit Maximization Model Using Excel Student’s Name Prof Name Institution Introduction Overview This case study uses available data from a general business supply chain with an example illustrated in Picture 1 to define an optimization modeling and solve a problem for a fictitious company referred to as Supix. Though, the data and optimization modeling can be also used for real-life scenario. In this case the model use simulation and forecasting modeling in Excel to solve a supply chain problem. Figure 1: Sample Supply Chain Problem Description Supix company problem to establish the monthly optimal product supply chain plan of the two models of cars (S and LX), which the company will optimally distribute the existing capability to exploit the total revenue. Also, Sensitivity assessment to offer insights on the way total profit could be streamlined based on inputs modification such as, input cost and the retailing price. Hence, the main issue is to determine the optimal decision variables (production units for each car model), which can maximize our objective (total income) hence fulfil the resources constraints (manufacturing capability, available time/hours in stamping and forming the units), The overall profit is the total of each item profit that is less the fixed cost. The total cost comprises of fixed and variable costs. Whereas the variable costs comprise the direct materials, the workforce, and the operation costs per unit of manufacturing. Conversely, the fixed cost is monthly constant cost and not wide-ranging with the unit of manufacturing. Further, the fixed cost takes account of the cost for the stamping, forming, and production process. The contribution margin is the retail price less the variable cost. Therefore, the Supix’s solution is to design the optimal decision for the supply and manufacturing plan to maximize the income under the constraint of scarce resources. In this regard, the overall objectives are and the use the Excel Solver to integrate all the constraints to design a linear programming (LP) modelling to optimize resource allocation in a way that the total turnover is maximized. The specific objectives are; Find the profit for the current production plan (400 units of Model S and 1400 units of Model LX). To find the optimal production plan in order to stop the narrative of who is right or wrong and the