Task: Use Excel and the Solver add-in to explore the effect of various resource constraints on the optimal product mix.

a. Read the article “Boost Profits With Excel,” by James A. Weisel in the December 2003 issue of the Journal ofAccountancy (available online at the AICPA’s Web site, www.aicpa.org—navigate to the Online Journal of Accountancy pages).

b. Download the sample spreadsheet discussed in the article (http://www.aicpa .org/download/pubs/jofa/2003_12_weisel.xls), and print out the solutions dis¬ cussed in the article.

c. Rerun the Solver program to determine which of the following parameter changes maximizes income (print out the results of each option):

• 2,500 additional minutes of labor for an additional $600

• 5,000 additional minutes of labor for an additional $1,500

• 7,500 additional minutes of labor for an additional $3,000 • 10,000 additional minutes of labor for an additional $5,000

d. The previous step required much manual work to repeatedly change the assumptions and rerun the simulation with Solver. Create a one-variable data table to automate this process. (The article “Hey, What If... ?” by Stephanie M. Bryant in the June 2000 issue of the Journal ofAccountancy explains how to create data tables). The variable you will be modifying is additional minutes of labor, for the four increments used in step c (2,500 through 10,000 extra minutes). Change the value of the formula for total labor costs (cell Jll) to equal 10,000 + 0.25 per additional minute of labor over 50,000 minutes. {Hint: you will want to reference cell J25 in your formula.) Your one-variable data table should look something like this (with the blank cells showing the results of the simulation—compare to your results for step c):

Transcribed Image Text:

Additional Labor Minutes over 50,000 Operating Income 0 2,500 5,000 7,500 10,000