Back Savers Is a Company That Produces Backpacks Primarily for Students.They

Question

Examlex · Accepted Answer

The Answer is (a)To build a spreadsheet model for this problem,start by entering the data.The data for this problem are the unit profit of each type of backpack,the resource requirements (square feet of nylon and labor hours required),the availability of each resource,5400 square feet of nylon and (35 laborers)(40 hours/laborer)= 1400 labor hours,and the sales forecast for each type of backpack (1000 Collegiates and 1200 Minis).In order to keep the units consistent in row 8 (hours),the labor required for each backpack (in cells C8 and D8)are converted from minutes to hours (0.75 hours = 45 minutes,0.667 hours = 40 minutes).The range names UnitProfit (C4:D4),Available (G7:G8),and SalesForecast (C13:D13)are added for these data.

(a)To build a spreadsheet model for this problem,start by entering the data.The data for this problem are the unit profit of each type of backpack,the resource requirements (square feet of nylon and labor hours required),the availability of each resource,5400 square feet of nylon and (35 laborers)(40 hours/laborer)= 1400 labor hours,and the sales forecast for each type of backpack (1000 Collegiates and 1200 Minis).In order to keep the units consistent in row 8 (hours),the labor required for each backpack (in cells C8 and D8)are converted from minutes to hours (0.75 hours = 45 minutes,0.667 hours = 40 minutes).The range names UnitProfit (C4:D4),Available (G7:G8),and SalesForecast (C13:D13)are added for these data. The decision to be made in this problem is how many of each type of backpack to make.Therefore,we add two changing cells with range name UnitsProduced (C11:D11).The values in CallsPlaced will eventually be determined by the Solver.For now,arbitrary values of 10 and 10 are entered. The goal is to produce backpacks so as to achieve the highest total profit.Thus,the objective cell should calculate the total profit,where the objective will be to maximize this objective cell.In this case,the total profit will be Total Profit = ($32)(# of Collegiates)+ ($24)(# of Minis)or Total Cost = SUMPRODUCT(UnitProfit,UnitsProduced).This formula is entered into cell G11 and given a range name of TotalProfit.With 10 Collegiates and 10 Minis produced,the total profit would be ($32)(10)+ ($24)(10)= $560. The first set of constraints in this problem involve the limited available resources (nylon and labor hours).Given the number of units produced (UnitsProduced in C11:D11),we calculate the total resources required.For nylon,this will be =SUMPRODUCT(C7:D7,UnitsProduced)in cell E7.By using a range name or an absolute reference for the units produced,this formula can be copied into cell E8 to calculate the labor hours required.The total resources used (TotalResources in E7:E8)must be <= Available (in cells G7:G8),as indicated by the <= in F7:F8. The final constraint is that it does not make sense to produce more backpacks than can be sold (as predicted by the sales forecast).Therefore UnitsProduced (C11:D11)should be less-than-or-equal-to the SalesForecast (C13:D13),as indicated by the <= in C12:D12 The Solver information and solved spreadsheet are shown below. Thus,they should produce 1000 Collegiates and 975 Minis to achieve the maximum total profit of $55,400.(b)To build an algebraic model for this problem,start by defining the decision variables.In this case,the two decisions are how many Collegiates to produce and how many Minis to produce.These variables are defined below: Let C = Number of Collegiates to produce,M = Number of Minis to produce.Next determine the goal of the problem.In this case,the goal is to produce the number of each type of backpack to achieve the highest possible total profit.Each Collegiate yields a unit profit of $32 while each Mini yields a unit profit of $24.The objective function is therefore Maximize Total Profit = $32C + $24M.The first set of constraints in this problem involve the limited resources (nylon and labor hours).Given the number of backpacks produced,C and M,and the required nylon and labor hours for each,the total resources used can be calculated.These total resources used need to be less than or equal to the amount available.Since the labor available is in units of hours,the labor required for each backpack needs to be in units of hours (3/4 hour and 2/3 hour)rather than minutes (45 minutes and 40 minutes).These constraints are as follows: Nylon: 3C + 2M ≤ 5400 square feet,Labor Hours: (3/4)C + (2/3)M ≤ 1400 hours.The final constraint is that they should not produce more of each backpack than the sales forecast.Therefore,Sales Forecast: C ≤ 1000 M ≤ 1200.After adding nonnegativity constraints,the complete algebraic formulation is given below: Let C = Number of Collegiates to produce,M = Number of Minis to produce.Maximize Total Profit = $32C + $24M,subject to Nylon: 3C + 2M ≤ 5400 square feet,Labor Hours: (3/4)C + (2/3)M ≤ 1400 hours,Sales Forecast: C ≤ 1000 M ≤ 1200.and C ≥ 0,M ≥ 0.(c)Start by plotting a graph with Collegiates (C)on the horizontal axis and Minis (M)on the vertical axis,as shown below. Next,the four constraint boundary lines (where the left-hand-side of the constraint exactly equals the right-hand-side)need to be plotted.The easiest way to do this is by determining where these lines intercepts the two axes.For the Nylon constraint boundary line (3C + 2M = 5400),setting M = 0 yields a C-intercept of 1800 while setting C = 0 yields an M-intercept of 2700.For the Labor constraint boundary line ((3/4)C + (2/3)M = 1400),setting M = 0 yields a C-intercept of 1866.67 while setting C = 0 yields an M-intercept of 2100.The sales forecast constraints are a horizontal line at M = 1200 and a vertical line at C = 1000.These constraint boundary lines are plotted below. A feasible solution must be below and/or to the left of all four of these constraints while being above the Collegiate axis (since C ≥ 0)and to the right of the Mini axis (since M ≥ 0).This yields the feasible region shown below. To find the optimal solution,an objective function line is plotted by setting the objective function equal to a value.For example,the objective function line when the value of the objective function is $48,000 is plotted as a dashed line below. All objective function lines will be parallel to this one.To find the feasible solution that maximizes profit,slide this line out as far as possible while still touching the feasible region.This occurs when the profit is $55,400,and the objective function line intersect the feasible region at the single point with (C,M)= (1000,975)as shown below. Therefore,the optimal solution is to produce 1000 Collegiates and 975 Minis,yielding a total profit of $55,400.

The decision to be made in this problem is how many of each type of backpack to make.Therefore,we add two changing cells with range name UnitsProduced (C11:D11).The values in CallsPlaced will eventually be determined by the Solver.For now,arbitrary values of 10 and 10 are entered.

The goal is to produce backpacks so as to achieve the highest total profit.Thus,the objective cell should calculate the total profit,where the objective will be to maximize this objective cell.In this case,the total profit will be Total Profit = ($32)(# of Collegiates)+ ($24)(# of Minis)or Total Cost = SUMPRODUCT(UnitProfit,UnitsProduced).This formula is entered into cell G11 and given a range name of TotalProfit.With 10 Collegiates and 10 Minis produced,the total profit would be ($32)(10)+ ($24)(10)= $560.

The first set of constraints in this problem involve the limited available resources (nylon and labor hours).Given the number of units produced (UnitsProduced in C11:D11),we calculate the total resources required.For nylon,this will be =SUMPRODUCT(C7:D7,UnitsProduced)in cell E7.By using a range name or an absolute reference for the units produced,this formula can be copied into cell E8 to calculate the labor hours required.The total resources used (TotalResources in E7:E8)must be <= Available (in cells G7:G8),as indicated by the <= in F7:F8.

The final constraint is that it does not make sense to produce more backpacks than can be sold (as predicted by the sales forecast).Therefore UnitsProduced (C11:D11)should be less-than-or-equal-to the SalesForecast (C13:D13),as indicated by the <= in C12:D12

The Solver information and solved spreadsheet are shown below.

Thus,they should produce 1000 Collegiates and 975 Minis to achieve the maximum total profit of $55,400.(b)To build an algebraic model for this problem,start by defining the decision variables.In this case,the two decisions are how many Collegiates to produce and how many Minis to produce.These variables are defined below: Let C = Number of Collegiates to produce,M = Number of Minis to produce.Next determine the goal of the problem.In this case,the goal is to produce the number of each type of backpack to achieve the highest possible total profit.Each Collegiate yields a unit profit of $32 while each Mini yields a unit profit of $24.The objective function is therefore Maximize Total Profit = $32C + $24M.The first set of constraints in this problem involve the limited resources (nylon and labor hours).Given the number of backpacks produced,C and M,and the required nylon and labor hours for each,the total resources used can be calculated.These total resources used need to be less than or equal to the amount available.Since the labor available is in units of hours,the labor required for each backpack needs to be in units of hours (3/4 hour and 2/3 hour)rather than minutes (45 minutes and 40 minutes).These constraints are as follows: Nylon: 3C + 2M ≤ 5400 square feet,Labor Hours: (3/4)C + (2/3)M ≤ 1400 hours.The final constraint is that they should not produce more of each backpack than the sales forecast.Therefore,Sales Forecast: C ≤ 1000 M ≤ 1200.After adding nonnegativity constraints,the complete algebraic formulation is given below: Let C = Number of Collegiates to produce,M = Number of Minis to produce.Maximize Total Profit = $32C + $24M,subject to Nylon: 3C + 2M ≤ 5400 square feet,Labor Hours: (3/4)C + (2/3)M ≤ 1400 hours,Sales Forecast: C ≤ 1000 M ≤ 1200.and C ≥ 0,M ≥ 0.(c)Start by plotting a graph with Collegiates (C)on the horizontal axis and Minis (M)on the vertical axis,as shown below.

Next,the four constraint boundary lines (where the left-hand-side of the constraint exactly equals the right-hand-side)need to be plotted.The easiest way to do this is by determining where these lines intercepts the two axes.For the Nylon constraint boundary line (3C + 2M = 5400),setting M = 0 yields a C-intercept of 1800 while setting C = 0 yields an M-intercept of 2700.For the Labor constraint boundary line ((3/4)C + (2/3)M = 1400),setting M = 0 yields a C-intercept of 1866.67 while setting C = 0 yields an M-intercept of 2100.The sales forecast constraints are a horizontal line at M = 1200 and a vertical line at C = 1000.These constraint boundary lines are plotted below.

A feasible solution must be below and/or to the left of all four of these constraints while being above the Collegiate axis (since C ≥ 0)and to the right of the Mini axis (since M ≥ 0).This yields the feasible region shown below.

To find the optimal solution,an objective function line is plotted by setting the objective function equal to a value.For example,the objective function line when the value of the objective function is $48,000 is plotted as a dashed line below.

All objective function lines will be parallel to this one.To find the feasible solution that maximizes profit,slide this line out as far as possible while still touching the feasible region.This occurs when the profit is $55,400,and the objective function line intersect the feasible region at the single point with (C,M)= (1000,975)as shown below.

Therefore,the optimal solution is to produce 1000 Collegiates and 975 Minis,yielding a total profit of $55,400.

Back Savers Is a Company That Produces Backpacks Primarily for Students.They

Definitions: