How to Calculate Profit From Gross Sales and Projected Sales in Excel
Gross sales encompasses the total value of money made by the business prior to subtracting the cost of sales, discounts allowed to customers, inwards returns of goods, taxes and expenses incurred in the operations of the business. Cost of sales and the returns inward are subtracted from the gross sales to determine the gross profits. The cost of sales for an accounting period is calculated by subtracting closing stock from the total of opening stock and purchases. Operating expenses and taxes are subtracted from the gross profits to determine the net profits. Projected sales are the estimated amount of money the business expects to raise from the sale of its products at a future date.
Other People Are Reading
Instructions
-
-
1
Open an Excel spreadsheet and label the first column "Description." Position the cursor of your computer in the line between column A and B on the top of the Excel sheet and pull it toward the right side of the Excel worksheet to resize the column.
-
2
Skip column B and C in your spreadsheet and label column D as "Totals." Skip the second row and write the word "Sales" in the Description column and insert the total sales figure for the year in the "Totals" column. When calculating profits from gross sales, your total sales should comprise the total invoices and cash receipts for the period as indicated in the day sales book and the general ledger. When calculating profits from projected sales, your total sales will be the total sales revenues recorded in the budget of your business.
-
-
3
Skip one row after Sales and write the words "Cost of Goods Sold" in column B. Write the items "Opening Stock," "Purchases" and insert the total amounts of each of the two items in column C. Write the items "Closing Stock" and "Returns Inward" in the rows right below "Purchases" and enter the total amount of closing stock and returns inward as negative figures.
-
4
Click on the "Totals" cell that lies adjacent to the "Closing Stock" figure. Press the "=" sign and then click in the cell with the "Opening Stock" amount. Press the "Shift" key, followed by the "+" sign and then click on the "Purchases" amount. Press the "Shift" key again, followed by the "+" sign and then click on the negative amounts of "Closing Stock" and "Returns Inward" and press "Enter." The result will be your cost of goods sold.
-
5
Skip one cell below the amount for cost of goods sold and click on the next cell. Press the "=" sign and then click on the "Total Sales" amount in column D, then press the "-" sign and press "Enter." The difference will be your gross profits.
-
6
Skip two rows below the gross profits row and list all the operating expenses in column B and write their respective amounts in column C. Such operating expenses include wages, electricity bills, water bills, insurance premiums and sundry expenses. Click on the cell adjacent to the last cell with operating expense amount, click on the equal "=" sign and then click on the amount of the first expense item. Press the "Shift" key followed by the plus "+" sign and then the click on the amount of the next expense item and repeat the procedure until the last expense item and click "Enter." This will be your total expense for the period.
-
7
Skip one cell below the total expenses and click on the next cell. Press the equal "=" sign and then click on the "Gross Profits" amount in column D, then press the minus "-" sign and then press enter. The difference will be your net profits before tax.
-
8
Write the word "Taxes" in the description column below the net profits before tax. Enter the total amount of tax as a negative figure. Click on the cell below and click on the Auto Sum function. The result will be your after tax profits.
-
1
Tips & Warnings
Projections may not reflect the real situation because sometimes they might underestimate or overestimate real sales, hence affecting expected profit.
Do not combine your tax computation with your calculations for expenses because such an approach will complicate your computation of deductible taxes.
