Microsoft Office Tutorials and References
In Depth Information
Case 8: Kuhlman’s Department Store Media Problem
8
KUHLMAN S DEPARTMENT STORE
MEDIA PROBLEM
CASE
Decision Support Using Excel Solver
PREVIEW
Kuhlman
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
improvements. Kuhlman
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.
PREPARATION
￿
￿
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.
￿
BACKGROUND
You have been asked to use your Excel skills to build a decision model and determine how much of each
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:
￿
magazines, and direct mail
￿
The potential audience size in the region that Kuhlman
s serves
￿
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

Search JabSto ::

Custom Search