Profit planning model

A Profit Planning Model

Downloading and using this model

This is an interactive model and must be downloaded to to a computer to be used. It was constructed using Microsoft Excel 2000 and it requires software that reads this program. Contact Three Sigma for assistance if needed.

[Download this Microsoft Excel 2000 spread sheet as a self-extracting executable file (.exe - 46k).]

Dont' have Microsoft Excel? Get a free Excel viewer from Microsoft; http://office.microsoft.com/downloads/2000/xlviewer.aspx

The Need for Profit Planning "Profit is a condition of survival. It is the cost of the future, the cost of staying in business". Peter Drucker
Profit is an essential cost of business activity and must be planned and managed just like other costs. Successful business performance requires balancing costs and revenues as illustrated by the following model. Costs of the future (profit) + current costs (expenses) = Average revenue per unit sold x sales volume (net revenue). The effective manager must make trade-offs among these variables to keep this equation in balance and this requires effective profit planning. A business must earn sufficient profit to maintain access to the capital markets for the investment it needs to grow and prosper. This profit can be difficult to determine but it cannot be less than the business' cost of capital.
Cost of capital is the cost the business must pay for its debt and equity financing. These minimum profit requirements enable the business sustain its current operations and maintain its wealth producing potential. A growth strategy may require additional profit to fund market and/or product research and development or strategic acquisitions. These profits come from the surplus generated from business operations or operating profit (also known as net income before interest and taxes).
Determining the Costs of the Future
To preserve and maintain the wealth-producing assets of the business, its return on equity must be competitive with other investment options available to stockholders and investors. Unless there are emotional or indirect financial incentives, this expected rate of return is based on the investors perceived risk associated with the investment opportunity. The range of investment options and their associated risks are shown in the following table.
	Investment options		Rate of return		Risk
	30-year U. S. government bonds		3-5 %		Low
	Stocks and mutual funds		8-13 %		Moderate
	Venture capital		25-30 % or higher		High
Investors and stockholders perceived risk associated with your business activity will determine their rate of return expectations. Failure to meet these expectations will dry up future financing capability and produce unhappy stockholders.
Complete the following matrix to determine your cost of capital and minimum operating profit requirements. This historical data loads your model.
Profitability should be calculated as an average of the profits over good and bad years. For this reason profit planning needs an historical perspective. 5 to 7 years of data provides a good profit planning horizon because this captures the up and down market cycle for many industries. 3 years of data is a minimum time period for meaningful planning unless the organization is new with a short performance history.
		Past Year 6	Past Year 5	Past Year 4	Past Year 3	Past Year 2	Past Year 1	Current Year
Enter the fiscal year
Net sales revenue (dollars)
Sales volume (units)*
Operating profit (dollars)
After tax net income
Interest paid on long term debt (dollars)
Owners/stockholders equity (dollars)
Owners/stockholders after tax rate of return on equity expectations (percent). See note below.
Average income tax rate (percent)
* If unavailable leave blank
NOTE: The actual after tax rate of return experienced by investors and stockholders can be determined by changing the rate of return throughout this row (up or down) until the current year cell in the CUMULATIVE OPERATING PROFIT SURPLUS OR DEFICIT row in the matrix below is close to zero. The rate of return value that causes this is the actual rate experienced by investors. This rate can also be a departure point for future rate of return expectations.
Cost of Capital and Profit Performance Trends Entries in the following matrix are calculated from the historical data you entered above.
		Past Year 6	Past Year 5	Past Year 4	Past Year 3	Past Year 2	Past Year 1	Current Year
Annual cost of capital and minimum operating profit requirement
Annual operating profit surplus or deficit
Cumulative cost of capital and cumulative operating profit
Cumulative operating profit surplus or deficit
Operating profit margin (percent)
Revenue per unit sold (dollars)
Complete the following matrix to determine your future operating profit requirements These are your financing and capital structure planning estimates
		Future year 1	Future year 2	Future year 3	Future year 4	Future year 5
Enter the planning year
Estimated interest expense on long term debt (dollars)
Estimated equity financing requirements (dollars)
Estimated dividend payout (dollars)
Estimated stock repurchase (dollars)
Owners/stockholders after tax rate of return on equity expectations (percent).
Average income tax rate (percent)
Future operating profit requirements These entries are calculated from your financing and capital structure estimates
Annual minimum operating profit requirement (dollars)
Annual operating profit requirements to eliminate previous years profit deficit (dollars)
Enter the following profit planning information These entries are your profit planning parameters
Estimated operating profit margin (percent)
Estimated revenue per unit of sales (dollars)
Planned operating profit (dollars)
Your profit planning results These entries are calculated from your profit planning parameters
Cumulative profit surplus or deficit
Estimated sales revenue needed
Estimated sales volume needed
Revenue change from previous year (percent)
Sales volume change from previous year (percent)

Contact Three Sigma for assistance with your profit planning