Microsoft Office Tutorials and References
In Depth Information
Case 8: Kuhlman’s Department Store Media Problem
KUHLMAN ’ S DEPARTMENT STORE
Decision Support Using Excel Solver
s Department Store is a family-owned business in a thriving Midwestern city. The store has been
successful for more than 90 years, but recently it has come under serious pressure from a new suburban
shopping mall. Sales and operating income have been falling in the past two years since the mall opened, and
the store is now barely making a profit.
The owners recently hired a media consulting firm to evaluate the store
s advertising strategy and suggest
s has traditionally used direct mail and newspaper advertisements, but the media
consultants recommended that the store consider TV, radio, and magazine ads, and even Internet ads for
advertising alternatives. The consultants performed a media study and developed estimates for potential
audience size, cost per ad, recommended maximum number of ads, and the estimated percentage of
customers gained from the added media exposure.
You are the MIS manager for Kuhlman
s, and you have been asked to build a spreadsheet model to
determine the best mix of media to optimize the store
s net income, given an advertising budget of $80,000.
Review the spreadsheet concepts discussed in class and in your textbook.
Your instructor may assign Excel exercises to help prepare you for this case.
Tutorial D explains how to set up and use Solver for maximization and minimization problems.
Review the file-saving instructions
it is always a good idea to save an extra copy of your work
on a USB thumb drive.
Reviewing Tutorial F will help you brush up on your presentation skills.
You have been asked to use your Excel skills to build a decision model and determine how much of each
advertising medium Kuhlman
s should purchase in order to maximize the company
s net income. To build
this model, you need the following data, which the media consultants compiled:
The six advertising media to consider—Ads for the Internet, television, radio, newspaper,
magazines, and direct mail
The potential audience size in the region that Kuhlman
The cost per ad, cost per commercial, or cost per Web site hit (for Internet ads)
The maximum amounts of advertising for each available medium
The percentage of customer visits expected as a result of each ad or commercial
In addition, the Accounting Department has given you the following information about the operating
budget based on historical revenues and costs:
The average sales per new customer per year
The maximum budget for advertising expenses