Author Topic: Excel files used for calculating material used in product, and material amount.  (Read 1439 times)

0 Members and 1 Guest are viewing this topic.

mogensbeck

  • Full Member
  • ***
  • Posts: 207
    • View Profile
  • Liked: 85
  • Likes Given: 12
We got tired of spending long time trying to calculate what a product costed in material from each time to time.
We made an excelfil where we can type in the materials we used for a custom/standard product so we would get a more correct price what the products costed to make.
We put the prices 30%ish more then what we normaly pay to compensate for waste in production and shipping of the material to Denmark.

Now we just type in how many centimeters webbing, amount of buckles, triglider ect.  And we can look at our cutting files and see how many squarecentimeters of fabric are needed and the excel file give us a material price for a product.



The other file we made was for our production so standard products would get their own material file. And we just type in how many we want to produce of the item and it would tell us how many and what sizes we need to cut with the automatic webbing cutter. But also tell us how much material we would need if a order was so big that we needed to order more material.


Hope it will give you some ideas to make a system that work for you.

essal

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 494
    • View Profile
    • Nora Tactical
  • Liked: 81
  • Likes Given: 44
I use my calculator to calculate the cost of the actual product.

BaseNO is the most up to date one, the others are just laying around on my server.
All measurements are in inches, pricing is in NOK which you probably want to change into what you pay per yard or item.

http://noratactical.com/files/baseNO.xlsx
http://noratactical.com/files/diytacticalcommunitymaterialcostcalculator.xlsx
http://noratactical.com/files/diytacticalcommunitymaterialcostcalculatorV2.xlsx

Feel free to use, modify and share whatever you guys come up with.
Nora Tactical
Product Technician - Norrøna

SR Tactical

  • Newbie
  • *
  • Posts: 25
    • View Profile
  • Liked: 4
  • Likes Given: 11
Thanks a lot essal. I just downloaded your Excel sheet and changed it to Euro and metric measurements, turns out my belts are just 8 cents off from my calculations and my estimated price I used so far.

Rainman01

  • Tactical Gear Creations
  • Jr. Member
  • **
  • Posts: 73
    • View Profile
    • Tactical Gear Creations
  • Liked: 9
  • Likes Given: 5
Thank you to all involved, I've been trying to create something like this for awhile but couldn't work out the formulas. Very much appreciated.


Sent from my iPhone using Tapatalk

WPJ

  • Full Member
  • ***
  • Posts: 234
    • View Profile
  • Liked: 18
  • Likes Given: 106
Thanks, interesting files to do some costing work on to see your total material cost per job.

essal

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 494
    • View Profile
    • Nora Tactical
  • Liked: 81
  • Likes Given: 44
Thanks, interesting files to do some costing work on to see your total material cost per job.
I found it quite the opposite; The interesting part was to see how much my time is worth.
Material is a cheap, even at the high rates that you pay for 330-500-1000D Nylon 6,6 fabrics compared to other fabrics. Once you start to value your time at even minimum wage in your country, it will most of the time be a much higher sum than your material cost.

Only thing I can think of where the materials end up as higher than the hourly rate is a very simple belt with a Cobra buckle bought at a retail price...
Nora Tactical
Product Technician - Norrøna

Gear Dynamics

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 963
    • View Profile
    • Gear Dynamics
  • Liked: 255
  • Likes Given: 246
This is a little different than the production calculations that mogansbeck posted, but I also use excel to track my inventory, as well as the individual cost for products. I figure I'd share an example of the latter, because as a small business, I initially struggled with breaking down costs.

As a preamble, my excel inventory book consists of sheets divided by type (buckles, webbing, fabric etc) and breaks down each item entered to provide a cost per unit (factoring in things like inbound shipping expenses). I also have four more columns that track the amount and dollar value of: A) what was started with, B) what was added to, C) what was used and D) what is left. Unfortunately that requires an inventory count at the end of the year, which is painful to say the least.

Using the cost per unit from the inventory, product sheets are created that have all the required info to build the product and calculate it's gross profit margins. Below is a sample that I whipped up to show how it's laid out. The numbers are just made up, but it gives you an idea of how the system works. There are some formulas (so you don't need to do math) that aren't visible, but they are all fairly basic to create once you understand excel. When the sheet is done, you can easily adjust numbers and it will correct itself. This is handy if material costs go up or down, or if you decide to charge more or less for the product.



Based on the image above, this particular product has the following info:

- Retails for $40 and wholesales for $21 (30% less)
- The most expensive variant is Ranger Green which costs $6.20 worth of material
- Based on a rate of 4 units/hr and hourly wage of $30, it costs $7.50 in labour
- This makes a total cost of $13.70 to produce, which yields a retail profit of $16.30 and a wholesale profit of $7.30