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.