Business Decisions modelling Assignment Sample

Introduction to Business Decisions modelling through linear programming and a solver

Task 1:

Explanation and interpretation of linear programming

a) Definition of the notation used in the formula

The notation used in the formulations are as follows,

The objective function, or Z, stands for the firm's overall contribution margin. It is calculated by multiplying the total contribution margin by the quantity of units produced for each product.
The choice variables, denoted by x1, x2, and x3, stand for the quantity of product 1, product 2, and product 3 units, respectively.

These numbers are not negative. The disparities that restrict the machines' availability are known as the limitations (Friston et al., 2020). They make sure that each machine's available machine time is not exceeded by the overall amount of time that the items need (Mayer, 2021). The constraints known as coefficients represent how much time each product needs to run on a given system as well as how much of a contribution each product makes (Osaba et al., 2021). The problem statement contains them.

b) Identification of the decision variables

The unknown values that we need to find in order to maximize the objective function are the choice variables. The number of units of product 1, product 2, and product 3 are represented by the choice variables in this issue, which are x1, x2, and x3. Since they are noni-negative integers, they are not able to be fractional or negative.

c) Explanation of the constraints

The disparities that restrict the machines' availability are known as the limitations. They make sure that each machine's available machine time is not exceeded by the overall amount of time that the items need (Suwannahong et al., 2021). For instance, the first limitation states that no more than ninety hours can be spent by products one, two, and three combined on machine 1. For the other two limitations, the same reasoning holds true (Díaz et al., 2020).

d) Presentation of the formulation in Standard normal form

The formulation in Standard Normal Form is as follows:

Maximize Z = 30x1 + 40x2 + 35x3 subject to: 3x1 +4x2 +2x3 + s1 = 90 2x1 +1x2 +2x3 + s2 = 54 1x1 +3x2 +2x3 + s3 = 93 with x1, x2, x3, s1, s2, s3 ≥ 0

Where s1, s2, and s3 are the slack variables that measure the unused machine time on each machine (Bayatvarkeshi et al., 2021). It was added to the left-hand side of the inequalities to make them equalities. They are also non-negative integers.

Task 2

Input the formulation into Solver to determine the optimal solution of the firm's production problem.

Table 1: Input for the formulation to get an optimal solution for the firm's production problem

(Source: Self-created in MS EXCEL)

There are a few procedures that must be followed in order to enter the formulation into Solver and find the best answer for the manufacturing challenge facing the company.
Establish the decision factors first (Bermúdez et al., 2020). At initial stage for the number of units for Product 1, 2 and 3 it was provided the input of 1,2,1 number of units respectively. It was inputted for checking the function of excel solver.

The amounts of each product to be produced in this instance are indicated by the symbols x1, x2, and x3, which stand for Product 1, Product 2, and Product 3, respectively.
Establish the goal function, which is to maximize overall profit, second. This might be expressed,
Total Profit of 145 = 30x1 + 40x2 + 35x3

Next, establish the limitations according to each machine's production capacity and available resources. The following are the limitations:

• Machine A: 3x1 + 4x2 + 2x3 ≤ 90
• Machine B: 2x1 + x2 + 2x3 ≤ 54
• Machine C: x1 + 3x2 + 2x3 ≤ 93

Additionally, ensure that the production quantities cannot be negative:

• X1, x2, x3 ≥ 0

Enter the objective function and constraints into the Solver tool once they have been determined. Determine the goal function that will maximise overall profit while adhering to the specified restrictions (Outram et al., 2021). Next, the solver will figure out how much of each product to produce in order to maximize profit while still meeting the machine's capacity and resource limitations. With respect to the company's production capacity and available resources, this ideal solution offers insights into the most profitable production plan.

Extract the optimal solution from Solver, including the value of the objective function and the production schedule for the firm

Table 2: Used solver to get optimal solution for firm's production problem

(Source: Self-created in MS EXCEL)

Subject to the limits of machine hours available, the Solver's beast solution is the one that maximizes the objective function, or total profit. The total profit per unit multiplied by the quantity of each product's units determines the value of the objective function (Briones et al., 2020). The quantity of units to be produced for each product is specified in the company's production schedule.

Producing 0 units of product 1, 12 units of product 2, and 21 units of product 3 is the beast course of action in this scenario. Doing so will provide a maximum profit of £1215. The spreadsheet illustrates this: row 4 shows the quantity of units, and column E5 shows the total profit. The Silver Results dialog box, which displays the ideal values of the choice variables (x1, x2, and x3), also displays the production schedule (Minsan, 2021).

Producing 0 units of product 1, 12 units of product 2, and 21 units of product 3 is the best course of action in this scenario. Doing so will provide a maximum profit of £1215. The spreadsheet illustrates this: row 4 shows the quantity of units, and column E5 shows the total profit. The Solver Results dialog box, which displays the ideal values of the choice variables (x1, x2, and x3), also displays the production schedule (Mohamad et al., 2021).

Task 3

Other optimal production schedules for the firm

Indeed, alternative manufacturing plans that provide the same maximum profit of £1215 for the company exist. This is due to the fact that there is a no redundant critical constraint parallel to the objective function in the case of an alternate optimum solution to the issue (Šedivýx et al., 2020). The machine C constraint in this instance, which binds at the best solution, is the crucial constraint.

Using the sensitivity analysis report that Solver created, we can determine the other ideal production schedules. The permitted increase and decrease values for each constraint are displayed in this report. These numbers demonstrate how much each constraint's right-hand side can vary without compromising the best possible outcome. For instance, the machine C restriction allows for a maximum rise of 15 and a maximum drop of 0, meaning that machine C's available hours can either grow or remain the same, but they cannot decrease in any way.

We can determine the range of values for the choice variables that uphold the ideal solution using this knowledge. For instance, the following restriction would arise if we increased machine C's available hours by 15:

• 1x 1 + 3x 2 + 2x 3 ≤ 108
• Substituting the optimal values of x 1 and x 2 into this constraint, we get:
• 0 + 36 + 2x 3 ≤ 108
• Solving for x 3 , we get:
• X 3 ≤ 36

This implies that any value between 15 and 36 for x 3 will still result in the best answer. In a similar manner, by adjusting the other constraints within their permitted bounds, we may determine the range of possibilities for x 1 and x 2.
If the criteria are met, any combination of numbers falling inside these ranges will yield an ideal production plan for the company. As an illustration, a potential manufacturing schedule is:

• X 1 = 6, x 2 = 12, x 3 = 18

This plain will likewise provide a maximum profit of £1215 and utilize all of machine A's and machine B's available hours, leaving machine C with 12 hours of wasted time.

Opportunity cost associated with product 1 and interpretation to the opportunity cost

The lower cost of the relevant choice variable, in this case x1, is the opportunity cost related to product 1. The decreased cost is the amount that would need to happen before x1 could take on a positive value in the optimal solution that is, before the objective function coefficient of x1 could improve, or rise in the cause of a maximizing issue (Owczarek and Olszewska, 2020). Stated differently, the marginal cost of manufacturing one additional unit of product 1 or the cost of slightly raising x1 is the decreased cost.

The opportunity cost may be seen as the amount of money the company loses by not creating any units of product 1. It also illustrates the amount that product 1's earnings per unit would need to rise before the company would think about manufacturing it. Since the lowered cost of x1 in this example is -6, the company would need to improve its profit per unit of product 1 by 6 (from 30 to 36) before it could begin producing any units of that product (Kuneva et al., 2021). Alternatively said, it indicates that for every unit of product 1 that the company does not manufacture, it loses six units of profit.

Contribution margin for product 1 increased from £30 to £43.

Table 3: Contribution margin changed

(Source: Self-created in MS EXCEL)

Yes, the ideal plan would probably alter if Product 1's contribution margin was raised from £30 to £43. The economics of producing Product 1 in comparison to other goods would be impacted by this modification. Product 1 is now more profitable than the other goods due to its higher contribution margin of £43. In order to preserve resource restrictions, this modification may cause a shift in the ideal production plan, which might lead to an increase in the production of Product 1 and a decrease in the production of other products.

Owing to Product 1's larger contribution margin, the optimization algorithm would reevaluate each product's contribution to the overall profit and modify the production schedule appropriately. The precise modifications to the ideal plan, however, would rely on the particular limitations and the degree of the contribution margin increase.

Change of contribution margin for product 2

Prior to the present optimal solution being suboptimal, the contribution margin for product 2 may vary by as much as £3.33. This represents the machine's hidden cost. The strongest restriction on product 2 is a constraint. The difference between the total profit and the number of machine hours available is showing by the shadow pricing.

Task 4

Shadow prices in this problem and interpretation

The amount by which the overall profit would rise or fall, respectively, if each machine's available hours were to grow or decrease by one unit, is what the term "shadow prices" refers to. For instance, the machine's shadow price of 0.727273 Due to a limitation, the total profit would rise by 0.727273 units (from 1215 to 1215.727273) if machine A's available hours rose by one unit (from 90 to 91).

As long as the binding restrictions and the ideal solution remain unchanged, the shadow pricing are valid. The degree to which the right-hand side of each constraint can vary without impairing the ideal solution is determined by the permitted increase and decrease values for each constraint. Solver has prepared a sensitivity analysis report that contains these values. For instance, the machine a restriction allows for a maximum rise of 6 and a maximum drop of 3, meaning that machine A's available hours can fluctuate between 87 and 96 without afflicting the shadow price or thief best solution. Nevertheless, the best course of action and the shadow price might alter if machine A's available hours fluctuate outside of this range.

Using the shadow price associated with Machine 1 the impact of in- creasing the available machine time by 1 hour

Table 4: Solver model and sensitivity report for changing machinery time by 1 hour

(Source: Self-created in MS EXCEL)

The effect of a one-unit increase in available machine time on profit is shown by Machine A's shadow pricing. The shadow price for Machine A in this instance is around £0.73 per hour.
For Machine A, an increase in available machine time of one hour would translate into a profit rise equal to the shadow price, or £0.73.

This implies that spending an extra hour on Machine A would add around £0.73 to the overall profit. Consequently, Machine A's profit would increase by around £0.73 if its available machine time were extended by one hour.

An increase of one hour in the available machine time would result in a 0.727273 unit increase in the overall profit, based on the shadow price of Machine 1. Multiplying the shadow price of 0.727273 by the shift in the Machine 1 constraint's right-hand side, which is 1, will show this. To retain the feasibility of the restrictions, the best solution modifies somewhat when x3 grows by 0.727273 units and x2 and x3 drop by 0.227273 units each. The rise in overall profit is 0.727273 units, which is equivalent to Machine 1's shadow price.

Marginal value of a new product

Table 5: Solver model new product

(Source: Self-created in MS EXCEL)

The idea of opportunity cost, or the worth of the best option given up as a result of a choice, must be used in order to respond to this query. The profit that is lost by lowering the production of the current items is, in this instance, the opportunity cost of creating one unit of the new product. The shadow pricing of the limitations, which show how much the overall profit would change if the available hours for each machine rose or reduced by one unit, respectively, may be used to compute the opportunity cost.

The shadow prices of the constraints are:

• 0.727273 For the machine a constraint
• 0.018182 For the machine B constraint
• 0 for the machine C constraint

The total of the shadow prices multiplied by the machine hours needed to produce one unit of the new product is the opportunity cost, which equals:

(0.727273 x 4) + (0.018182 x 2) + (0 x 3) = 2.927274

This indicates that the overall profit will be decreased by 2.927274 units when one unit of the new product is produced. Consequently, only if the new product's contribution margin exceeds its opportunity cost of 2.927274 should it be manufactured. In this instance, the opportunity cost is less than the new product's contribution margin of 55. Thus, it is necessary to create the new product.
The difference between the new product's opportunity cost and contribution margin, or the marginal value of manufacturing one unit, is:

• 55 - 2.927274 = 52.072726

This means that producing one unit of the new product will increase the total profit by 52.072726 units.

References

Journals

• Bayatvarkeshi, M., Imteaz, M.A., Kisi, O., Zarei, M. and Yaseen, Z.M., 2021. Application of M5 model tree optimized with Excel Solver Platform for water quality parameter estimation. Environmental Science and Pollution Research, 28, pp.7347-7364.
• Bermúdez, M., Puertas, J. and Cea, L., 2020. Introducing Excel spreadsheet calculations and numerical simulations with professional software into an undergraduate hydraulic engineering course. Computer Applications in Engineering Education, 28(1), pp.193-206.
• Briones, L., Morales, V., Iglesias, J., Morales, G. and Escola, J.M., 2020. Application of the microsoft excel solver tool in the optimization of distillation sequences problems. Computer Applications in Engineering Education, 28(2), pp.304-313.
• Díaz de los Ríos, M. and Hernández Ramos, E., 2020. Determination of the Hansen solubility parameters and the Hansen sphere radius with the aid of the solver add-in of Microsoft Excel. SN Applied Sciences, 2, pp.1-7.
• Friston, K.J., Wiese, W. and Hobson, J.A., 2020. Sentience and the origins of consciousness: From Cartesian duality to Markovian monism. Entropy, 22(5), p.516.
• Kuneva, V., Milev, M. and Gocheva, M., 2021, March. Modeling the transportation assesment with MS excel solver. In AIP Conference Proceedings (Vol. 2333, No. 1, p. 150005). AIP Publishing LLC.
• Mayer, C., 2021. The future of the corporation and the economics of purpose. Journal of Management Studies, 58(3), pp.887-901.
• Minsan, P., 2021. Comparing Methods of Optimization in Solver of Microsoft Excel 2007 and 2019: A Case Study of Statistical Models. ???????????????????????????????????, 31(3), pp.496-511.
• Mohamad, M.A., Putra, Z.A., Bilad, M.R., Nordin, N.A.H.M. and Wirzal, M.D.H., 2021. An excel based tool development for scheduling optimization. ASEAN Journal of Science and Engineering Education, 1(1), pp.7-14.
• Osaba, E., Villar-Rodriguez, E., Del Ser, J., Nebro, A.J., Molina, D., LaTorre, A., Suganthan, P.N., Coello, C.A.C. and Herrera, F., 2021. A tutorial on the design, experimentation and application of metaheuristic algorithms to real-world optimization problems. Swarm and Evolutionary Computation, 64, p.100888.
• Outram, J.G., Couperthwaite, S.J., Martens, W. and Millar, G.J., 2021. Application of non-linear regression analysis and statistical testing to equilibrium isotherms: Building an Excel template and interpretation. Separation and Purification Technology, 258, p.118005.
• Owczarek, A. and Olszewska, M.A., 2020. Development and validation of UHPLC-PDA method for simultaneous determination of bioactive polyphenols of horse-chestnut bark using numerical optimization with MS Excel Solver. Journal of Pharmaceutical and Biomedical Analysis, 190, p.113544.
• Šedivý, J., ?ejka, J. and Guchenko, M., 2020. Possible application of solver optimization module for solving single-circuit transport problems. LOGI–Scientific Journal on Transport and Logistics, volume 11, issue: 1.
• Suwannahong, K., Wongcharee, S., Kreetachart, T., Sirilamduan, C., Rioyo, J. and Wongphat, A., 2021. Evaluation of the Microsoft Excel Solver Spreadsheet-Based Program for nonlinear expressions of adsorption Isotherm models onto magnetic nanosorbent. Applied Sciences, 11(16), p.7432.