Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
Cost of Goods as a percent of Sales
s paid to
purchase the merchandise it sells, expressed as a ratio of its sales revenue. The cost of goods
also includes the costs of freight-in, duties, and preparing the merchandise for sale.
This value, 80%, is the amount that Kuhlman
Other Operating Expenses
This value, $820,000, is Kuhlman
s budgeted operating expenses for
the year, exclusive of the advertising budget.
Income Tax Rate
This value, 22%, is the current income tax rate.
Your Calculations section will contain the data obtained from the media consultants for each of the
advertising media. It will also contain the Changing Cells row, the calculations for Advertising Expense,
Customers, Sales, and Cost of Goods, and the totals for all the calculations. See Figure 8-2. An explanation
of the line items follows the figure.
This line contains the titles for each of the six media types, which you enter
into cells C11 through H11: Internet Ads, TV, Radio, Newspaper, Magazine, and Direct Mail Ads.
These values are the average audience in the region exposed to one airing
or printing of the advertisement for each media type. Enter the following values into cells C12
through H12 in the order shown: 3,000,000, 400,000, 250,000, 45,000, 55,000, and 250,000.
Cost per Ad or Web Hit
These values are the cost of an ad in each advertising medium. Note
that Internet ads are charged
or per Web hit to the ad site. Enter the following values
into cells C13 through H13 in the order shown: $.010, $3,000, $1,000, $200, $500, $.40.
Number of Ads or Web Hits
This line is the heart of the Solver model: the Changing Cells.
These values are the amounts of each type of advertising that Solver recommends for Kuhlman
Enter a zero in cells C14, D14, E14, and G14, enter 30 in cell F14 for Newspaper Ads, and enter
185,000 in cell H14 for Direct Mail Ads. These values represent Kuhlman
s historical spending
for advertising (see the following Note regarding Internet ads). You might want to fill in the cells
with a background color to indicate that they will be the Changing Cells for Solver.
Maximum Ads or Web Hits Available
These cells are the limits for the amounts of ads
purchased, as recommended by the media consultants. You will use these cells to define constraints
for the amount of each medium purchased. Enter the following values into cells C15 through
H15 in the order shown: 3,000,000, 16, 30, 52, 26, 250,000.
% of customers
These cells contain estimates of the percentage of new customers obtained from
exposure to each type of advertising media. The media consultants were conservative in their
estimates for the impact of all media types except direct mail ads. Kuhlman
s provided the figure
of 14.00% based on customers who brought in direct mail ads for a special discount during the
past year. Enter the following values into cells C16 through H16 in the order shown: .50%, .70%,
.80%, 1.50%, .80%, and 14.00%.