Pricing Decisions for Professional Service Providers Using Excel’s What-If Analysis Tool

(Salary amounts modified on August 1, 2012 due to error discovered in initial computations.)

A problem for small professional services companies in starting up their businesses can be what price (fee) to charge for the services provided.   The demand for the services and what other similar (same experienced, skills, etc) providers charge are often uncertain.  Using Excel’s What-If Analysis Tool can provide a baseline fee level for various demand hours (total billable hours) at an assumed variable cost and fixed cost (including an expected salary) and at a breakeven point ($0 profit).

A lowest baseline fee level needed to reach a certain salary at an assumed demand, variable and fixed costs, and profit = $0 (breakeven point) should be useful for professional service providers to know.  Knowing a lowest baseline fee level removes some uncertainty about the lower limit of a fee to charge.  With this knowledge, the service provider should be more confident that any fee below a fee determined by using the What-If Analysis Tool will not provide an expected (needed) salary.  This does not mean that this lowest baseline fee is the fee that should be used.  What is met is that the fee is the lowest fee that should be considered.  Considering higher fees depends on many uncertain factors, not the least of which is demand at various fee levels, which a tool like What-If Analysis will not be able to provide much help with.

Using Excel’s What-If Analysis, I was able to show, at 1,200 hours of demand (total billable hours), a fixed cost of $10,000 (rent, utilities, office expenses, etc.), a variable cost equal to 30% of revenues (which corresponds to US Census survey data that shows that professional service companies have an average 70% gross profit margin percentage), and at a breakeven point (profit = $0), the following:

1.  With an additional fixed cost of $30,000 (salary), the fee needs to be from $45 to $50 per hour; at an additional fixed cost of $40,000 (salary), the fee needs to be from $55 to $60 per hour; at a additional fixed cost of $50,000 (salary), the fee needs to be $70 to $75; and at an additional fixed cost of $60,000 (salary), the fee needs to be from $80 to $85 per hour, in order to reach the stated salary. 

2.  But, decreasing the demand to 800 hours, the corresponding required fees for the above stated additional fixed costs (salaries) need to be:  $70 to $75; $85 to $90; $105 to $110: and $125.

So, if a professional service provider expects a $50,000 salary and will have a demand equal to 1,200 hours, and has $10,000 of other fixed costs (in addition to the salary expectation), and assumes a 70% gross profit margin percentage, a fee of $70 to $75 per hour will obtain the expected $50,000 salary.  Also, it is possible that a professional service provider will be very intolerant of continuing in business at a salary level lower than $30,000 to $40,000.  If yes, a minimum fee for such a service provider would be $45 to $60 per hour, given the above stated conditions.  (In other words, a $45 to $50 per hour fee provides a $30,000 salary, at the conditions stated.)  This fee range would go down at higher demand levels, and up at lower demand levels, again, given the conditions stated above. 

In addition to suggesting a lowest baseline fee to use, the What-If Analysis Tool can generate demand (number of total billable hours) levels that will provide a certain salary, at a fee amount.  One possible use of such data might be adjusting a fee on a year to year basis.  For example, if after year 1, a certain salary is achieved at one fee and demand, What-If Analysis results will show how much a drop-off in demand can be tolerated in the next year when the fee is raised (assuming a raise will lead to a lower demand) yet obtain the same salary as  in the previous year.  

For those interested in using Excel’s What-If Analysis Tool (available within standard versions of Excel), many websites can be found that provide information on using the tool.  One such website (click here) provides instruction, by Wayne Winston, on setting up and using What-If Analysis, with an application description similar to the application discussed above.

The analysis that I did with Excel’s What-If Analysis Tool, a summary of which is provided above, I believe shows that the tool can be very useful in decision making.   Some thought has to go into the details of what is desired.  Then the tool needs to be tested based on the analysis of these details to determine if useful data is generated that helps in decisions that needs to be made.