solvsamp - e11e4212379e971930116bc7ba367d12528a5dca.xls...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
e11e4212379e971930116bc7ba367d12528a5dca.xls Page 1 Quick Tour of Microsoft Excel Solver Month Q1 Q2 Q3 Q4 Total Seasonality 0.9 1.1 0.8 1.2 Units Sold 3,592 4,390 3,192 4,789 15,962 Sales Revenue $143,662 $175,587 $127,700 $191,549 $638,498 Color Coding Cost of Sales 89,789 109,742 79,812 119,718 399,061 Gross Margin 53,873 65,845 47,887 71,831 239,437 Target cell Salesforce 8,000 8,000 9,000 9,000 34,000 Changing cells Advertising 10,000 10,000 10,000 10,000 40,000 Corp Overhead 21,549 26,338 19,155 28,732 95,775 Constraints Total Costs 39,549 44,338 38,155 47,732 169,775 Prod. Profit $14,324 $21,507 $9,732 $24,099 $69,662 Profit Margin 10% 12% 8% 13% 11% Product Price $40.00 Product Cost $25.00 The following examples show you how to work with the model above to solve for one value or several values to maximize or minimize another value, enter and change constraints, and save a problem model. Row Contains Explanation 3 Fixed values Seasonality factor: sales are higher in quarters 2 and 4, and lower in quarters 1 and 3. 5 =35*B3*(B11+3000)^0.5 Forecast for units sold each quarter: row 3 contains the seasonality factor; row 11 contains the cost of advertising. 6 =B5*$B$18 Sales revenue: forecast for units sold (row 5) times price (cell B18). 7 =B5*$B$19 Cost of sales: forecast for units sold (row 5) times product cost (cell B19). 8 =B6-B7 Gross margin: sales revenues (row 6) minus cost of sales (row 7). 10 Fixed values Sales personnel expenses. 11 Fixed values Advertising budget (about 6.3% of sales). 12 =0.15*B6 Corporate overhead expenses: sales revenues (row 6) times 15%. 13 =SUM(B10:B12) Total costs: sales personnel expenses (row 10) plus advertising (row 11) plus overhead (row 12). 15 =B8-B13 Product profit: gross margin (row 8) minus total costs (row 13). 16 =B15/B6 Profit margin: profit (row 15) divided by sales revenue (row 6). 18 Fixed values Product price. 19 Fixed values Product cost. This is a typical marketing model that shows sales rising from a base figure (perhaps due to the sales personnel) along with increases in advertising, but with diminishing returns. For example, the first $5,000 of advertising in Q1 yields about 1,092 incremental units sold, but the next $5,000 yields only about 775 units more.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
e11e4212379e971930116bc7ba367d12528a5dca.xls Page 2 You can use Solver to find out whether the advertising budget is too low, and whether advertising should be allocated differently over time to take advantage of the changing seasonality factor. Solving for a Value to Maximize Another Value One way you can use Solver is to determine the maximum value of a cell by changing another cell. The two cells must be related through the formulas on the worksheet. If they are not, changing the value in one cell will not change the value in the other cell. For example, in the sample worksheet, you want to know how much you need to spend on advertising
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 09/01/2010 for the course COMPUTER S CSI1306 taught by Professor Aziri during the Spring '10 term at University of Ottawa.

Page1 / 17

solvsamp - e11e4212379e971930116bc7ba367d12528a5dca.xls...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online