Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default EXCEL TEMPLATE THAT CALCULATES PRODUCT PRICES

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,203
Default EXCEL TEMPLATE THAT CALCULATES PRODUCT PRICES

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/te...s/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/P...onTemplate.xls


"bobnewmark" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Default Excel file and template Mark McDonough Excel Discussion (Misc queries) 2 May 26th 06 02:50 AM
excel template wizard Judy Excel Discussion (Misc queries) 6 December 8th 05 10:13 PM
Excel template to load automatically as the default template? David Excel Discussion (Misc queries) 1 March 21st 05 12:24 PM
Merging data from an excel worksheet into an excel template pkasler Excel Worksheet Functions 1 March 13th 05 04:36 AM
I get a program error when I download an excel template Ladybug Excel Discussion (Misc queries) 3 March 4th 05 12:02 AM


All times are GMT +1. The time now is 06:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"