Information Systems: Build Models on Excel

California Time 13:00 Hours 

Build Excel Models 

1)      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?

2)       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:

·       Q_HardCover  - Quantity of hardcover books sold

·       P_{HardCover   -} Price of hardcover book

·       Q_{Paperback  -} Quantity of paperback books sold

·       P_Paperback   - Price of paperback book

·       Advertising  - Amount spent on advertising the book