New Excel Sheet Beta Testing

[mrchaos101]

Hello ALL!


I been working an a excel sheet to help every body figur true cost ( or close to it) for things we make.

Right now, this sheet will handel bakeing and brewing and tailoring. I am not putting any other skills into until I get some of the buggs worked out.

I would like some people to down load and looka the Afternoon Tea and the Kaladim Cons and see that the formulas are right.

This zip has 2 files in it. prices holds all the price I obtained from eqtraders.com, and brewing which is the actual sheet you want to use. Make sure you keep both sheets in the same folder.

If you see any errors or bugs please post in here so I can make changs.

this is v1.0 of the sheet.

Thanks

Tim

[mrchaos101]

Update Commng

I have made a few minor changes in the spread sheet.

I was wondering if any body has downloaded the sheet and tried it out?
Any ideas on change would be welcomed.

Keep in mind this sheet should be able to be used for ALL trade skills. in the database I only have the componetns for baking, brewing, and tailoring.

As soon as I finsh a few more tweeks I will post the newest version as well.

[Turlo Lomon]

I have some similiar sheets made, so I downloaded yours to compare. (Heck, I used to have a website dedicated to the subject.) Over all, it is similiar in nature. Noticed a few things though.

1) Your success rate is kicking into 100% for Qeynos Afternoon Tea (and others). You may want to check the formula... it looks too short for the variances on success rate. My interpretation of the formula gives a 95% success rate given the other data.

2) I would reformat all monetary variables to be three decimal places. This would make everything consistant with prices in game. (You have some stopping at 0, and some going to 6. I found it easier to read when all the same format.)

This definitely is a different take on what I was doing. Interesting stuff. This would be a good thing for people making a living at selling tradeskill items. However, for myself, I only make items via contract, so it wouldn't necessarily make my needs, or so I thought. It definitely showed me some areas of improvement in my own project.

Thank you for the work done on this. It is making me reconsider what information I need to track.

[mrchaos101]

I have some similiar sheets made, so I downloaded yours to compare. (Heck, I used to have a website dedicated to the subject.) Over all, it is similiar in nature. Noticed a few things though.

1) Your success rate is kicking into 100% for Qeynos Afternoon Tea (and others). You may want to check the formula... it looks too short for the variances on success rate. My interpretation of the formula gives a 95% success rate given the other data.

2) I would reformat all monetary variables to be three decimal places. This would make everything consistant with prices in game. (You have some stopping at 0, and some going to 6. I found it easier to read when all the same format.)

This definitely is a different take on what I was doing. Interesting stuff. This would be a good thing for people making a living at selling tradeskill items. However, for myself, I only make items via contract, so it wouldn't necessarily make my needs, or so I thought. It definitely showed me some areas of improvement in my own project.

Thank you for the work done on this. It is making me reconsider what information I need to track.

1) Your success rate is kicking into 100% for Qeynos Afternoon Tea (and others). You may want to check the formula... it looks too short for the variances on success rate. My interpretation of the formula gives a 95% success rate given the other data.

Hm... are you saying that no Item made should be above a 95% success rate? How does this work when you are making say... Ale? At a skill of 252 I have never faild making something that was Trival at 68. I can change the formula to .95% as a max but that would over shoot the estmated costs for the lower trival items. Suggestions?

2) I would reformat all monetary variables to be three decimal places. This would make everything consistant with prices in game. (You have some stopping at 0, and some going to 6. I found it easier to read when all the same format.)

I have noticed this and already made the changes. I also made a few other changes to allow the user to read the data better.

Thank you for the post. I will be releasing another version next week with some changes. I am also trying to learn a few more things in Excel I dont know for later down the road.

Is there any other data that anybody would like to shee th spreadsheet track also?

[Turlo Lomon]

No, I am not saying you should cap the success at 95%. The variances I am referring to are as follows:

Trivial < 15 = 100%
Trivial > 68 = Skill - .75 * Trivial + 51.5
Else Trivial = Skill - Trivial + 66
Normally Min 5%, Max 95%

However,

Skill > Trivial + 40 = 96%
Skill > Trivial + 80 = 97%
Skill > Trivial + 120 = 98%
Skill > Trivial + 160 = 99%
Skill > Trivial + 200 = 100%

This is from the FAQ, with a bit of extrapulation from a post I read on a Sony board.

http://mboards.eqtraders.com/eq/showthread.php?t=11

Ends up being a very long formula.

[Turlo Lomon]

Oh, noticed a few more things... and please do not think of me as over critical. This is my accounting degree coming to the service.

Once the success rate is taken into account, there are a few other factors to consider:

Returned items on failure, returned items on success. (extra bottles, etc.). This off sets the cost per combine.

Another thing to take into account is the order of processing. You are calulating a batch process. Not saying this is wrong, just different.

The order I process things is as follows:

1) Raw Materials (Sum of components from a price sheet, similiar to yours)
2) Trivial (for calulations)
3) Success rate (overly complex formula by a sane person's standards)
4) Gross Cost. This would be the raw materials divided by the success rate. (For example, if your success rate was 50% and raw materials cost 10pp, the gross cost would be 20pp)
5) Failure Return (items returned on failure)
6) Success Return (extra items on success)
7) Net Cost (prorated amounts of both failure return and success return calulated out)
8) Quantity per combine
9) Stacked flag (does it stack or not, will make sense in a few)
10) Unit Cost (net cost divided by quantity)
11) Markup (this is where stacked flag comes into play)
12) Final Price

The way I handle mark up is different for stacked items then for different. I operate at a 50% profit margin, using a RL manufacturing industry as a model. I then round it up to nearest plat. If it is an item the stacks, I calculate this on a stack basis, then divide the total by 20 to get a unit price.

I use this sheet to give up front pricing on my contracts.

Well, I hope you find this input helpful. If you need any help with specific excel formulas, please feel free to ask. Even if I have a different spin on things, I still try to respect the work that people put into these type of things and try my best to help.

[mrchaos101]

Aye,

and you to have opend my eyes to a few things. I want to get a few more isseus worked out before I tackel the return on fail and return on success.

Im not sure how to do an IF THEN statment in Excel. I can program in VB how ever. Im starting to think I should make this project VB based... but that would effect others in customizing stuff they want to change.

[Turlo Lomon]

I'll post my magical formula when I get home tonight. I worked on a bit to simplify it from the original creation I made. Hopefully, a lot will benefit from it in their own projects.

[KyrosKrane]

Skill > Trivial + 40 = 96%
Skill > Trivial + 80 = 97%
Skill > Trivial + 120 = 98%
Skill > Trivial + 160 = 99%
Skill > Trivial + 200 = 100%

Based on what I recall from the Fan Faire, Tanker (the SoE programmer) told us that the forumla for extra successes worked in 50-point increments. For each 50 skill points above the trivial, you would have a 25% reduction in fail rate. This is not necessarily the same thing as increasing the max success rate. When you are 200 points above the trivial, the item is no-fail.

I understand this to mean that if you are above a certain threshold and you would fail the combine, the game does an additional check, rand (1, 4). If this number is less than or equal to your percentage "bonus" * 4, you succeed automatically.

The key difference is that with your formulae, there is still a chance to fail even when over 200 points. With this system, you always succeed.

Of course, I honestly haven't plugged the numbers into a spreadsheet to analyze it all, so I could be off base.

[Turlo Lomon]

I wonder if Tanker could verify this, as it would be illogical and a tad bit more complicated to code.

Skill > trivial +50 = 96
skill > trivial + 100 = 97
Skill > Trivial +150 = 98
skill > trivial +200 = 100

You are missing 99%. Ugh... I realize I forgot to post my formula. I will definitely do it tonight during the patch. It is very simplistic, yet includes all of these varients.

[Turlo Lomon]

As promised, the formula.

=MAX(0.05,IF(C3<16,1,IF(D3>C3+40,MIN(INT(95+(D3-C3)/40)/100,1),MIN(IF(C3>68,INT(D3-(0.75*C3)+51.5)/100,(D3-C3+66)/100),0.95))))

C3 = Trivial
D3 = Skill

Let me know if you need any parts detailed further.