Quant Question (Using Excel)
Gone fishing…….Individual Assignment #1
Mark Martinko is currently considering the possibility of developing manufacturing facilities to produce a new type of fishing pole. The fishing pole would have a price of $8.00. Mark estimates that the fixed cost could range anywhere from $10,000 to $11,000, the variable cost could range from $4.00 to $6.00, and the volume could range anywhere from 4,000 to 6,000 units. Unfortunately, Mark has no idea what the fixed cost, variable cost, or the volume would be. Therefore, he would like to try several different conditions to see what effect these conditions would have on the break-even point in units and the overall profitability.
For the first condition, Mark would like to use a fixed cost of $10,000, a variable cost of $4.00 per unit, and a volume of 4,000 units sold. The second condition should have a fixed cost of $10,000, a variable cost of $5.00 and a volume of 4,000. The third condition Mark would like to test, has a fixed cost, again, of $10,000, but the variable cost has increased to $6.00, while the volume remains at 4,000 units. For the fourth, fifth, and sixth conditions, Mark would like to hold the fixed cost equal to $11,000 and the total units sold equal to 4,000 units. For the fourth condition, variable costs should start $4.00.
For the fifth condition, variable cost should be $5.00, and for the sixth condition, variable costs should be $6.00. For the seventh, eighth, and ninth conditions that Mark would like to test, he would like to hold fixed costs at $10,000 and increase the units to sold to 6,000. For the seventh condition, variable costs should start at $4.00. For the eighth condition, variable costs should be $5.00, and for the ninth condition, variable costs should be $6.00. For the final three conditions that Mark would like to test, conditions 10, 11, and 12, he would like to hold fixed costs equal to $11,000 and the number of units sold equal to 6,000. For the tenth condition, variable costs should again start at $4.00 per unit. For the eleventh condition, variable cost should be equal to $5.00 per unit, and for the twelfth condition, variable cost should be equal to $6.00 per unit.
Using a table (MS Excel) with each row representing a different alternative, Mark would like you to determine:
1. The breakeven point in units for each of the twelve scenarios
2. The profit for each of the twelve scenarios
3. The alternative that will give Mark the best overall profitability. Indicate your answer by highlighting the box.