EXCEL TEMPLATE THAT CALCULATES PRODUCT PRICES

B

bobnewmark

I am looking for a template that allows me to calculate a selling price when
i posess known cost factors such as: Cost of Product, Freight In, Handeling,
Storage, Freight Out, Overhead %, Gross Profit Margin%

It would be great of I could calculate several (up to 5) prices on one
worksheet
 
J

JLatham

Create your own. In its simplest form you would enter the values for your
'fixed costs' into the first 5 cells of a row (cost of product, freight in,
handling, storage, and freight) So that fills up A1 through E1
in F1 you could put a sub total of those costs as =SUM(A1:E1)
in G1 calculate your overhead as =F1 * .15
assuming a 15% overhead cost.
in H1 you can put another subtotal formula as
=F1+G1
calculate your profit on all of that in cell I1
=H1 * .25
then finally, in J1 you calculate the sell price:
= I1 + J1

There are ways to close some of these up, combine them and make it all take
less room across a sheet, but this gives you a pretty good view of what you
probably want and need to see. Just make sure that costs for things like
freight in/out, storage are all based on the same unit. Example: You'd have
to calculate the individual share of the cost of storage of a case of
something when you want to compute the single unit sell price.

You could also put your overhead and profit margin values in just one cell
each and reference that cell in your formulas. That way when your overheads
change or you decide to try to make a little more profit, you can update
those numbers in one place and they get updated everywhere else.

After entering that first row of cells, you can extend all of the formulas
down the sheet and they'll automatically adjust themselves to calculate based
on new fixed cost entries in the first 5 cells of each row.

You can also even insert a new row above row 1 where I had you place the
formulas and enter column titles to explain what's in the cells in the
columns.

You can also check the Microsoft Office templates to see if they have one
available:
http://office.microsoft.com/en-gb/templates/default.aspx

And as a last resort, I've uploaded a working version much like the one I
described above here (not pretty, but would do the basic job):
http://www.jlathamsite.com/uploads/PriceCalculationTemplate.xls
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top