Microsoft Office Tutorials and References

In Depth Information

**Important Tools for Any Business**

Now, to paste the name into a formula, create the formula. When it comes time to apply the

range, simply choose from the Insert menu, Name, and Paste. Select the name to insert and

click OK.

Figure 2.23

Instead of using range

referencing in formulas,

apply named ranges to

simplify the formula.

P&L—Direct Contribution

When managing your business, you’re going to need a certain amount of return on ever y

investment to remain profitable. By creating a simple P&L layout, with the right formulas

combined on the P&L, you can effectively forecast each project’s contribution-margin ratio to

your business. It’s also important to know where your breakeven points are for both total and

monthly sales. Notice the example in Figure 2.24. There are six formulas that operate off

each other and can effectively help you manage your projected return on investments. The

key formulas and descriptions are:

Contribution Margin Ratio
=IF(C12<>0,C19/C12,0)
The contribution margin dollar

divided by the Projected Sales.

Break Even Sales
=IF(C4<>0,F25/F4,0)
The Total Fixed Expenses divided by the

Contribution Margin Ratio.

Monthly Breakeven Sales
=IF(C11<>0,C5/C11,0)
The Breakeven Sales dollar

amount divided by Months. Months also can be described as the life of the product.

Contribution Margin
=C12-SUM(C15:C18)
The Projected Sales dollar amount divided

by the sum of the Variable Expenses (Materials, Labor, Variable Overhead, Other).

Total Fixed Expenses
=SUM(F11:F24)
The sum of the total Fixed Expenses.

Profit During the Period
=(C12-C5)*C4
The Projected Sales minus the Breakeven

Sales times the Contribution Margin Ratio.