How to Write Percentage Formulas in Excel
Real Life Example: Manufactures Use Percentage Formulas to Track Key Indicators
I work as an accountant for a business that manufactures goods. We use Microsoft Excel to do our monthly reporting and analysis. EBITDA, Earnings Before Interest Taxes Depreciation Amortization, is our most important measure of overall business performance. Raw materials cost, as a percent of sales is probably our second most important indicator of how the business is performing. It tells us whether we are able to hold on to our profit margins as raw material prices fluctuates. We track other indicators such as material margin as a percent of sales, overhead as a percent of sales, gross profit as a percent of sales, and working capital as a percent of sales to name a few. Knowing how to write percentage formulas in Excel is critical in the business world. Percentage formulas are important because they provide a benchmark to allow you to see how sales price and cost changes affect the business. Looking at net sales and raw material costs for example, cloud the picture of what is going on. A Percentage formula allows you to compare several periods and take the noise out.
Businesses are not the only area to use percentage formulas. Any kind of statistical analysis will use percentage formulas. Political polling uses them to break down the poll information into a more useful form. Sports use percentages for all kinds of statistics such as completion percent (NFL), free throw percent (NBA, NCAA), etc.
Step-by-Step Instructions to Writing Percentage Formulas in Excel
Writing a percentage formula in Excel is easy. Click on the cell where you want to put the formula. Enter “=” and either enter a number or a cell reference that represents the number that you want to divide. Enter a “/” and either the cell reference or the number that represents the divided by number.
=75/100 = .75
Now format the cell as a percentage or leave it as a decimal depending on how you want it to look. To format it as a percentage go to Excel’s Home tab and click either the “%” button on the toolbar or bring up the “Format Cells” dialogue box by right-clicking on the cell, selecting “Format Cells”, and clicking on “Percentage.” Choose however many decimals that you wish.
Real Life Business Use Examples
Now that we have seen how to write a simple percentage formula, let us apply it to the real world. I use the percentage formulas that are listed below every month in our monthly financial reports. I spend the most time looking at raw material percent to tie what the accounting systems says raw material percent should be versus what the P&L actually says.
- Raw Material Percent – For a manufacturing business this is a key indicator that tells you if your raw material prices are rising or falling compared to your selling price. If it begins to increase, businesses will try to push through price increases to offset their costs. It is calculated as follows:
=[raw material costs] / [net sales]
- Material Margin Percent – This is the other side of the calculation for raw material percent. This looks at the amount of profit that a business makes based on sales minus raw material costs. If both of these calculations are used, the total percentage will add up to 100%. Material margin percent is calculated as follows:
=([sales] – [raw material costs]) / [sales]
- Gross Profit Percent – This is an important calculation that helps a business know what their manufacturing costs are as a percentage of sales. Obviously, the higher the percentage the more profitable the business is. Gross profit is net sales minus manufacturing costs. Gross profit percent is calculated as follows:
=([sales] – [total cost of sales])/[sales]
- SG&A Percent – This is a measure of all of the selling, marketing, research & development, and administrative costs of a business as a percentage of sales. It is calculated as follows:
=([sales] – [SG&A costs])/[sales]
Without percentages to compare to, reviewing financial information would be much more difficult because sales volume would skew the comparisons. Percentages easily allow financial information to be analyzed across periods.