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
Instead of using range
referencing in formulas,
apply named ranges to
simplify the formula.
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.