Please use excel for this problem A company is trying to determine how to allocate its $145,000 advertising budget for a new product. The company is considering newspaper ads and television commercials as its primary means for advertising. The following table summarizes the costs of advertising in these different media and the number of new customers reached by increasing amounts of advertising: Media & Number of Ads, Number of New Customers Reached, Cost Per Ad In Dollars Newspaper: 1 – 10 1900 1,000 Newspaper: 11 – 20 1700 900 Newspaper: 21 – 30 1400 800 Television: 1 – 5 10,000 12,000 Television: 6 – 10 7,500 10,000 Television: 11 – 15 5,000 8,000 For instance, each of the first 10 ads the company places in newspapers will cost $1,000 and is expected to reach 1900 new customers. Each of the next 10 newspaper ads will cost $900 and is expected to reach 1700 new customers. Note that the number of new customers reached by increasing amounts of advertising decreases as the advertising saturates the market. Assume the company will purchase no more than 30 newspaper ads and no more than 15 television ads. How many ads should the company place to maximize the number of new customers reached by advertising? 1. Formulate the LP model for this problem. 2. Create the spreadsheet model and use Solver to solve the problem
Please use excel for this problem
A company is trying to determine how to allocate its $145,000 advertising budget
for a new product. The company is considering newspaper ads and television
commercials as its primary means for advertising. The following table summarizes
the costs of advertising in these different media and the number of new customers
reached by increasing amounts of advertising:
Media & Number of Ads, Number of New Customers Reached, Cost Per Ad In Dollars
Newspaper: 1 – 10 1900 1,000
Newspaper: 11 – 20 1700 900
Newspaper: 21 – 30 1400 800
Television: 1 – 5 10,000 12,000
Television: 6 – 10 7,500 10,000
Television: 11 – 15 5,000 8,000
For instance, each of the first 10 ads the company places in newspapers will cost
$1,000 and is expected to reach 1900 new customers. Each of the next 10
newspaper ads will cost $900 and is expected to reach 1700 new customers. Note
that the number of new customers reached by increasing amounts of advertising
decreases as the advertising saturates the market. Assume the company will
purchase no more than 30 newspaper ads and no more than 15 television ads. How
many ads should the company place to maximize the number of new customers
reached by advertising?
1. Formulate the LP model for this problem.
2. Create the spreadsheet model and use Solver to solve the problem
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 6 images
