City University of New York



Prof. I. Rudowsky CIS9.2/BUS31.3 MW11

Spring 2010 Homework #3

Goal Seek

Mountain Cycle specializes in making custom mountain bikes. The company founder, PJ Steffan, is having a hard time making the business profitable. Knowing that you have great business knowledge and solid financial sense, PJ has come to you for advice. The break even point in a business is when your revenues equal your costs – your profit is zero i.e., you are not losing money or making money. Any items sold above the break even point add to your profit. For example if PJ has the following costs associated with his business:

■ Fixed cost equals $65,000 (rent, salaries, insurance, etc.)

■ Variable cost equals $1,575 (what it costs to manufacture a bike)

■ Bike price equals $2,500 (the price you sell the bike)

Then according to the formula:

Break even point = fixed cost/(selling price per unit –variable cost per unit)

PJ has to sell 70.27 (or 71 to round up) bikes to break even.

PJ wants you to setup a spreadsheet with the above values and formula but to then use Goal Seek to determine what his sales price should be if he wants his break even point to be (a) 60 bikes and (b) 100 bikes. The fixed and variable costs do not change. Use Scenario Manager to display the results for both breakeven points.

Solver

HotSprings Spas manufactures and sells two spa models: the Steamboat and the Classic. HotSprings Spas receives spa bodies from another manufacturer and then adds a pump and tubing to circulate the water.

The Steamboat model demands 15.5 hours of labor and 14.5 feet of tubing. The Classic model requires 10.5 hours of labor and uses 20 feet of tubing.

Based on selling patterns, the owner, Deborah Liebson, has determined that the Steamboat model generates a profit of $400 per unit, and the Classic model generates $345 profit.

While Deborah would like a large labor capacity and sufficient tubing and motors to build any number of spas, her resources are limited.

For the next production period, Deborah has 2,650 labor hours, 3,450 feet of tubing, and 231 pumps available. Deborah needs assistance in figuring out how many Steamboat and Classic models (in integer value) to build in order to maximize her profit. Given the constraints above, use Solver to assist Deborah in her what-if analysis.

The webpage contains the link for HotSprings Spas’ spreadsheet.

Pivot Table

Established in 2002, t- has rapidly become the place to find, order, and save on Tshirts. One huge selling factor is that the company manufactures its own T-shirts. However, the quality manager for the production plant, Kasey Harnish, has noticed an unacceptable number of defective T-shirts being produced.

You have been hired to assist Kasey in understanding where the problems are concentrated. He suggests using a PivotTable to perform an analysis and has provided you with a spreadsheet. The following is a brief definition of the information within the spreadsheet:

A. Batch: A unique number that identifies each batch or group of products produced.

B. Product: A unique number that identifies each product.

C. Machine: A unique number that identifies each machine on which products are produced.

D. Employee: A unique number that identifies each employee producing products.

E. Batch Size: The number of products produced in a given batch.

F. Num Defect: The number of defective products produced in a given batch.

Write a report analyzing different pivot scenarios and explain what each pivot tells you about the possible source of the problem. Identify combinations of employees, machines, batches, batch size and product that point to problem areas. Show the pivot tables you used and how they support your conclusions. Based on your analysis, what suggestions do you have for Kasey to reduce defects?

Use the link on the website for the t- spreadsheet.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download