In-Depth Data Analysis-using Excel Solver to find the maximum profit of rubber toys

Background: A toy factory can produce two kinds of hot tub toys, namely, duck and fish, with limited raw materials and production time, how can we allocate the quantity of two types of toys to maximize the profits of the manufacturers.

The assumptions are as follows:

Product Name	Quantity
Duck	A
Fish	B
Product Name	Quantity of required materials per unit
Duck	100
Fish	125
Total quantity of required materials	100a + 125 * B
Quantity of stock materials	50000
Product Name	Unit profit
Duck	5 $
Fish	4 $
Total profit	A * 5 + B * 4

 

 

 

 

 

 

 

 

 

Figure 1

Analysis of table data: Assume that 100 pieces of rubber are required to produce a rubber duck, and 125 pieces of rubber are required to produce a total of 50000 pieces of rubber, A month can produce a maximum of 400 rubber ducks or 300 fish (PS: If a month produces 400 rubber ducks, there is no time to produce the rubber fish)

1: Find the decision variables, that is, the variables that can be left or right during data analysis.

Produce a, B

2: Find out the constraints, that is, the actual conditions

Time, total amount of raw materials available

3: Find the target, that is, the target we want to achieve

Get the maximum profit expression

-------------------------------------------------------------- How to use the Excel Solver to solve the problem? --------------------------------------------------

For example, enter the values of A and B as shown in figure 1, false A = 100, and B = 200.

B7 cell expression: = b2 * B5 + B3 * B6

Cell B12 expression: = b2 * B10 + B3 * B11

Next, edit the solver window.

Click solve. The result is as follows. The values in the table calculate the maximum value of the target expression according to the constraints.

PS: of course this is just an example. As a result, the Rubber Duck has already reached 400. I feel that the tool in the book is only an idealized value. In reality, it may be worth more details. This article focuses on tools.

 

In-Depth Data Analysis-using Excel Solver to find the maximum profit of rubber toys