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.

Wine Charm Display How To

The summer craft show season is nearly upon us, and I've been thinking of ways to better display my wine charms, particularly the alphabet charms. I've them in cute little bowls (a hassle to find letters when they're all bunched together), I've separated them a plastic container so they're easy to find (but not as attractive as I'd like and people don't seem to like having to bend over to look at them all), so now I'm trying an upright display.

I couldn't find any pre-made, so I decided to make my own. I did a few searches online for jewelry displays and decided to try something with cork board. Although all the how to's were very nice, they didn't meet all of my criteria for an outdoor show. First, I wasn't hanging this on a wall, so it so it had to be sturdy enough to stand up by itself. I also wanted some kind of frame. Second, using cute tacks or hooks weren't going to do me any good if a gust of wind came up and blew everything down. Whatever I hung on the board had to be secure and stay on the board.

I did some recon at Michael's during a recent 40% off sale and found most of what I needed. I supplemented the essentials with a quick stop at my local quilt shop and Ace Hardware store.

1. 11x14 piece of cork board - Michael's
2. 11x14 simple frame - Michael's
3. 1 square yard of white cotton (I didn't need it all, but I wanted enough in case I messed up) - Quilt Shop
4. 3 pkgs of 5/8 eye screws - Ace

I also needed a few supplies from around the house.

1. Stapler that opens
2. Ruler or other measuring tool
3. Iron

All told, this project should take between 30 minutes and 1 hour.

1. Step 1 - Remove the glass from the picture frame and set aside. I'll have to come up with something to do with it later.
2. Step 2 - Cut 2 pieces of approximately 14x17 fabric. I felt that one piece was too thin, but two pieces covered the cork board nicely and looked whiter.
3. Step 3 - Iron the fabric. Irons make me nervous. I avoid them if I can, but in this case, ironed fabric just looks nicer.
4. Step 4 - Lay out the fabric with the cork board on top. I tried two different methods of stapling (I'll explain why later). You can see in the pictures for one try, I cut out most of the corner and folded, and for the second take, I used less fabric and folded the corner in, then stapled. Either worked well. My staples are probably 1" to 1.5" apart. I just estimated what felt best for keeping the fabric taut.
5. Step 5 - Fit the board into the frame. Unfortunately, the back won't fit back on. After I've used this once or twice, I might go back and glue it in, but the board isn't loose at all. I would have preferred to use the back to be able to stand up the board, but I ended up using a frame stand instead, and the frame became mostly decoration and a little added weight.
6. Using the ruler, decide how far apart the eye screws need to be. I want to hang wine charms on, so I measured out six across and five down. After measuring the first row and the beginning of the second, I used the grain of the fabric as a guide and it worked fine.
1. Here's why I had to staple twice: on the first attempt, instead of eye screw, I used double sided tacks, which are basically big staples. It didn't work. Hammering them in was a pain, and then they weren't big enough for the wine charms to clasp. I took off the fabric which now had ugly holes in it, put on fresh fabric, and tried again using eye screws, which worked beautifully, although my thumb was pretty tired by the end.

That's it! See the pictures below to see all the steps.

All told, and without looking at my receipts, I'd say I spend about $25. I probably could have found a frame at thrift shop or something, but it wasn't convenient to where I was.

What do you think?

Bacon Swiss Individual Quiche

I've been in the mood for quiche lately, and I thought it would be nice to make individual quiches to make leftovers easier. It's been a while since I made a quiche, but after looking up several recipes, I decided to just wing it. Most of the recipes were fairly similar, and I've never liked having to be too exact.

So if you've never made a quiche before, you're in luck: this is something you can't mess up.

Here's approximately what I used:

• 7 eggs
• 3/4 c milk
• 1 c shredded Swiss
• 2/3 c bacon bits
• 1 t crushed garlic
• handful of spinach
• handful of other shredded cheeses (Parmesan mix from deli)
• pepper to taste
• 5 layers phyllo dough (remember, if you buy frozen phyllo, put it in the fridge 24 hours before you want to use it) *NOTE - this can also be done with puff pastry
• 3 T Butter/melted

Pre-heat oven to 350 degrees F. I used 4 ceramic 1 3/4 cup Corelle casserole dishes.

First I sauteed the spinach until wilted, the set it aside to cool. Then I mixed the eggs, milk, Swiss, bacon, garlic, other cheese, and pepper together and set it aside.

I took out 5 layers of phyllo dough and cut into 4 equal squares. I took the first section and brushed a little bit of butter between each of the five layers, which I then put into one of the casserole dishes. I set aside and repeated with the other 3 dishes.

I put the four phyllo dishes into the oven and baked for 5 minutes, then removed the dishes.

I mixed the spinach in with the egg mixture and poured a little under one cup of the mixture into each dish, placing them back into the oven for approximately 25 minutes.

When a knife inserted into the center comes out clean, they're done.

 

I thought they came out excellent! There was a very strong bacon flavor, and just enough spinach for my taste. Some people may prefer more. I didn't add any salt because the bacon was pretty salty already, but others may want to add salt or Tabasco for additional flavor.

 I might try to be a little more careful with the edges of the phyllo next time. Taking the dishes in and out of the oven so much made a lot of the edges break off and fall into the bottom of the oven and all over the stove and made a mess.

Have you ever made quiche before? Do you use a real recipe, or just add whatever sounds good?

How To Fold A Paper Cup

Paper cups are pretty handy, I've found, even as an adult. You just never know when you're going to need to carry water and not have a glass nearby. :) I learned the technique as a kid for practical purposes at camp one summer. I think the original theory was that it could be made out of a large piece of bark, but I could be making that part up. ;) Nowadays, I've found that regular printer paper works really well for this.

A year or so ago I taught a few teenagers the trick, and it was like a light bulb just came on. I had to send one kid out to the water fountain to prove it worked, and when he came back, everyone else crowded around to inspect. Did it really work? Was it leaking? And then he drank the water out of it! How cool! How handy! What other uses are there?

Scroll to the bottom to see a picture of my adorable dog and another use for paper cups. :)

Most paper is not square, so start by folding the paper into a square.

See the extra paper? Cut it off. Or leave it, if you want. Sometimes I just fold and tuck it in.

Now it's gone! If I don't have scissors, I just fold the extra bit a few times and tear it off.

Now take one corner to touch the opposite side.

Turn the paper over and repeat.

Fold the top triangle down. I like to tuck it into the handy slot, but you don't have to.

 Do the same to the opposite side.

And now you have your very own paper cup!

















Try some water! My sample paper was a lighter scrapbook paper, and it didn't hold up as well as computer paper does, perhaps because there's more surface area?













For the record, I never dress up my dog, but after I made the paper cup for the pictures, I couldn't resist trying a larger one. And isn't he cute? I made an extra big paper cup and then bribed him with a milk bone to hold still long enough to take the picture. See that intent stare? There's a dog cookie at the other end of that stare.

Tanka

A few weeks ago, I wrote a little bit about Haiku. Today I'd like to talk about an even older form of Japanese poetry, one that I think I might like even better than the haiku. :) Tanka are five line poems with a syllable pattern of 5, 7, 5, 7, 7. The examples below are off on the syllables because of the translation.

Tanka often have themes similar to haiku, such as nature, and it's interesting to note that there was an official government Office of Poetry which conducted tanka competitions, but I think what makes tanka so special is the way they were used in private. Tanka were revered as THE style of love poem. Young men spent a great deal of time constructing just the right words, and then had to decide just the right form of presentation. He may have tinted bamboo paper a pretty color or scattered the paper with tiny flecks of gold and silver foil before rolling it up with a pretty flower or twig.

It almost goes without saying that the words would have then been written in his most careful calligraphy. If the young lady in question responded to his poem with an encouraging tanka of her own, the relationship would proceed. An exchange of new, personal, and unique poems would be expected to be exchanged throughout the courtship.

Below are a few examples of tanka written by Ono Komachi, still a well-known poet today, although she was born in 834 AD (death date unknown). Read each poem several times to get the full meaning. While they may have been written at completely different times about the author's own experience, I read it as one fictional girl's experience with a young man (I find it interesting that she has initiated the poem exchange). Tanka, as with haiku, could be used as a way to write an entire book.

Notice how the first two or three lines often set up the poem with some kind of image while the last two lines describe some kind of reflection/metaphor on that image (the third line acts as a kind of bridge or turning point to connect the top two and bottom two).

Sent anonymously to a man who had passed in front of the screens of my room

Should the world of love

end in darkness,

without our glimpsing

that cloud-gap

where the moon's light fills the sky?

Sent to a man who seemed to have changed his mind

Since my heart placed me

on board your drifting ship,

not one day has passed

that I haven't been drenched

in cold waves.

Sent in a letter attached to a rice stalk with an empty seed husk 

How sad that I hope 

to see you even now,

after my life has emptied itself

like this stalk of grain

into the autumn wind.

What do you think?

Haiku

Everyone knows that a traditional Japanese haiku is based on the number of syllables over three lines (5, 7, 5), but did you also know that haiku almost always have some reference to nature? What I love about this is that it isn't just talking about nature for the sake of saying something is pretty, but to make some kind of observation. Because they are so short, they have to pack a punch. The shortness also means it's nice and easy to reread multiple times to really see beyond the words.

Here is an example by the famous Basho Matsuo (1644-1694):

Bush clover in blossom waves

Without spilling

A drop of dew.

However, that doesn't mean haiku have to be stuffy. Check out this one by Issa, who lived from 1763-1827:

Don't worry, spiders,

I keep house

Casually.

The syllables don't work out exactly because of the translation, but I find it comforting and a little bit hilarious that people across the world over 200 years ago had the same problem I have!

Palm Folding

Folded Palm Crosses

In honor of Palm Sunday, I thought I'd share a few links I found on how to fold palms into crosses and other designs!

Here's one on Squidoo with a video and lots of links.

Here's a Wiki-How with pictures.

Here's an Origami site with links to diagrams.

Folded Palm Cross

I know I'm a very visual learner, so if I can see something done, I can often recreate it. These three sites give you three different ways to see how folding palms can be done, so hopefully however you learn, there's something helpful for you here!

The pictures here are two that I folded this morning. As you can tell from the top picture, the first one wasn't that great, but each subsequent one was better than the last.