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.

Concentrate on Coaching and Organizational Goals in Employee Feedback

What companies should communicate to employees about employee performance is a major area of management research and analysis.  Searching the Internet and academic article databases on such terms as employee, performance, evaluation, strategy, and alignment will find hundreds of examples of this research, analysis, and discussions about employee performance feedback.  Much of what is found in recent information recommends changes in the traditional supervisor to employee evaluation, assessment, and rating schemes in practice for dozens of years.

One article found at the Bersin & Associates website provides what seems to me as an excellent overview of the changes being recommended in employee performance feedback.  Click here to read this article.  This article does not eliminate the traditional supervisor to employee performance assessments, but indicates that this traditional practice is only one of the goals of employee feedback, and is a less important contributor to the company’s success than other goals for employee feedback.  These other goals deal not with past performance of the employee, as traditional assessments do, but with aligning the employee’s future performance in ways that support company strategic goals.

Therefore, the major objective of supervisor to employee feedback and supervisor and employee goals for this feedback is not past performance but future directions.  The supervisor now has the role of serving as a coach moving the employee in the direction of acquiring the skills, understanding, motivation, and activities aligned with the company’s strategic goals.

Consider as an example to demonstrate this forward-looking, coaching-management perspective of supervisor-employee feedback, a small company and its accounting function.  Assume the company has three strategic goals:  increased revenues; increased profits; and better customer and vendor relations.  The accounting department would then develop a set of goals for its function that support the company’s strategic goals.  These accounting goals might be: customer and vendor evaluations and analysis; cost analysis; and sales analysis.  Now supervisor to accounting personnel feedback sessions will have a new objective – the needed individual actions, competencies, and analysis to meet the three accounting department goals (which are tied to the company’s goals).

The traditional supervisor-employee work assessments still are needed, but with a lot less importance.  Its importance is now primarily only to screen out those employees who need to be terminated.  All other employees are accepted as worthy in the ways that they are worthy.  These employees are given encouragement and coaching to increase their worthiness, and are asked to set personal goals related to the accounting department’s goals.

Return on Equity – an Excellent Metric for Decision Making

The return on equity is an excellent measurement for small businesses to use to monitor how their businesses are going.  The return on equity is determined by dividing the net income for the year by the average equity (beginning equity plus ending equity divided by 2) for the year.  This Wikipedia site (click here) provides more on the definition.

Searching the Internet can find commentary on the value of the return on equity measurement.  For example, an article by Timothy Vick associates success of many companies with a high return on equity percentage.   Click here to read this article (PDF file).  Another article, by Richard Teitelbaum, Kimberly McDonald, and Ed Brown, does the same.  Click here to read this article.

Researchers have shown a relationship between a company culture, as defined by the researchers, and higher returns on equity.  Lorraine Eastman, Christopher Kline, and Robert Vandenberg show that companies with certain cultural characteristics have higher returns on equity.  Click here to read this article (PDF file).    Richards Barrett demonstrates the same conclusion.  Click here to read his information (PDF file).

The return on equity measurement is easy to make.  Net income is quickly obtained from the profit & loss statement and average equity can be computed from the equity balances at the beginning and ending of the year, obtained from the balance sheet for those dates.

Another useful return on equity attribute is that three other measurements, net income as a percentage of sales, sales as a percentage of total assets, and total assets as a percentage of average equity, can be targeted for improvements.  As each of these ratios are improved (increased percentages), the return on equity will increase.  This is because (net income/sales) X (sales/total assets) X (total assets/average shareholder equity) equals net income/average shareholder equity (which is return on equity), when the numerators and denominators in the equation cancel one another out.  So, a company can target anyone, or all three, of these percentages for improvements, and by doing so, will increase the return on equity.  Such actions as reducing costs while maintaining the same sales amount or selling assets but maintaining the same sales amount will increase the return on equity.

Companies should stride to increase their returns on equity. This site (click here), maintained by Aswath Damodaran, at New York University, provides average returns on equity for about 98 business sectors.

Save on Airline Tickets with Advanced Purchases

Significant savings can be obtained by booking airline tickets in advanced.  A 2012-published survey by Egencia found that advanced purchases of airline tickets to several destinations could result in savings from 11% to 47%.  Purchases were at least 22 days in advance compared to near term travel purchases.  Click here to read the data in this survey (PDF file).

In Priceline (click here), I found that I could save from 31% to 87% if I purchased round-trip tickets from Baltimore to 4 destinations (Boston, Chicago, Denver, and Miami) four months in advance rather than a few days in advance.

However, special factors, such as advanced bookings to a destination when a seasonal or

well-publicized event is happening during the desired travel dates, might reverse this finding to where a purchase price is higher than lower for an advanced booking.

I also found that there was no material difference in purchase price whether through Priceline or directly from the airlines at their websites.   But, using Priceline is very convenient because several airline prices are shown in one search, from which the best price can be chosen.  Otherwise, each airline has to be searched separately.

No airline consistently had the lower price.   Airlines recognized as being low price carriers did not have more “lowest” prices than other airlines.  There was great variability between the airlines in who offered the lowest price.   Also, there was often a wide range between the lowest price and the highest price.

According to the Aberdeen Group report entitled “Travel & Entertainment Expense Management – Reducing Processing Costs & Improve Policy Compliance”, travel and entertainment expenses, on average, account for between 8% and 10% of total operating expenses.  The report provides several recommendations for a more efficient and effective management of a company’s travel needs.  As this report and the above data on advanced bookings suggest, policies and procedures related to a company travel needs can lead to significant cost reductions.  (Click here to read the Aberdeen report.) (PDF file.)

Determine Marketing Effects on Retail Sales Using a Control Chart

Small retailers should consider the use of a control chart to help determine whether a marketing campaign, such as offering discounts or gift cards, has any effects on sales.  Control charts can be used to show where a statistically significant variation from a normal pattern of events exists.  Daily sales totals are an example of a normal pattern of events.  If the daily sales totals increase by a statistical significant amount above the normal variation in sales from day to day, at the same time that a marketing initiative is implemented, the increase is likely due to the marketing initiative.  If no significant variation is seen, then the marketing initiative likely has had no effect on increasing total sales.

Control charts creation and use do involve some complexity, and therefore time is required in learning about and dealing with the complexity.  Certain aspects of the use of control charts and how best and how not to apply them can be beyond the abilities within a small retailer’s shop.  However, I believe other uses of control charts, for example, in charting daily sales against statistical significant limits variability for sales, could be within the resources that a small retailer might want to expend.  One reason for this is that accounting software, such as QuickBooks, can easily export daily sales data to Excel.  Once in Excel, a control chart, based on that daily sales data, should be relatively easy to create, after the initial understanding of how to do so is learned.

I did some Internet research to find a few websites that provide introductions to a basic use of control charts, using Excel to create control charts, and how control charts can be used with daily sales data to show effects, or lack of effects, of an external factor, such as a marketing camping, on the sales data.

This link (click here) takes you to a CPA Journal article that provides a basic definition of a control chart and what can be shown on a control chart.  Data points falling outside of the upper and lower control limits show that likely something is affecting the data other than normal variation. 

This link (click here) takes you to a ProfTDubYouTube video that demonstrates how to create a control chart in Excel.

This bizmanualz site (click here) uses a series of weekly sales data to demonstrate the various computations that are used in creating the average line, the upper and lower control limits, and the moving range average.  Graphs are used to show how these computed values are used on a control chart to reach conclusions about the graphed data.

Two websites show well how a control chart can demonstrate that graphed data is being affected by external factors.  Click here to go to a BPI Consulting site.  Go to the bottom to see how a control chart shows that on-time airline arrivals and departures have improved as of a certain date due to changes made by the airlines. That several consecutive data points fall on the same side of the average line indicates a non-normal variation.  The biamanualz site above provides more on this consecutive rule.  

The second website (click here) has an article written by Australians Phil Cohen and Onno van Ewyk that provides a clear explanation of how data points on a control chart might show unusual affects on the data.

My blog article here focuses on one use of a basic control chart – to show how daily (periodic) sales might be affected by an external event such as a marketing campaign.  Many more uses of control charts exist.  Their uses to show information about processes is only limited by the number of processes that exist and the need to gain some insights about those processes.

This article also deals with a very basic statistical used of a control chart, such that smaller retailers might be able to use a control chart with available resources.   Much more sophisticated uses of control charts, requiring much more sophisticated computations and statistics, have been developed, which also greatly extend the uses of control charts.

A lot can be found on the Internet about these additional uses of control charts.

Use Government Websites to Help Manage Record Keeping

Federal and state governments have record keeping requirements that small and large businesses are expected to meet.   If record keeping requirements exist, a government website almost certainly will identify, explain, and provide other details about the requirements. 

A good strategy for a company is to establish the correct record keeping policy, which it seems to me means spending enough resources on keeping the records to meet the company and government needs without spending more than the necessary resources.  In establishing this policy, the company should use the Internet to go to the government websites dealing with record keeping requirements in order to clarify with certainly what is needed and what is not needed.    Knowing the requirements at these websites will help to insure that sufficient, but not excessive, resources for record keeping are used.

Many functions in a business operation may, or do, require record keeping.  Some functions such as sales and employing workers have record keeping requirements for all businesses.  Other functions such as transportation, export-import, and safety-related activities have record requirements for those companies involved with the functions.   Regardless of the business operations that exist, government websites will provide the information needed to help optimally manage the record keeping requirements.

Provided below are some links to government websites that demonstrate what is available at government websites.

Sales, revenues, and income.   The Internal Revenue Service has established record keeping requirements related to these business functions.  And, the IRS has websites that explain these requirements. Click here and here to go to two such websites.

Employee records.  The US Department of Labor oversees federal record keeping requirements for hiring, paying, terminating, and other activities related to a company’s employing workers. Click here to go a DOL site from which employee record keeping requirements can found.

In addition to federal record keeping requirements related to income and employees, and other business functions, states may also have additional requirements.  State government websites will have these requirements.  As part of the company strategy to develop an efficient and effective record keeping policy, these state websites should also be researched.