Tuesday, November 16, 2010

A Model on MS Excel to Factor in Fees into your Profit - Guest Post

Hi, This is Som from Som’s Studio!

When we run a business, the profit we make, is key to our success. If you have a store on Etsy and accept payment via PayPal, it is very important to factor in the fees you pay while calculating the profit you actually make.

To demonstrate my point, let me start with a simple model on Excel. If you can add, subtract, divide and multiple…trust me you will be able to make this model work for you.

In our case, let me be a seller on Etsy (which I am!) selling a pair of earrings. The Cost Price (material, labor, overheads etc) in this example is $8.00, and consequently I have priced the Selling Price (SP) at $16.00. So my total Sales (also called Profit) is Selling Price (SP) – Cost Price (CP) = $16.00 -$8.00 = $8.00.

I have opened an Excel document and typed in the data and have applied this simple formula under CellF4.

(Click on pics to enlarge)

Now the Profit percentage is calculated as (SP-CP)/CP *100 = ($16.00 -$8.00)/ $8.00 *100 = 100%.

I have applied this same formula on Excel, but to simplify have changed the format of Cell G4 to “Percentage”. To do this, right click on the Cell G4, Select “Format Cells”, and find “percentage” under the “Number” tab.

Now, to calculate the fees. Etsy charges a fee of 3.5% of SP and a fee of 0.20c per listing.

PayPal charges 2.9% of SP and a fee of 0.30c per transaction.

(Click on pics to enlarge)

So total fees amount to $1.52.

Total profits goes down from $8.00 to $6.48.

Profit percentage goes down from 100% to 81%.

In another example (below), both Earrings B and C have a CP of 0.50c. Earrings B priced at $1.00, show a profit of 100% before fees, but a loss of 13% after factoring in fees. Earrings C, in contrast shows a break-even point when SP is $1.07.

This simple model can easily be applied to sale of any product, and can be modified to include additional expenses to reflect true profits.

Have a happy Tuesday!

3 comments:

  1. Wow...this is great information and so clearly explained. Many thanks...now to get used to Excel! Patti/MissouriBendStudio

    ReplyDelete
  2. Awesome info! I've been meaning to start a spreadsheet like this. Thanks for the instructions. Hopefully reading this will kick me into gear!

    ReplyDelete
  3. Wow ... what a great detail tutorial !!!
    Thanks for posting this for all of us !!

    Happy sales!

    ReplyDelete