I need help with the Solver function in Excel. I have to solve word problems with multiple constraints and I don't understand how the book is presenting it. I need help with how they are telling us the constraint for contribution margin. The first picture is an example of how the book is doing it that I don't understand. The constraints are listed in 14. and their way of entering the ocnstraint into solver is 23.
Some help with this next problem would also be appreciated as well. I do have an excel file with the other information, I just can't figure out the solver part.
314 Excel for Accounting Chapter 10 Budgeting and Cost Analysis 13. Choose Datas Forecast-What if Analysis Goal Seek and completethe Goal Seek Make sure the Set Cell box shows D10 (change it if necessary) Enter 12000 in the To Value box Enter B13 in the By Changing Cell box. The Units Sold cell increased to 9,500 and the Fixed Costs Per Unit cell decreased to $13.74 14. Click OK to close the Goal Seek Status dialog box Now you want to consider how to maximize reventue by changing the sales price and costs. Past experience has indicated that the sales price cannot exceed $105, variable costs cannot be below $83 per unit, total foxed costs cannot be below $129,000, and the contribution margin must be at least 18% of the sales revenue. You will use Solver to determine the sales price and cost structure to maxmize net income. 15. Choose File-Options and click Add-Ins in the left pane. 16. Make sure that Excel Add-Ins appears in the Manage box at the bottom of the window and click Go 17. In the next window, click the checkbox for Solver Add-In and click OK Solver has now been added to the far right of the Data tab on the Ribbon Right-click the CVP Analysis #2 tab and choose Move or Copy, choose (move to end). click the checkbox for Create a Copy, and click OK 18. 19. Rename the CVP Analysis #2 (2) tab to: Analysis #3 20. Select cell D10 and choose Data-Analyze-+Solver. 21. Click in the By Changing Variable Cells box, click cell F6, type a comma and click cell F7, type a comma, and select cell D9 Note that the Set Objective box shows cell D10 and the Max option is selected. Solver will seek a solution that maximizes net income (cell D10). Solver can change values in ceills F6, F7, and D9 to arrive at the solution 22. Click the Add box and complete the Add Constraint dialog box as shown, clicking OK when finished Add Constraint Constraint Cell Reference SD$9 | | 129000 OK Add Cancel This constraint ensures that Solver's solution will not reduce fixed costs below $129,000 Repeat step 22 three times to constrain the solution such that cell F6 is less than or equal to 105, cell F7 is greater than or equal to 83, and cell F8 is less than or equal to cell F6 multiplied by 188 The final constraint includes a formula in which cell F6 is multiplied by 18%. Solver constraints can be entered as either numbers or formulas 23. 314 Excel for Accounting Chapter 10 Budgeting and Cost Analysis 13. Choose Datas Forecast-What if Analysis Goal Seek and completethe Goal Seek Make sure the Set Cell box shows D10 (change it if necessary) Enter 12000 in the To Value box Enter B13 in the By Changing Cell box. The Units Sold cell increased to 9,500 and the Fixed Costs Per Unit cell decreased to $13.74 14. Click OK to close the Goal Seek Status dialog box Now you want to consider how to maximize reventue by changing the sales price and costs. Past experience has indicated that the sales price cannot exceed $105, variable costs cannot be below $83 per unit, total foxed costs cannot be below $129,000, and the contribution margin must be at least 18% of the sales revenue. You will use Solver to determine the sales price and cost structure to maxmize net income. 15. Choose File-Options and click Add-Ins in the left pane. 16. Make sure that Excel Add-Ins appears in the Manage box at the bottom of the window and click Go 17. In the next window, click the checkbox for Solver Add-In and click OK Solver has now been added to the far right of the Data tab on the Ribbon Right-click the CVP Analysis #2 tab and choose Move or Copy, choose (move to end). click the checkbox for Create a Copy, and click OK 18. 19. Rename the CVP Analysis #2 (2) tab to: Analysis #3 20. Select cell D10 and choose Data-Analyze-+Solver. 21. Click in the By Changing Variable Cells box, click cell F6, type a comma and click cell F7, type a comma, and select cell D9 Note that the Set Objective box shows cell D10 and the Max option is selected. Solver will seek a solution that maximizes net income (cell D10). Solver can change values in ceills F6, F7, and D9 to arrive at the solution 22. Click the Add box and complete the Add Constraint dialog box as shown, clicking OK when finished Add Constraint Constraint Cell Reference SD$9 | | 129000 OK Add Cancel This constraint ensures that Solver's solution will not reduce fixed costs below $129,000 Repeat step 22 three times to constrain the solution such that cell F6 is less than or equal to 105, cell F7 is greater than or equal to 83, and cell F8 is less than or equal to cell F6 multiplied by 188 The final constraint includes a formula in which cell F6 is multiplied by 18%. Solver constraints can be entered as either numbers or formulas 23
