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
=B6B7
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
=B8B13
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.
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
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
to generate the maximum profit for the first quarter.
You are interested in maximizing profit by changing
advertising expenditures.
■
You will see messages in the status bar as the problem is set up and Solver starts working.
After a
moment, you'll see a message that Solver has found a solution.
Solver finds that Q1 advertising of
$17,093 yields the maximum profit $15,093.
This is the end of the preview.
Sign up
to
access the rest of the document.
 Spring '10
 Aziri
 Revenue, Interest

Click to edit the document details