What Is Solver?Solver is a powerful tool in Excel that allows you to optimize a mathematical model. It can find ideal values for a set of variables while respecting imposed constraints, whether to maximize or minimize a result.Typical use examples:- Minimize production costs;- Maximize profits;- Find the ideal combination of products to be manufactured with limited resources.Example 1: Profit MaximizationProblem:A small furniture factory produces chairs and tables. Each chair generates a profit of R$ 30, while each table generates R$ 50. The factory has 100 hours of work available per week, with each chair consuming 10 hours and each table 20 hours. What combination of chairs and tables maximizes profit while respecting the hours limit?Step-by-Step in Excel:Problem setup:Decision variables: number of chairs and tables to produce.Objective function: maximize total profit.Constraints: do not exceed 100 hours of work.Create the model in Excel:In one cell, define the quantities of chairs and tables (e.g.: C2 for chairs and D2 for tables).In another cell, enter the total profit formula: =C230 + D250.In another cell, enter the total working time formula: =C210 + D220.Configure Solver:Go to the Data tab and click Solver.Under "Set Objective", select the cell that calculates profit.Check the Maximize option.Under "Variable Cells", select the cells for the quantities of chairs and tables.Under "Constraints", add that the total working time cannot exceed 100 hours. Also add that chairs, time, and tables must be greater than 0.Click Solve and see the results. Excel will show how many chairs and tables the factory should produce to maximize profit without exceeding the available time. Your screen should look like this at this point:Solver in example 1Example 2: Transportation Cost MinimizationA logistics company wants to minimize transportation costs from two warehouses (A1 and A2) to three stores (L1, L2, L3). Each warehouse has a limited product capacity, and each store has a specific demand. Transportation costs vary according to the route between each warehouse and store.The question is: how can the company minimize total transportation costs while meeting store demands and respecting warehouse capacities?Data:Warehouse capacity:Warehouse A1: 100 units.Warehouse A2: 150 units.Store demand:Store L1: 80 units.Store L2: 70 units.Store L3: 100 units.Transportation cost (R$/unit):L1L2L3A1468A2537The objective is to minimize transportation cost, ensuring that the quantity shipped from each warehouse does not exceed its capacity and that all stores receive exactly the quantity of products they need.Step-by-Step in Excel:In a spreadsheet, create a table with the transportation costs from each warehouse to each store.Add extra rows for warehouse capacities and store demands.Warehouse capacities:A1: 100 units.A2: 150 units.Store demands:L1: 80 units.L2: 70 units.L3: 100 units.Define the decision variables:Create cells to represent the quantities shipped from each warehouse to each store. You can organize it like this:L1L2L3A1Q1Q2Q3A2Q4Q5Q6Where:Q1 is the quantity shipped from A1 to L1;Q2 is the quantity shipped from A1 to L2;Q3 is the quantity shipped from A1 to L3;Q4 is the quantity shipped from A2 to L1;Q5 is the quantity shipped from A2 to L2;Q6 is the quantity shipped from A2 to L3.Create the objective function (Cost Minimization):In another cell, calculate the total transportation cost based on the quantities shipped and the costs per unit:Total cost = Q14 + Q26 + Q38 + Q45 + Q53 + Q67Add the constraints:Now, you need to add constraints to the problem. These constraints include:The quantities shipped from each warehouse cannot exceed their capacities.Total shipped from A1 = Q1 + Q2 + Q3 ≤ 100.Total shipped from A2 = Q4 + Q5 + Q6 ≤ 150.The stores need to receive exactly the quantity they demand.Total received by L1 = Q1 + Q4 = 80.Total received by L2 = Q2 + Q5 = 70.Total received by L3 = Q3 + Q6 = 100.Configure Solver:Go to the Data tab in Excel and click Solver.Objective: select the cell that contains the total transportation cost.Optimization type: choose Minimize, since we are trying to minimize the total cost.Decision variables: select the cells that represent the quantities shipped from each warehouse to each store (Q1 to Q6).Constraints: add the following:Quantity shipped from each warehouse cannot exceed its capacity.Each store must receive exactly the quantity it demands.Click Solve and Solver will find the combination of quantities that minimizes transportation cost while respecting all constraints. Your screen should look like this:Solver in example 2Exercises1) Production MaximizationYou manage a bakery that produces two types of bread: whole wheat and white. Each whole wheat bread generates R$ 2 in profit, and each white bread generates R$ 1.50. The bakery has 200 kilograms of flour available per day. Each whole wheat bread consumes 0.5 kg of flour, while each white bread consumes 0.25 kg. Use Solver to determine the ideal quantity of each type of bread to be produced to maximize profit.Objective function: maximize total profit.Constraints: do not use more than 200 kg of flour.2) Time MinimizationA company manufactures two types of products: A and B. Product A requires 5 hours of work and product B requires 3 hours. The company has 152 hours available and wants to work exactly this production time. How should Solver be configured to find the ideal combination of products, ensuring that at least 10 units of each are produced?Objective function: the total production time must be equal to 152.Constraints: at least 10 units of each product.3) Production Maximization with Fixed ProfitA company manufactures two products, A and B, and needs to obtain a fixed profit of R$ 4,000. Product A generates R$ 100 in profit per unit, while product B generates R$ 150 per unit. The available production time is 200 hours, with product A consuming 5 hours per unit and product B consuming 8 hours. Use Solver to determine how many units of each product should be manufactured to achieve the fixed profit of R$ 4,000 while minimizing production time.Objective function: minimize production time.Constraints:Total profit = R$ 4,000.Available production time = 200 hours.4) Resource AllocationA digital marketing company has three types of campaigns (C1, C2, and C3) that it wants to run on an ad platform. The company has a budget of R$ 10,000 and wants to maximize the return on investment (ROI). Campaign C1 has an ROI of 5%, campaign C2 has an ROI of 8%, and campaign C3 has an ROI of 10%. Each campaign has a minimum investment amount: C1 = R$ 2,000, C2 = R$ 3,000, and C3 = R$ 1,000. Use Solver to determine how to distribute the budget in order to maximize the total ROI.Objective function: maximize total ROI.Constraints:Total budget = R$ 10,000.Respect the minimum investment amounts for each campaign.5) Fixed Transportation CostA company needs to ship products from two warehouses (A1 and A2) to three stores (L1, L2, and L3). The cost per unit is as follows:L1L2L3A1868A2746The company wants to ensure that all stores receive their specific quantities of products without exceeding the capacities of the warehouses. The idea is to ensure that the total transportation cost is as low as possible. How should Solver be configured to find the optimal distribution solution?Objective function: lowest possible total costConstraints:Stores L1, L2, and L3 must receive exactly 200, 150, and 100 units, respectively.The total units shipped from A1 and A2 must not exceed their capacities of 250 and 200 units, respectively.6) Work Hours PlanningYou are the manager of a consulting company and need to allocate work hours to two types of tasks: data analysis and reports. The available work time is 120 hours per week, and you want to maximize the number of completed tasks. Each data analysis task consumes 4 hours and each report task consumes 6 hours. However, you must deliver at least 10 reports per week. Use Solver to maximize the total number of tasks, respecting the hour limit and the minimum report requirement.Objective function: maximize the number of completed tasks.Constraints:Availability of 120 hours per week.Produce at least 10 reports.
로그인하고 대화에 참여
— 코멘트 0
, 반응 1
첫 댓글을 남겨보세요