Handmade Products Costing and Pricing with Excel

Description
Do you find it difficult to properly cost and price your handmade creations? Well, not anymore, it got easy with this Excel application which consists of 3 main sections:

1. Materials Costing sheet
Use this table to calculate the cost of all the materials that you use in your products. You can add new materials (press the Plus button at the top left), delete materials (double-click on the X on the right of each material), or change the data of existing materials.

For each new material, you need to type a name, input the amount and the units that you purchase, then the buying price, and then input the units in which you are using each material in your products. The app will calculate the cost per unit for each material and then use it each time you add this material to a product costing.

For example, check out the first material on the table: You buy 25 pieces of "Cardstock Neenah 80 lb" for $6.29 and you use it in pieces (25 uses). The cost per piece is $0.251600. An alternative way for the same thing would be to add it as "1 package" of "Cardstock Neenah 80 lb" for $6.29 but since 1 package has 25 pieces, the no. of uses would still be 25 and the cost per unit used would still be $0.251600.

Some materials that you buy in yards (example Tear Tape 1/4 in X 27 yds) and you use them in another unit (inches) you will need to add the converted unit in the "No. of Uses" cell: 27 yards of Tear Tape equal 972 inches. Thus, this material has a cost of $0.005134 per inch. If you use 0.5 inches of this tape in one production, then the cost of this material would be $0.005134 / 2 = $0.002567.

For other materials that are difficult to calculate their exact cost, the app uses the term "Uses" in the "Used In" column. For example K Gina Connect Glue. You buy 1 Tube of K Gina Connect Glue for $5.99 and it lasts for 200 Uses. So each time you use this material, it costs $0.029950. When costing a product, if you input that you will use this glue 3 times, then its cost will be $0.029950 X 3 = $0.08985.

Please feel free to add/delete/update anything on this table, from prices and buying-units, to no. of uses and used-in values, and make it as accurate as possible before starting costing products. If a material's price changes, or you buy it in a different quantity than before, or there are changes in the number of uses, you need to update the materials tables so the costings that currently use that material or the future costings that will use it, are accurate. When you change a price/quantity/uses of a material, you don't need to update any of the saved costings. If you open a costing that contains the updated material, its costing will be automatically updated and accurate.

Only in the case of changes in the "used in" units column, you need to check if you also have to update the quantity that you use in costings. For example, if you change the "used in" units for Sequins 5mm, from "pieces" to "teaspoons", then you need to check if the quantity that you use in some costings needs updating.

TIP 1: please avoid renaming materials after they are already been used in product costings. If you do need to rename a material that is already used in costings, you will need to re-open those costings one-by-one, re-select the material with its new name and then save the costings again.

TIP 2: you can store a material multiple times if you wish to use it in several different units. For example, you can have Sequins 5mm stored more than once in the materials table so you can use it in "pieces" and also in "teaspoons". Store it with different names like "Sequins 5mm (pieces)" and "Sequins 5mm (teaspoons)" and then select the one that suits best each product costing. Another example would be if you buy the same product from different suppliers at different prices. In that case, you could add the name of the supplier in the materials name, for example, Sprinkles (Amazon), so it will be easier to select it when costing a product.


2. Product Costing sheet
Use this sheet to perform your product costings. Follow steps 1 to 4 to complete each costing and when done, click the Save button to store the costing into the database, so you can re-open it for viewing or updating it.

First, you type a product name and then the number of units that will be produced in this project.

Then use the small plus button to add materials to this project. Use the drop-down list to select materials, and for each one, you only need to type the quantity used in this project.

Then set your desired rate per hour and the time required to complete this project, in minutes.

Then input your actual or estimated annual values for the fixed costs. Several tools go here, like stamps and brushes for example. Again, please feel free to alter this table according to your actual needs and values. You can remove an item by double-clicking on the X on the right or add new items by clicking the small plus button at the top left of this table.

An example for understanding better the concept of the fixed costs: for brushes, you need to calculate the cost of each one (or estimate an overall cost if you don't wish to calculate per brush), as follows: if Brush A costs $12 and lasts for 3 years, then its cost per year is $4. This is the value that you need to add there: the fixed costs per year for tools and other expenses that are related to your handmade business activity.

You also need to add the total (of all different products) estimated or actual annual sales volume (in items), so the app can calculate the fixed cost per item.

The final step is to type your desired profit markup and then the app will present you with the suggested selling price.

Don't forget to press the Save button, so this costing is stored in the database for future use.


3. Products Database sheet
This table hosts all the product costings that you saved. You can re-open a costing by double-clicking on its Product Name. You can also delete a costing by double-clicking on the X button at the right of each costing.

Each time you open a project, any materials that have updated costs (for example a price change), will automatically update the whole product costing. Each time you make changes in the Costing Step 3 (Fixed Costs), the Product Database sheet is automatically updated. Fixed costs are not set per product, they affect all your products and you don't have to input them for each new costing.


This Excel app is compatible with all PC and Mac systems that have a recent version of Excel installed (later than 2007). It is not compatible with the mobile versions of Excel (Android or iOS). You can open it for viewing in another Excel-like platform like Google Sheets, Numbers, etc, but you will not be able to create or save costings.

The price of the app is $60, one-time fee - no annual or other costs.

The app is also available in blue shades, for the same price!