[Spreadsheet] Calculating Commission Costs

Discussion in 'Norrathian Homeshow' started by Jazabelle, Jul 1, 2015.

  1. Jazabelle Well-Known Member

    Sometimes when building a home, you want to know how much you spent. Or perhaps you're working on a commission, and you need to know how much you've spent in fuel costs and items, so that your client can refund you the cost. (This is especially important when you're accepting a flat fee--you want to make sure you're making a profit on your commission!)

    Calculating out everything can be a pain, if you don't know the prices of fuel offhand. It often involves hours sitting in front of the fuel vendor with your recipe book open and a calculator handy, plugging in number after number.

    A few years ago, I became tired of that, and put together a spreadsheet with the costs of various tiers of items.

    About a year later, I got tired of using that to plug in numbers, and realized I could get the spreadsheet to plug the numbers in for me!

    It wasn't until a conversation with Boglily last night that I realized other people might also find the spreadsheet useful.

    It makes calculating costs a lot faster--it took me less than half an hour to calculate the entire cost of my Cathedral of Our Lady (the manifest I used for the example image). And the Cathedral of Our Lady used 2131 total items (nearly 1200 building blocks, and over 900 house items)!

    [IMG]

    It's pretty simple to use. Don't be freaked out by all the rows; the sheet itself includes instructions on how to use it. You will need the layout editor in order to create a manifest of your items.

    The spreadsheet adds together the information you've given it about what tier and cost, multiplies it by the cost of fuel for that item (listed on the Fuel Cost tab), and then multiplies that number by the number of that type of item you've used. So if you spent 2 plat per on bubinga lumber for 12 High Keep Ornate Chairs, the spreadsheet will take the cost of the fuel, add 2 plat for the bubinga, then multiply that number by 12 (26p 16g, in this case).

    Your job is very simple. Just paste in the ItemName and Count columns from the manifest, then set the drop-downs and add in any additional costs. The spreadsheet tells you at the top of the sheet what the total amount you spent on the build is!

    You can click here for the spreadsheet. Just go to File, and select either "Make a copy" or "Download as...".

    While making a copy works perfectly, I cannot guarantee that the downloaded spreadsheet works properly for all file types. I know it works fine with Google Sheets (which is what I used to put it together), and it should work fine with other spreadsheet programs. However, I lack any other programs with which to test a downloaded copy.

    If you use Google Sheets, you'll want to select "Make a copy" to save a copy of the sheet for your own.

    Happy calculating!

    Edit: According to Boglily, downloading the spreadsheet as an Excel file works fine!
    Katz, Feldon, Geden and 1 other person like this.
  2. Jazabelle Well-Known Member

    And for anyone interested, the link to look at the filled out spreadsheet I threw together is here.

    In this spreadsheet, I'm acting like I've built the cathedral for a client, and have set each mastercrafted item to include 1p to cover the rare, and added 5g to the cost of each holiday combine, to cover the cost of the holiday harvests.

    Yes, that estimate on prices is low, and if I were actually buying rares off the broker, the prices would vary by rare and tier. However, this sheet is just to give you an idea of how it works. As I harvested all of the rares myself, quested all of the quested items, and spent my own loyalty tokens on loyalty items, those are costs I wouldn't actually have to handle out of pocket on a personal build. If you want to see what the Cathedral of Our Lady actually cost me, you can click here.
  3. Zexian Member

    This is a really nice way to lay everything out, Jaz. I've often looked around and ended up using pencil and paper for the list of items put into it then after all was said tallied it up price wise (having a general idea of materials of course or where the items come from). I'll have to use this from here on!

    I am very glad you put this together and thank you deeply for it. :)

    Hopefully gratitude is enough for the time and effort placed into it. ;)
    Uwkete-of-Crushbone likes this.
  4. Jazabelle Well-Known Member


    Of course gratitude is enough! :p

    This spreadsheet is something I've been using for a while (although I did have to modify the math a bit, since I don't usually charge fuel + rare, but one or the other, so the cell formulae weren't set up properly for multiple charges). It took me less than half an hour to throw together, since the basic sheet already existed. Most of that half an hour was spent looking for the price of Frostfell fuel, since as I said, I usually charge either fuel cost or component cost, not both, and I didn't have the price for Frostfell fuel on hand... Well, that, and getting the Total House Cost to display as platinum, gold, and silver!

    Question: Would people find it more useful to do things the way they're done now, or would they prefer "Tinkerfest" be changed to "Holiday," and to have all of the holidays on the drop-down, so they can calculate themselves individually, without having to use the Handcrafted and Mastercrafted dropdowns?
    Geden and Uwkete-of-Crushbone like this.
  5. Sapphirius Well-Known Member

    I am loving this spreadsheet, and I can confirm that it works nicely with Excel 2010. I used the spreadsheet to verify what I figured out by hand using a calculator... I made a profit of less than 10 plat on one of my recent commissions.

    I've since set down some new ground rules for clients... like not adding onto the project unless they're willing to double or triple their budget and strongly encouraging a tip. I'm thinking of starting a new consolidated thread with a list of my available pre-made layouts and my new ground rules.

    Oh, and I have no opinion one way or the other about the holiday/Tinkerfest stuff.
    Geden and Uwkete-of-Crushbone like this.
  6. Jazabelle Well-Known Member

    Turns out that yes, each holiday needs its own drop-down, since some holiday combines produce more items than other holiday combines (and NotD has to be different, by using glowing fuel instead of basic fuel...). This is why I should not modify spreadsheets in the evening.

    [IMG]
    Geden and Uwkete-of-Crushbone like this.
  7. Uwkete-of-Crushbone Well-Known Member

    Ah...okay, so, is there an updated version (is that the one on the link?) and how does one download this? I didn't see anything obvious on the site, like "Download button here, dummy!"... ;->

    Uwk
    who is, indeed, quite interested, and totally agrees with the update, since you're right, the holidays will vary (some return 3 products, some 2; Tinkerfest usually = 1 only :-/)

    EDIT: Ah! File->Download As?
  8. Jazabelle Well-Known Member


    Yes, File -> "Download as..." -> The format you wish to download the sheet as.

    If you downloaded the sheet, you'll have to download it again. I discovered a rounding error that I didn't notice the previous time (rounding?! I didn't DO any rounding! I truncated! And yet...). If you look at my example screenshots, line 9 for example (burgundy spuncloth throw cushion) shows 11p 38g 79s 100c instead of 11p 38g 80s. I fixed that by adding 0.0000000005 (the equivalent of 5/10000th of a copper) to the formula, then truncating at coppers...

    If you don't care about the amounts possibly displaying one silver short, and 100c, then don't bother downloading the update. If you DO care about it (like I do), the sheet has been fully updated to fix the problem.

    (I also fixed a couple of cosmetic errors that were bugging me, but they don't affect how the sheet operates at all. I changed it so there isn't an extra space after p, s, or g, if the number doesn't end in coppers...)

    I promise, unless I find a super pressing error, the sheet is in its final format!
    Uwkete-of-Crushbone likes this.
  9. Uwkete-of-Crushbone Well-Known Member

    /chuckle Okay, so the link at the top goes to the final version...will do. ;->

    Fortunately, I hadn't downloaded any of it yet, so I'm good! :)

    Uwk
    who, when bad, is horrid... ;->
  10. Uwkete-of-Crushbone Well-Known Member

    Ah! Just downloaded it; originally, I did so as an Excel sheet, but apparently, Kingsoft doesn't play as well with all Excel files as it claims... :(

    Then downloaded it as an Open Office type 'sheet, and it came up beautifully, at least appearance-wise. I'll have to try it to see if it actually works in that format, but since it was on the list of possible file types, I presume it will. :)

    Uwk
  11. Moonpanther Well-Known Member

    Thank you Jazz for taking the time to do this...update it and share it. I really appreciate all you do to help the decorators. I'm proud to be a member of your guild.
  12. Sapphirius Well-Known Member

    *hugs* It worked beautifully for me in Excel 2010. I'm glad it worked in Open Office for you!
    Uwkete-of-Crushbone likes this.
  13. Uwkete-of-Crushbone Well-Known Member

    Well, Kingsford is free, so I guess I got what I paid for. ;->

    The most irritating thing about it is that it's European-based, so (my apologies to everyone else on the planet, but...) there's no options for American units; everything is in terms of centimeters or millimeters. X-P

    Uwk
    who, frankly, shouldn't need to keep hitting Google for metric conversions just to do work in a stupid spreadsheet program :-/
  14. Jazabelle Well-Known Member


    If you don't mind being assimilated by Google, you can always use http://sheets.google.com, since Google has the online spreadsheet program through Google Drive (which is why I built the spreadsheet in Drive. I use too many devices, and am constantly switching between them, so having my spreadsheets handy on Google's cloud server from any device I may use is heavenly. That, and the ability to easily allow people to access the sheets).

    I wouldn't use it for Super Important Stuff, but for something like a gaming spreadsheet, it's wonderful.
    Uwkete-of-Crushbone likes this.
  15. Jazabelle Well-Known Member

    Someone was requesting this the other day. Here it is!
    Uwkete-of-Crushbone likes this.
  16. Merriel Well-Known Member

    Awesome spreadsheet, Jaz! I've never actually decorated for anyone else besides myself before, as I've never known where to even begin to determine how much to charge, but this spreadsheet is great for helping to decide what to charge. I wouldn't be able to put something like this together, but with everything right there at my fingertips, and only having to add in some costs, I think I could handle this. :)
    Uwkete-of-Crushbone likes this.
  17. Katz Well-Known Member

    Thank you!
    Uwkete-of-Crushbone likes this.