Calculate the right retail price with Excel - Microsoft Excel Tutorial

(energetic electronic music) - Greetings, friends. I'm going to share with you the joy, tranquility, that data can bring when you have control over it. This project started with a sister who was making soaps, bath salts, shampoos, using essential oils. And her crisis was the need to be able to change recipes on the fly so that she could see how the prices would change. So, I built this model. In this example, we have a product called Tranquility. It's at $26.73, and she feels that's a bit high. So, let's look at it. (clears throat) Here, we see spikenard, honey myrtle, and tea tree oils in a carrier of cocoa butter. There's an eight ounce jar that costs $1.14. Now, let's adjust some of the numbers and see if we can get the price down. Instead of seven ounces of cocoa butter, how about six? Ah, $24. Instead of eight drops of spikenard, what do we get from seven? And adjust the markup a bit to 2.55, $20.08. (audience ahs) Doesn't that feel good? - Uh-uh. - It should. Alright, so how does this work? First of all, there are dropdown lists, see? The tea tree, here's a dropdown list. If I wanted to change it to something else, to thyme, I could select that. So, let's do it. Thyme. And the price changed because the prices are over here in the oils sheet. We can see black pepper. It's $14 a bottle. That converts to $28 an ounce, 250 drops per bottle, and a little less than a nickel per drop. That's how we get the price into the product. Over on the container sheet, we see that the eight ounce jar was purchased as one jar for $1.14. However, the three ounce spray bottle came in a pack of 12 for $10, which means $0.83 a piece. And one other feature to point out is the sheet is protected so that we don't pollute any other formulas, accidentally delete them, anything. Let's watch. I'm going to try to select the container total. I can't, but I can go back to a carrier sale, and I'm going to hit my down arrow and watch the sale jump. It went from the carriers to the containers. How does this work? Controlled inputs. Protected outputs. Integrated features. My sister's ultimate question was, can Excel melt away the anxiety, bring harmony, peace, love, tranquility that she can pass on to her clients? Never doubt. Yes. Excel can. (energetic electronic music)