For each of the following, build a model on excel. In addition, for problem 1 perform a sensitivity analysis and construct a Tornado Chart.
1. You have been asked to build a model to help determine the advertising budget for your company for a specific product. The product sells for $10 and has a $9 cost to produce each unit as well as a fixed cost of $10,000. Your marketing department estimates that demand as a function of advertising budget can be expressed as follows:
Demand = 333.8 * Advertising^(.454)Only build excel models for 2 and 3
2. An electronics firm is currently manufacturing an item that has a variable cost of $0.60 per unit and selling price of $1.10 per unit. Fixed costs are $15,500. Current demand is 32,000 units. The firm can substantially improve the product quality by adding a new piece of equipment at an additional fixed cost of $8,000. Variable cost would increase to $0.70 but the demand is expected to jump to 50,000 units due to the higher quality of the product. Should the company buy the new equipment?
3. Your publishing company is launching a new book soon and it has decided to release a hard cover and a paperback version at the same time. The cost to produce each hard cover book is $12 and the cost to produce each paperback book is $8.
You company wants you to build a model to estimate the total profit associated with the two books based on the price for each book and the advertising budget. The company's marketing department has estimated the total number of books that will be sold according to the following equations:
Hardcover: QHardCover = (10,000*(12 - PHardCover*.03) * Advertising^0.2
Paperback: QPaperback = (15,000*(8 - PPaperback*.04) * Advertising^0.2
Notes:
QHardCover - Quantity of hardcover books sold
PHardCover - Price of hardcover book
QPaperback - Quantity of paperback books sold
PPaperback - Price of paperback book
Advertising - Amount spent on advertising the book.