Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
NOTE
Kuhlman’s cannot actually dictate how many Web ads it will pay for, other than specifying a maximum charge that depends on
the number of potential customers who click the Web ad. This advertising medium was included in the case to recognize the
potential of the Internet to attract customers. In the case of a local department store, the impact of Internet advertising may not
be significant.
INTERNET ADS AND WEB
HITS
The calculations that make up the rest of the section are described next:
￿
Advertising Expense
This value is the Cost per Ad or Web Hit multiplied by the Number of Ads
or Web Hits.
￿
Customers
This value is the estimated number of customers gained from each of the media.
The calculation for this value is different for the Internet Ads and Direct Mail Ads than it is for
the rest of the media. The customers gained from the Internet Ads and Direct Mail Ads is simply
the Number of Ads or Web Hits multiplied by the % of customers. For the other four media
(TV, radio, newspapers, and magazine), the customers gained equals the Potential Audience
multiplied by the Number of Ads multiplied by the % of customers. Double-check your formulas
to make sure you entered them correctly.
￿
Sales
This value is the number of customers gained multiplied by Average Sales per new
customer per year. The latter value is taken from the Constants section.
￿
Cost of Goods
This value is the Sales multiplied by the Cost of Goods as a percent of Sales. The
latter value is taken from the Constants section.
￿
Sales per Advertising Dollar
This value is the Sales divided by the Advertising Expense.
Although this value is not needed to solve the problem, it will show the management team the
relative effectiveness of each advertising medium purchased. You should use an IF statement to
place
or another appropriate comment in the cell if the Advertising Expense is zero. Solver
might not select certain advertising media. If you do not use the IF statement and there is no
Advertising Expense, the cell will show a division by zero error.
NA
￿
Totals (for Advertising Expense, Customers, Sales, and Cost of Goods)
You should total each of
these rows and enter the results in cells I17, I18, I19, and I20, respectively. The total number of
new customers is not strictly necessary, but the other three totals are needed for further
calculations or constraints.
If you wrote the formulas correctly, your Calculations section should look like Figure 8-3.
FIGURE 8-3
Completed Calculations section—historical data
Income Statement Section
Kuhlman
s income statement (see Figure 8-4) is actually a projection for the coming year, and is based either
on its traditional advertising mix or the solution you produce with Solver. This section explains how you
should structure the Income Statement section and explains the formulas for each cell.
 
Search JabSto ::




Custom Search