Spreadsheets for Business – Using Excel to Help with your Small Business Questions

---

If you know the price of something, you can calculate the cost that will give you a 30% margin as follows: Cost = Price × 70% (1 – margin %).

In business, the very existence of an enterprise is determined by its profitability. Making a profit can be an elusive process, requiring a good understanding not only of the Cost of Goods Sold (COGS) but also the functions performed to sell the product or service.

There are many models that help define costs so that profit margins can be properly calculated. Choosing the best model is critical to establishing proper pricing levels. The math involved in calculating a 30%, 40%, or 60% margin isn’t the difficult part of the process. It is determining the right starting point that requires the most scrutiny and clarity.

Easy to Say But Hard to Do

The first thing to consider is – have all the relevant and pertinent costs been included in the final cost calculation? This is where many companies become confused and the process of determining costs can become quite complex.

Unless the true costs of a product or service are known, any calculation related to profit will not be accurate. This failure to incorporate all relevant costs could end up costing a company dearly.

It’s All About the Budget Baby!

Putting together the costs associated with a product/service includes much more than the obvious costs (material, labor). These are the beginning point for the calculation, but there are other areas that should also be included in the formula. Consider the following:

Invoice Amount

Does the invoice include any discounts for early payment or penalties for late payment? Those numbers should be included in the costing formula.

Handling Costs

If the item is delivered or picked up, there will be a separate cost involved in its transportation. There are also the costs associated with handling the item after its receipt like inventory and warehousing expenses.

Cost of Money

Products that sit on the shelf for an extended period of time have company money invested in them that is not producing at income while it is tied up in inventory. This is known as an opportunity cost.

Taxes

Like death, taxes are inevitable and the costs involved in paying taxes on inventory or property held at the end of a tax year can add to the product’s true cost. Inventory that turns quickly isn’t as much of a concern as inventory which takes a long time to get sold or used.

Overhead

This is always a loaded question with an explosive answer. What is overhead and how should it be allocated? Calculating overhead accurately is a science unto itself. Determining how to allocate it can be problematic at best.

Fudge Factor

Also known as “budget override” or “other costs.” This is an amount added to other costs to make sure nothing is left out of the calculated amount. In most cases, this is an additional 2% to 5% to help cover any unexpected changes.

Add It All Up and It Spells True Cost

The cost of acquisition, handling, overhead, and other considerations have been added up. A final total true cost has been determined. Now what?

With all that formulation and processing, the resultant number is one you can have confidence in. Now that we’ve looked over what the true costs are we can finally multiply it by 30%, 40%, 60% and there’s the sales price. Right?

No.

Markup is not the same as margin. But, more on that in a bit.

Price is what determines margin

Knowing the true cost is the first step, but now it’s time to look at the selling side of the equation. Here are some other factors to consider:

Cash Flow

Is the product or service sold and paid for immediately or does it go on an account for 30 days or more? Are there discounts offered for prompt payment or pre-payment of invoices? Will the buyer earn volume discounts for large orders or is a discount earned over time based on volume?

Associated Costs to Deliver

Sales programs offering free freight or trips to Hawaii for sales associates are additional costs that should be estimated when determining overall profitability.

Negotiation Protocols

Some companies state their prices and no one challenges those prices; other companies state a price but they know the customer will be negotiating the price based on different issues. Allowances should be made to include any fluctuations involved as a result of negotiating the price for the product or service provided.

After-sale Follow-up

Once the product or service has been delivered the costs associated with the transaction aren’t done accumulating. After-sale follow-up by customer service, warranty expenses, and product administrative costs like safety notifications or updates add up quickly and should be taken into account.

Inflationary Issues

Not all inventories are subject to inflationary conditions but many companies find themselves confronted by the costs associated with wider economic issues. Issues that could alter the costs of inventory or services. Construction, manufacturing, and many other industries are constantly facing changes in local, regional, and national economic conditions that affect their business at its most basic levels.

Margin vs Markup

These are two terms that are often mixed. They are similar (even sound similar!) but they are not the same.

Margin can only approach 100%. Markup can be an infinite percent.

Markup is based on cost. It is calculated by dividing profit (gross, operating, or net) by cost.

Say something costs $1.00. If it’s marked up 30%, the price would be $1.30. If it’s marked up 60% the price would be $1.60.

Margin is based on price. It is calculated by dividing profit (gross, operating, or net) by price.

Say something costs $1.00. If it has a 30% margin, the price would be $1.43. If it has a 40% margin, the price would be $1.67.

Price = Cost ÷ (1 – margin %)

Here are some more comparisons of margin and markup:

Price	Cost	Margin %	Markup %
$1.00	$1.00	0%	0%
$1.43	$1.00	30%	43%
$1.67	$1.00	40%	67%
$2.50	$1.00	60%	150%
$3.00	$1.00	67%	200%
$4.00	$1.00	75%	300%

Retail, Wholesale, or Manufacturer – The Rules Still Apply

So, is a 30%, 40%, or 60% margin good?

It depends.

It depends on whether you are talking gross margin, operating margin, or net margin. It also depends on the industry and business model you are referring to. Every business and industry is different. What’s good and what isn’t can only be determined when comparing to an appropriate benchmark.

Some industries work with high profit margins and others work on minuscule margins.

Regardless of where an organization stands in the supply chain, the need to maintain profitability is important. Advanced accounting methods combined with software that can quickly search through data to extract the most important information makes the process of assigning costs much easier and much faster.

However, the need to incorporate all salient costs and related expenses can become burdensome and overly detailed if not monitored for accuracy and applicability. Traditional models have been replaced by highly-customized programs that reflect the conditions of individual companies rather than using industry-wide standards for calculating costs.

Protecting the Margin

Knowing the true and detailed costs of a product or service is critical in a competitive environment. If margins start to slip, most businesses will go to their suppliers looking to save money and maintain profit margins by asking for discounts. Or, they might take other cost-saving measures.

Any Way You Cut It, Margin Still Matters Most

The discussion over the difference between the terms “margin and “mark-up” are arguments over the same thing – profit. Unless an organization is a non-profit, its goal is to make a profit and to do so for as long as possible.

Net profit is often called the “bottom-line” and it still defines an organization’s character and capability to many. Every financial analyst, stockbroker, and business columnist focuses on a company’s ability to not only generate a profit margin but to do it repetitively and under a variety of conditions. Knowing the numbers and figures that determine the actual costs for a product or service leads to the opportunity to produce the desired margin more readily and realistically.,