Suppose a company makes a product, which they sell at a price of $35. Normally, the firm’s variable costs per unit are $14 and total fixed costs are $42000. Using Excel, analyze the sensitivity of profitability to changes in costs and volume. Assume variable costs can rise or fall $2 per unit from $14 and that fixed costs can rise or fall $6000 from the $42000 total fixed cost projection.
Show the profit (loss) for all scenarios assuming sales volumes of 1000, 1500, 2000, 2500 and 3000 units. Your worksheet should show 45 profit (loss) figures given the sales volume levels and the various mix of fixed and variable cost.