• Design a Spreadsheet

  • 15 Points - Workbook is organized with clear instructions

  • 15 Points - Model is clearly set up to solve the problem if desired.

  • 15 Points - All worksheets made aid in making the problem and assumptions clear.

  • 15 Points - All input cells are easy to find, and manipulate and all output cells are easy to find and interpret.

  • 15 Points - All relationships are easy to discern using Auditing tools and various cells have comments highlighting additional information and data validation is used when and where appropriate.

EXAMPLE As product-marketing manager, one of our jobs is to prepare recommendations to the The Executive Committee as to how advertising expenditures should be allocated. Last year's Advertising advertising budget of $40,000 was spent in equal increments over the four quarters. Initial Budget expectations are that we will repeat this plan in the coming year. However, the committee Decision would like to know whether some other allocation would be advantageous and whether the total budget should be changed. Our product sells for $40 and costs us $25 to produce. Sales in the past have been seasonal, and our consultants have estimated seasonal adjustment factors for unit sales as follows: 01 : 90% 03 : 80% 02:110% 04 : 120% (A seasonal adjustment factor measures the percentage of average quarterly demand experienced in a given quarter.) In addition to production costs, we must take into account the cost of the sales force (projected to be $34,000 over the year, allocated as follows: 01 and 02, $8,000 each; 03 and 04, $9,000 each), the cost of advertising itself, and overhead (typically around 15 percent of revenues). Quarterly unit sales seem to run around 4,000 units when advertising is around $10,000. Clearly, advertising will increase sales, but there are limits to its impact. Our consultants several years ago estimated the relationship between advertising and sales. Converting that relationship to current conditions gives the following formula: Unit sales = 35 x seasonal factor X (3,000 + Advertising) EXAMPLE As product-marketing manager, one of our jobs is to prepare recommendations to the The Executive Committee as to how advertising expenditures should be allocated. Last year's Advertising advertising budget of $40,000 was spent in equal increments over the four quarters. Initial Budget expectations are that we will repeat this plan in the coming year. However, the committee Decision would like to know whether some other allocation would be advantageous and whether the total budget should be changed. Our product sells for $40 and costs us $25 to produce. Sales in the past have been seasonal, and our consultants have estimated seasonal adjustment factors for unit sales as follows: 01 : 90% 03 : 80% 02:110% 04 : 120% (A seasonal adjustment factor measures the percentage of average quarterly demand experienced in a given quarter.) In addition to production costs, we must take into account the cost of the sales force (projected to be $34,000 over the year, allocated as follows: 01 and 02, $8,000 each; 03 and 04, $9,000 each), the cost of advertising itself, and overhead (typically around 15 percent of revenues). Quarterly unit sales seem to run around 4,000 units when advertising is around $10,000. Clearly, advertising will increase sales, but there are limits to its impact. Our consultants several years ago estimated the relationship between advertising and sales. Converting that relationship to current conditions gives the following formula: Unit sales = 35 x seasonal factor X (3,000 + Advertising)