|
|
The world's largest collection of prices and pricing related support tools for the desktop professional |
|
current rates for writing, editing, graphic design, DTP, prepress, multimedia and web design and development |
| THREE FOR ONE PRICE - BUY THE BRENNER BUNDLE! |
 Get ANY THREE of these four books:
(How to Price Graphic Design & DTP Services,
Pricing Web Services),
Regional Pricing Tables,
and Desktop Production Time Standards
for ONLY $99.95 (a 40% discount or more) |
|
|
Make a Cost & Overhead Analyzer Spreadsheet ©1997-2003 Brenner Information Group
Here's how to construct a useful spreadsheet for tracking and controlling costs. (If you like, you
can purchase [Order #001] this worksheet as an Excel file.) This analyzer is contained in our Desktop
Production Standards reference package, but we also offer it here for you to build yourself. We
developed the model using Excela, but you can use any
current spreadsheet program. What's nice about this is that, besides having a working analysis
tool, you can modify and add to it as you see fit. You can customize it for your own business.
The first step is to list all of your
expenses on a sheet of paper. Partition them into fixed and variable costs. Fixed costs are those
expenses that don't change each month. They occur just because you're in business. You pay
these costs each month regardless of the volume of business that you have. Variable costs are
those expenses that are not necessarily the same each month. These include wages for direct
labor, indirect labor, part time and freelance help. Fixed and variable costs become the two main
sections of your spreadsheet. A third section deals with the costs that can be directly attributed
to project work. Overhead is comprised of your fixed and variable expenses, but it's good to know
(and track) all of your costs.
Once you have all of your known
and expected expenses listed, you can begin to build your computer spreadsheet. Here's how
we designed ours.
We titled the worksheet "Cost &
Overhead Analyzer." It contains 15 columns (A - O) and 91 rows (1 - 91). Column A contains
headings and cost categories. Columns B through M contain data for each of 12 months. Column
N contains the cumulative costs for each row. Column N contains the percent each cost row is of
the total costs.
Across the top row we centered the
title. We skipped a row and then on the second row, in column B, we entered the label "year."
Column C of row 3, we allocated to entering the year. Then we skipped some rows.
Row 7 contains the labels, "Fixed
Costs," the months "Jan" through "Dec" and then Annual Cumulative (N7) and "% Total Costs"
(O7). Annual Cumulative is where you'll collect the total cost that you pay for each cost category.
The data in "% Total Costs" will show what percentage each cost is of the total expenses paid by
the company. This lets you quickly see where your largest percentage of costs are and to compare
your business with other businesses in the same industry.
Now, down column A enter all of
your known fixed costs. This includes rent, insurance, taxes, fees and licenses, leases, salaries,
telephone and on-line costs. Our list used rows 8 through 35. On row 36, we put the label other
as a catch-all for fixed costs we may not have thought of. Then in A37 we placed the label "TOTAL
FIXED COSTS." Across the row in columns B through M, we placed a formula to add rows 8 through
35 in each column. For example, B37 contains the formula "=SUM(B7:B36)" and M37 contains
"=SUM(N7:N36)."
To calculate the annual cumulative
data, we added each row from column B through M. For example, in N7, we placed the formula
"(=SUM(B7:M7)" and in N37, we have "=SUM(B37:M37)."
We wanted to keep the percent total
costs cells blank until a row had cost data entered, so we used the formula that would not only
determine what each row of costs represents of the total costs, but also would be empty until
data was entered in any of the cost cells in that row. So, O7 contains the formula:
"=IF(N8>0,N8/$N$85,"")." This logical statement says that if there is data in the annual cumulative
cell (N8), then divide the value in N8 by the value in N85 (where we collected the total costs for
the whole sheet). If N8 does not have any cumulative data, then place a null (no data, blank) in
O7. This formula was repeated down column O.
We skipped row 38 and entered
"Variable Costs" in A39. ." Variable costs are those that are not necessarily the same each month.
These include wages for direct labor, indirect labor, part time and freelance help.
We copied the month labels (Jan
through Dec) and "Annual Cumulative" and "% Total Costs" from row 7 and pasted these into B37
through O37. Next we entered all the known variable costs that we could have. This includes
wages for part time and freelance help, payroll taxes, marketing and advertising, workers comp
insurance and many more. Our list takes up rows A40 through A70.
In A71, we put the label "TOTAL
VARIABLE COSTS" and totaled the variable cost for each month in B71 through M71. For example,
in B71 we used the formula: "=SUM(B40:B70)" to do the summation. The formulas in row 71
(total variable costs) are similar to those in row 37 (total fixed costs). Thus this section of the
spreadsheet totals the variable costs for each month and generates cumulative and percent
information in the two rightmost columns.
<Click Here To Order> <Real Prices Confidential description page>
|
|
©Copyright 2001-2003, Brenner Information Group, All rights reserved.
revised on July 1, 2003
