Google Sheet alternative to Craftybase or Soapmaker Pro

[SoapyBenjamin]

Long time lurker... First post...

I've been working on a Google sheet that replaces soapcalc's basic utility, plus allows for additives, volumes, etc. I was able to download the code base from Soapee (GitHub) to gather all the oils/fats and their saponification values.

It wasn't difficult to take the next step and add labor calculations, product cost, and packaging. In fact, it's all fairly simple on a "per batch" basis.

The next step would be to separate out all the various "modules" so that—in whole—these spreadsheets could replace software like Craftybase or Soapmaker Pro.

Inventory levels and automation will be more difficult, but certainly possible.

Wondering if anyone else might be interested in this work... I suppose I could share the "batch" spreadsheet after a little polishing. Any feedback would be most appreciated.

Thank you!

 

[AliOop]

I believe @Grantxw8 has started something similar over in this thread. Sounds like you could be a good team!

Just be aware that a lot of folks here put a lot of time and effort into testing and feedback for Soapmaking Friend. When they switched without warning to a paid subscription model, it left a lot of folks with a bad taste in their mouth. I'm not saying it is wrong to do a paid subscription - not at all. But my recommendation is to be very upfront if that is your plan. That way, folks who contribute to your efforts will know that ahead of time. Alternatively, if you start out planning to be free and then change your mind, give lots of advance notice so that the folks who have saved information in your program don't suddenly lose access.

 

[Relle]

If you are intending to do this to sell, then you would have to join as a sponsor, pay a fee.and then advertise your product. Soapcalc is provided free.

 

[Elise]

And of you don't, I'm sure people would be interested in trying your spread sheet and giving feedbacks. I know I would
I find that your project could be very useful.

 

[SoapyBenjamin]

Full disclosure: This endeavor would never be used as the basis for some future for-profit initiative... Ever... Just a bunch of Google spreadsheets that I personally want to "wire" together that'll give me the same feature set as some of the other products on the market. For what it's worth, I don't believe there is anything wrong w/ a for-profit angle, but it's not my intent, and I can see where some of you may have felt a little burned by decisions made by others in the past.

My intent is to make great soap, and build a scalable business.

@AliOop is right... This is very similar to what @Grantxw8 has done in Microsoft Excel—and he's done a great job from the looks of it. I'm on a Mac, and don't have Microsoft Office. Also, since Google Sheets are "sharable," I thought I would continue with this effort in the hope that others would find it valuable. I'm a techie in my current day job, and this is more fun than a chore

In short, this would be a gift to other soapers, free from any obligation whatsoever (@Relle).

Thank you, all! First version (single batch/recipe only) coming soon.

 

[SoapyBenjamin]

I've compiled the complete fats/oils/waxes spreadsheet. This is not the tool I mentioned earlier, but is used by that tool. Some may find this spreadsheet helpful as a standalone document.

Please note: This comes directly from the Soapee JSON file. There are some rows with total saponifiables greater than 100%, and you'll see fatty acids that are less common.

CSV file attached.

 

[Grantxw8]

Full disclosure: This endeavor would never be used as the basis for some future for-profit initiative... Ever... Just a bunch of Google spreadsheets that I personally want to "wire" together that'll give me the same feature set as some of the other products on the market. For what it's worth, I don't believe there is anything wrong w/ a for-profit angle, but it's not my intent, and I can see where some of you may have felt a little burned by decisions made by others in the past.

My intent is to make great soap, and build a scalable business.

@AliOop is right... This is very similar to what @Grantxw8 has done in Microsoft Excel—and he's done a great job from the looks of it. I'm on a Mac, and don't have Microsoft Office. Also, since Google Sheets are "sharable," I thought I would continue with this effort in the hope that others would find it valuable. I'm a techie in my current day job, and this is more fun than a chore

In short, this would be a gift to other soapers, free from any obligation whatsoever (@Relle).

Thank you, all! First version (single batch/recipe only) coming soon.

Hi SoapyBenjamin,

Anything I can do to help I’ll be happy to do. Just let me know. Doing this in Google sheets is a great way to provide something for free. Mine will always be free too but does require access to MS Excel. I choose that program because I like the automation that I can use with macros. It’s a tool I built for myself and choose to share.

I do have plans to develop the tool some more to add candles and other products (bath bombs for example). I too enjoy the work of building it. It’s a bit like Sudoku for me.

It would be nice to have an idea how often my tool is downloaded given the effort that goes into making it but I don’t think there is any way to do that. Given the lack of feedback/questions I receive I’m guessing it isn’t used very much.

Let me know if I can do anything to help.

 

[Grantxw8]

I've compiled the complete fats/oils/waxes spreadsheet. This is not the tool I mentioned earlier, but is used by that tool. Some may find this spreadsheet helpful as a standalone document.

Please note: This comes directly from the Soapee JSON file. There are some rows with total saponifiables greater than 100%, and you'll see fatty acids that are less common.

CSV file attached.

Hi @SoapyBenjamin,

Just a word of thanks for the CSV file. I have integrated it into my Soap Designer spreadsheet as it was so comprehensive. Let me know if I can contribute to your Google Sheets efforts. FWIW, none of the sheets in my spreadsheet are locked in any way so feel free to use any of it as you please.

Enjoy.

 

[SoapyBenjamin]

Hey @Grantxw8, that's great!

I merely transferred a JSON file into a CSV file. A big "thank you" to the creator of Soapee is well-earned. I hope it serves you well, sir.

I'm still trying to determine how far I want to mess with Google Sheets. In particular, the whole inventory management aspect of it. I may stick to a single sheet for recipe creation, cost, and labor. Anything beyond that starts to look more like an app, and further from the spreadsheet sweet spot.

 

[Vicki C]

Oh, this is great! I’m looking forward to what you produce. I use google sheets for many things - tracking sales, maintaining a price list for all ingredients (by vendor per ounce), scaling recipes.
@Grantxw8 I had downloaded an early version of your spreadsheet but it was still being refined - I will take another look.
As a small business owner what I should do (and don’t) is track my inventory and link it to production. My current system is to look at what I have and vaguely know what I need. Yesterday I was surprised to realize I only have three master batch buckets left of twelve I had mixed in June. (Each bucket is 12.5 kg). I also aim to link purchases to production to know exactly the source of every ingredient in every bar.
I too am a Mac user and I’ve never purchased Soapee, haven’t wanted to deal with running a different OS just to be able to use one program that I’m not sure I want.
Thanks for your efforts!

 

[Grantxw8]

Hi @Vicki C ,

FWIW, I too am a Mac user. Excel is largely platform agnostic (at least for Windows and Mac) but does require a subscription. In Australia it’s about $AU179 to buy outright or about $AU11/month for an Office 365 subscription. For me it’s worthwhile subscribing to get the entire Office suite for my whole family.

 

[Grantxw8]

Hey @Grantxw8, that's great!

I merely transferred a JSON file into a CSV file. A big "thank you" to the creator of Soapee is well-earned. I hope it serves you well, sir.

I'm still trying to determine how far I want to mess with Google Sheets. In particular, the whole inventory management aspect of it. I may stick to a single sheet for recipe creation, cost, and labor. Anything beyond that starts to look more like an app, and further from the spreadsheet sweet spot.

I have given inventory control some thought but a flat file database, like Excel, isn’t really the best tool for that job. That would be a relational database and that’s a level of complexity beyond my interest. There’s also the cost/complexity of creating runtime versions so users can use it without installing a database.

I think the only way to keep something like this completely free is to use a ubiquitous tool or a free tool. Between us we have those things covered if you proceed with the Google sheets project. I look forward to seeing what you come up with (and maybe ‘borrowing’ some ideas ).

 

[Becky1024]

I loved Soapee - it was so easy to use and allowed for dual lye and additives that Soapcalc does not. I downloaded the Soapee file but never had the time to look at it so I appreciate your work! I have never used Craftybase or Soapmaker. Instead, my inventory is managed on Wix and then I manually low stock into a file on Excel. Google Sheets is fine with me too. It would be awesome if you could integrate Wix into an inventory management spreadsheet somehow. Sometimes I mess up and run out of stock! I'm interested to see what you come up with!

 

[Vicki C]

Hi @Vicki C ,

FWIW, I too am a Mac user. Excel is largely platform agnostic (at least for Windows and Mac) but does require a subscription. In Australia it’s about $AU179 to buy outright or about $AU11/month for an Office 365 subscription. For me it’s worthwhile subscribing to get the entire Office suite for my whole family.

I should have said I do have the office suite, including Excel, on my MacBook. Sheets is handy for me because being cloud-based I can check it from my phone, iPad or MacBook. But - I am going to look again at your spreadsheet.

 

[Grantxw8]

I loved Soapee - it was so easy to use and allowed for dual lye and additives that Soapcalc does not. I downloaded the Soapee file but never had the time to look at it so I appreciate your work! I have never used Craftybase or Soapmaker. Instead, my inventory is managed on Wix and then I manually low stock into a file on Excel. Google Sheets is fine with me too. It would be awesome if you could integrate Wix into an inventory management spreadsheet somehow. Sometimes I mess up and run out of stock! I'm interested to see what you come up with!

Hi @Becky1024, I’m not familiar with Wix but there may be a way to access it as a database from Excel using Power Query. If you’re using Excel anyway it might be worth looking into?

I’m curious about your stock issue. When you say “stock” are you referring to inventory, materials or both? I use Square as a credit card/payment processor and it can do rudimentary inventory management. That is, when you make a batch you add it to available inventory manually and Square tracks it as it’s sold.

I’m not sure about the manners of taking over this thread with a tangent like this. Is it ok? Or, should I start a different thread if we’re going to continue?

I should have said I do have the office suite, including Excel, on my MacBook. Sheets is handy for me because being cloud-based I can check it from my phone, iPad or MacBook. But - I am going to look again at your spreadsheet.

ahh. At the risk of sounding like a Microsoft salesman, if you have Office 365 it is also cloud based using OneDrive. I use it on my MacBook, iPhone (not ideal for Excel) and iPad.

 

[Becky1024]

Hi @Becky1024, I’m not familiar with Wix but there may be a way to access it as a database from Excel using Power Query. If you’re using Excel anyway it might be worth looking into?

I’m curious about your stock issue. When you say “stock” are you referring to inventory, materials or both? I use Square as a credit card/payment processor and it can do rudimentary inventory management. That is, when you make a batch you add it to available inventory manually and Square tracks it as it’s sold.

I’m not sure about the manners of taking over this thread with a tangent like this. Is it ok? Or, should I start a different thread if we’re going to continue?

ahh. At the risk of sounding like a Microsoft salesman, if you have Office 365 it is also cloud based using OneDrive. I use it on my MacBook, iPhone (not ideal for Excel) and iPad.

I’m referring to both raw materials and finished product. It would be nice to have a program where I could set my stocking levels and alert me when I need to buy or make more.

 

[Grantxw8]

I’m referring to both raw materials and finished product. It would be nice to have a program where I could set my stocking levels and alert me when I need to buy or make more.

Hi Becky1024,

I have given this some thought in the past. My ideal program would allow me to enter my materials stock as I purchase it and deplete it automagically as I make batches depending on the recipe/batch size I choose. It would then add the resulting stock (e.g. Soaps) to my inventory. This in turn would be depleted as I recorded sales.

As far as I'm aware, there is nothing available that fits that description. Certainly not free of charge. I'd be delighted to be corrected on this though.

The best I have found is using POS software (Square in my case) to deplete the inventory as I make sales. I have to manually add the inventory once I have made a batch and it is ready for sale. I've yet to find a satisfactory method for tracking raw materials - but I haven't spent any time or effort in looking.

I might start another thread and ask others for their thoughts.

 

[Vicki C]

You had me at automagically

 

[SoapyBenjamin]

I've given it some thought and decided I will not try to make Google Sheets into something it's not...Google Sheets might be easier to share a link, but it's not for building apps. Even though @Grantxw8 works with Microsoft Excel (another spreadsheet platform), Excel is superior to Google because it supports more "app-like" capabilities (like macros, etc.).

I fully support him in his endeavor and will help out where I can.

I will, however, continue with a "one sheet" feasibility calculator that will help soapers quickly determine the particulars of a given recipe. It will not be tied to inventory, automatic ordering systems, or "talk" to other spreadsheets. This was useful for me to determine profit potential and true cost analysis. It's where this all started and I hope it's useful to others.

It will:

1. Allow you to tweak your formula in grams or ounces
2. Recalculate on batch size changes
3. Calculate the lye/water amounts for KOH and/or NaOH, with superfatting, discounts and/or lye purity variables
4. Calculate your total material cost (you supply the prices you paid for your bulk materials & shipping)
5. Determine the total volume each bar will take up in your mould + considers curing shrinkage by starting weight & final sellable weight
6. Calculate the labor cost (uses a per/hour variable, even if it's you doing the work... Time is money)
7. Calculate the packaging costs (you provide)

It will not:

• Provide any kind of inventory insight (stock levels, etc.)
• Produce a shopping list when supplies run low (part of inventory)
• Integrate with Wix, Shopify, or any other e-commerce system
• Have any kind of visibility into fixed or variable costs (rent, utilities, insurance, taxes, etc.)

Example scenario:

• Let's assume I am formulating a new soap and trying to understand cost and profit potential
• I know I'm willing to swap some of the ingredients out if needed, but not others... I just need to see the particulars before I bend on substitutions
• I mostly buy my materials from Amazon, Costco, WSP, etc. But what if I purchase 44 lb. bags of coconut oil from Jedwards or Shay and Company? i.e. how much does that lower my cost per bar?
• Maybe I really don't NEED colloidal oatmeal, or sorbitol, or aloe vera juice, or... If I leave these out, will it affect my product?
• What if I use labels instead of custom-printed boxes, so the plain boxes are interchangeable, and can be used for all my bars?
• What if I swap CO for PKO because it's 20% cheaper in bulk? Am I willing to consider this?
• What if I plug in a rate of $15/hour for labor to see how hiring my niece might affect the margin?
• What if I could reduce prep time by 10 minutes by master batching?
• Etc, etc.

This is what I put together. I hope it makes sense...

Because I've opted to not build something bigger or more integrated, I probably only need a week or so to polish up the spreadsheet before posting an update. I'd also like to make a "how to" video if time permits.

@Grantxw8, if any of this "prototyping" logic makes sense for your application, by all means, feel free to incorporate it into your app.

Thanks all!

 

[Grantxw8]

I've given it some thought and decided I will not try to make Google Sheets into something it's not...Google Sheets might be easier to share a link, but it's not for building apps. Even though @Grantxw8 works with Microsoft Excel (another spreadsheet platform), Excel is superior to Google because it supports more "app-like" capabilities (like macros, etc.).

I fully support him in his endeavor and will help out where I can.

I will, however, continue with a "one sheet" feasibility calculator that will help soapers quickly determine the particulars of a given recipe. It will not be tied to inventory, automatic ordering systems, or "talk" to other spreadsheets. This was useful for me to determine profit potential and true cost analysis. It's where this all started and I hope it's useful to others.

It will:

1. Allow you to tweak your formula in grams or ounces
2. Recalculate on batch size changes
3. Calculate the lye/water amounts for KOH and/or NaOH, with superfatting, discounts and/or lye purity variables
4. Calculate your total material cost (you supply the prices you paid for your bulk materials & shipping)
5. Determine the total volume each bar will take up in your mould + considers curing shrinkage by starting weight & final sellable weight
6. Calculate the labor cost (uses a per/hour variable, even if it's you doing the work... Time is money)
7. Calculate the packaging costs (you provide)

It will not:

• Provide any kind of inventory insight (stock levels, etc.)
• Produce a shopping list when supplies run low (part of inventory)
• Integrate with Wix, Shopify, or any other e-commerce system
• Have any kind of visibility into fixed or variable costs (rent, utilities, insurance, taxes, etc.)

Example scenario:

• Let's assume I am formulating a new soap and trying to understand cost and profit potential
• I know I'm willing to swap some of the ingredients out if needed, but not others... I just need to see the particulars before I bend on substitutions
• I mostly buy my materials from Amazon, Costco, WSP, etc. But what if I purchase 44 lb. bags of coconut oil from Jedwards or Shay and Company? i.e. how much does that lower my cost per bar?
• Maybe I really don't NEED colloidal oatmeal, or sorbitol, or aloe vera juice, or... If I leave these out, will it affect my product?
• What if I use labels instead of custom-printed boxes, so the plain boxes are interchangeable, and can be used for all my bars?
• What if I swap CO for PKO because it's 20% cheaper in bulk? Am I willing to consider this?
• What if I plug in a rate of $15/hour for labor to see how hiring my niece might affect the margin?
• What if I could reduce prep time by 10 minutes by master batching?
• Etc, etc.

This is what I put together. I hope it makes sense...

Because I've opted to not build something bigger or more integrated, I probably only need a week or so to polish up the spreadsheet before posting an update. I'd also like to make a "how to" video if time permits.

@Grantxw8, if any of this "prototyping" logic makes sense for your application, by all means, feel free to incorporate it into your app.

Thanks all!

@SoapyBenjamin This sounds awesome! I look forward to playing with it.