|
|
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 |
| HOW DO I KNOW MY PRICE IS RIGHT? |
 Set your rates using
How to Price Graphic Design & DTP Services ($32.95)
or Pricing Web Services ($34.95)
These books clearly explain how to set prices that work. (Both books also include tips on estimating, bidding and
negotiating jobs.) |
|
|
Make a Cost & Overhead Analyzer Spreadsheet ©1997-2003 Brenner Information Group continued After skipping row 72, we then
generated the Direct (Project) Costs section. These are costs that can be directly associated with
a project. We used rows 74 through 82 to list the direct cost categories. Row 83 contains the totals
for this section (TOTAL DIRECT COSTS) and contains formulas similar to rows 37 (total fixed costs)
and 71 (total variable costs).
We skipped row 84 and labeled A85
"TOTAL ALL COSTS." In cells B85 through M85 we totaled the total cost entries for each column.
Thus B85 contains "=SUM(B37+B71+B83)" and so on across and including cell M85. Cells N85
and O85 contain the same formulas as found in cells N71 and O71.
Skipping another row, we next
calculated the overhead for each month. Now overhead consists of those costs that exist and
cannot be allocated to a specific project. In our spreadsheet, overhead includes the fixed and
variable costs. Thus A87 is labeled "MONTHLY OVERHEAD" and cells B87 through M87 contain
the sum of the total costs in row 37 and row 71. For example, B87 contains the formula:
"=SUM(B37+B71)" and likewise across row 87. Cell N87 contains the total monthly overhead
values for cells B87 through M87.
On row 88, we calculated a running
average of overhead using a tricky formula. To do this, we had to overcome the fact that dividing
by 0 results in an ERR message. So we developed a counting scheme in a hidden portion of the
spreadsheet. We used cells C115 through C126 for this special formula. In C115 we placed
"IF(B87>1,1,0)" which says that if the contents of B87 are greater than 1, then the result is 1.
Otherwise the result of this logical statement is 0 and C115 will contain 0. The formula is repeated
down through cell C126. For example, C126 contains the formula: "IF(M87>1,1,0)" representing
data or no date for the month of December. In C127, we totaled the 1s and 0s in C115 through
C126. Thus, if January, February and March had total data in their cells, C127 would contain 3.
Back in row 88, we then used a
formula that looked at C127 to calculate a running average for each month. Cell B88 (January)
contains: "IF(B87>1,B87/C127,"")," which means that if there is an overhead number in cell B87
(any number is greater than 1), then the contents of B88 should be the result of dividing B87 by
C127. Now for February and out through December, we need to sum up all the monthly overheads
and then divide by the number of months. Thus C88 contains: "IF(C87/1,SUM((B87+C87)/C127,"")"
which means that if there is data in cell C87, then add up both B87 and C87 and then divide by
the contents of C127 to get a new running average. If C87 has not yet been filled in, then keep
C88 blank. As you can see, the average overhead changes as you complete entries for follow-on
months throughout the year because it incorporates each new month with the overhead values
for all the previous months to generate a running average of all the overhead costs. It's displayed
as "Average Monthly Overhead" so you can compare it with the previous row "Monthly Overhead"
and detect trends or special seasonal costs.
Next we went after the hourly
overhead. By assigning row 89 to contain the number of hours worked each month, you can
generate an "Hourly Overhead" cost for your shop. Thus A89 is labeled "Hours Worked (all)" and
cells B89 through M89 are for entering the hourly figure. in row 90, we calculate the hourly
overhead by dividing the monthly overhead by the number of hours worked. To keep the cells
empty until data is available, we used the logical IF statement again. Thus cell D90 contains
"(=IF(D89>1,D87/D89,""). "
And, just as with the "Average
Monthly Overhead" formula, we then dedicated the next row to calculating an "Average Hourly
Overhead" on a moving average basis. This is useful in calculating a budgeted hourly rate or
cost and in determining how much you should charge for your services.
This cost and overhead spreadsheet
lets you quickly analyze expenses on your own computer screen. It also lets you immediately
see the affect and effect of any cost reductions that you make or are considering. With your
spreadsheet, you should be able to produce a number of different graphs of any row or column to
show visually the cost and overhead trends in your business. With some spreadsheets, you can
also perform statistical analysis to determine the existence and extent of relationships of data.
Once you have your own cost and
overhead calculator, you'll find it your favorite business analysis engine. It takes up two and a
quarter sheets of paper when printed in landscape (wide). We added color to make the display
and printout more interesting and readable. If you want a canned solution to cost and overhead
analysis, we'll send you our complete spreadsheet as an e-mail attachment for just $15. It will
be unprotected so you can modify and customize if for your own business.
<Click Here To Order> <Real Prices Confidential description page>
|
|
©Copyright 2001-2003, Brenner Information Group, All rights reserved.
revised on July 1, 2003
