Create an Easy Custom Formula in Excel to Price Items

I wrote a while back about taking inventory of several of my supplies. Today, as I waited not-so-patiently for new supplies to come in, I decided to take the time to update and review my pricing and profit margins. Once upon a time, I had imputed information into Excel to easily see my costs. For example, I had done the math to find out how much 20 beads cost, the number that fit around one ring for my wine charms.

Well, since then I have added new types of embellishments and had to change old ones (switching suppliers or brands and the like). It was time to make sure I was still on the straight and narrow.

Unfortunately, I found that my old spreadsheet was a mess. Sure, it had information in it, but it didn't calculate anything by itself, and really, isn't that why we use Excel? So it will calculate things for us?

After some time searching online and experimenting, I finally figured out how to create a mini-spreadsheet with a built in formula to quickly redo the math anytime my costs change. Turns out it's ridiculously easy. I'm using an Excel 2004 Mac version, but I'm pretty sure Excel doesn't change much.

(These number are made up for the sake of example)

The first thing I did was type out the information I wanted to know (materials cost/profit with various fees, etc.) onto the left side of a set of boxes I set off with dark lines. The lines aren't necessary. It looks like this -------------------------->

Then it was time to create the formula. Most of my items are $9, $12, or $15, so I created a Master Formula for each to copy/paste easily later. This is my example for the $15 item.

I wanted to make it easier to see and modify the formula I was creating, and since the formula bar wasn't at the top of my page, I had to make it visible. To do that, I went into View at the top of the page and checked it off. Looks like this -------------------------->

Without the formula bar, you can't change things later, like adding or deleting how may items you want added together for the Materials Cost.





Once the formula bar was turned on, this popped up at the
 tip of my page ------------------------------->

"What's the formula in the bar?" you ask. That's a good question. Look up at the first picture. See where it says "Material Cost" and next to it 7.1? Well, I clicked into where it says 7.1 when it was an empty box. I then went up to the formula bar and typed in my formula (I could have typed straight in the box, too, and it would have disappeared to insert the answer).

FORMULA:

1. Type an = sign. This tells the computer that it's going to put in an answer
2. Type a ( without any spaces between it and the = sign. This tells the computer this is the start of the formula.
3. In the example above, G equals the Column Letter (look at the top of your page. It could just as easily have been A, B, C, etc.) and 23 equals the row. The boxes that I'm putting the cost of my materials into are G23 through G28).
1. Type in the Column/Rows that you want to add together with a + sign between each. In my example, I had six materials, so I wanted to add six Column/Rows together. 
4. Type a ) to show that this is the end of your formula.

I also added a formula to add the fees together (since they may be subject to change) and a formula to add my profit without fees (for selling in person, although I would probably want to add a booth fee in there somewhere if I went to a show) and with fees (I used Etsy's fees, which are a $.20 listing fee and 3.5% transaction fee, in addition to Paypal's transaction fee, which is 2.9%+$.30. My formula for profit was =(G35-G29) (Sold Price - Materials Cost) and my formula for profit including fees was =(G36-G33) (Profit w/o fees - Selling Fees Total).

That's all there is to it! I know it looks like of cumbersome, but it was truly surprisingly easy once I figured out all the information I wanted.

MAKE THE FORMULA FIT ANY ITEM:

To modify, I copied and pasted it to the second page of the spreadsheet, then deleted the words Materials 1, and typed "4mm Beads x20", and then typed the cost of those 20 beads into the adjoining box. Copying it has no effect on the formula. Even though I originally said G23 for Material 1, when I paste it, the formula will automatically change to J5 or wherever the Material 1 box now is.

I also modified one of my formulas to include a x6 factor since many of my items are sold in sets of six. In the same way that I subtracted the materials cost from the selling price, after I calculated the costs of a single wine charm, I added a formula at the end to multiple everything by six so I could easily see the price of one versus the price of the set.

* * *

The reason I went to all this trouble is twofold: One - my costs change on a yearly basis, and Two - my costs to the bead/finding are often uneven cents, and I hate adding up a list of cents by hand, over and over and over, and then redoing it all because I can't find/forgot what it was the first time. In the future, when those 20 beads go from $0.50 to $0.70, all I need to do is change it on the spreadsheet and I have the new calculation for my costs and profit. This also allows me to quickly calculate the price of new items - for example, if I didn't know what I wanted to sell something for, seeing the raw cost (and time) quickly could help me decide the overall worth.

To make things even easier in the future, on a third page of the spreadsheet, I began compiling how I got my costs (I currently have all that information written on notes in several locations). For example, I typed the item name, how many individual parts it had, and the total cost (I included a cut of shipping costs where applicable). For example, one material might say: Brand X 4mm beads: 250 (36in) @Y price = Price of individual bead. Yes, it's nitty-gritty to get down to the cost of one bead, but I have to start treating myself like a real business, and that one bead times 20 for one charm times 6 for a set means I more accurately know how much I'm spending per item and whether my prices are fair. It also means if I want to switch to Brand Z 4mm beads, which may be 400 to a bag, I can see immediately if the cost is equal or better.