| Welcome Guest | Login |
Index
| Recent Threads
| Register
| Search
| Help
| 
| View Unanswered Threads
|
 |
Forums » List all forums » Forum: Tips & Tricks, Questions & Answers » Thread: Our Shoppe Spreadsheets |
|
Thread Status: Normal Total posts in this thread: 312
|
[Add To My Favorites] [Watch this Thread] [Post new Thread] |
| Author |
|
|
Whiplash
Joined: Jul 28, 2003 Posts: 893 Status: Offline |
Well I figured I'd update this post so people looking for the most up to date spreadsheet wouldn't get confused. Faulkston has pretty much taken this project over but I give him advice from time to time though :P Anyways, heres the link of the most recent spreadsheet: http://www.angelfire.com/fang/faulkston/shop_sheets.html
---------------------------------------- SO Good Omens
---------------------------------------- [Edit 2 times, last edit by Whiplash at Jun 2, 2004 6:21:52 PM] |
|||||
|
|
DrunknBfly
Joined: Aug 19, 2003 Posts: 1141 Status: Offline |
Brilliant! I'm trying this out immediately! :D ---------------------------------------- Pierut of Crimson Tide, formerly Looterati, not dead.
|
|||
|
|
bokodasu
|
What fantastic timing! I was just going to get started on doing this myself today, and now I don't have to. And it is indeed very colorful and much prettier and easier to look at than mine would have been. ---------------------------------------- [size=9]Tomyris, Cult of the Red Mantis, Looterati Bonnie, Octafish Dream, Viridian ---------------------------------------- [Edit 1 times, last edit by bokodasu at Jun 2, 2004 9:29:23 PM] |
||
|
|
alanafiona
|
/em picks her jaw up from the floor. Wow. So much prettier than the thing I'd written.... Umm. Thank you! ---------------------------------------- Alanafiona, distracted Captain of the Crimson Tyrants Queen of the Sailors of Fortune Why, yes, we are a bit strange - after all, we're lego pirates! |
||
|
|
katwarrior
|
you poor poor soul next time use the sumproduct function to make life easier : -/ I can edit it if you like ? ---------------------------------------- Freefunctor NakedChef Mostly an inactive player |
||
|
|
guppie
Joined: Sep 1, 2003 Posts: 178 Status: Offline |
You might also try naming the commodity cost cells you you can drag the formulas through without having to correct the cell references.... ---------------------------------------- Jinia ~a puzzled pirate since Sept 2003. Retired Captain of the Crazed Mob Retired Princess of Cold Steel First and Former Governor of Wrasse Beginning a new life on Sage! |
||
|
|
Silverstar
Joined: Oct 15, 2003 Posts: 517 Status: Offline |
I'd been working on my own spreadsheet for Shipyards. It's rather ... over the top, and it breaks out the data rather extensively, but I like having that much information available. Anyways, at the advice of Arabella, before posting it, I've gone through and added comments to many of the cells. Now that I've done that, here it is. Shipyard Costings v4 (OpenOffice) Shipyard Costings v4 (Excel) These have not been updated for the current economy! I use OpenOffice, but I've tried to ensure that the spreadsheet works in Excel, too. As far as I know, all the formulas work in Excel, but as I've made some changes since the last time I verified it, I could have booched something. The formatting might be a little booched, too, from what I've been told, but that doesn't affect functionality. The OpenOffice version requires OpenOffice 1.1.1, I believe. This is version 4 of the spreadsheet. Version 5 will change the tables a bit so I can specify a default markup, plus override any use price. PS: This is a'sanitized' version. The values for the costs and charge prices are not what I actually use. ;-) Edit: I've updated the mime.types on the webserver, so now browsers should not try to display the OpenOffice version. ---------------------------------------- Silvermoon, First Mate, Silver Dragon Trading Company Silverstar, Instructor, Puzzle Pirates Academy (retired) ---------------------------------------- [Edit 3 times, last edit by Silverstar at Jun 3, 2004 12:05:08 PM] |
||
|
|
Riah
Joined: Dec 25, 2003 Posts: 187 Status: Offline |
Beautiful Mate! /me bows Aegean- ---------------------------------------- Aegean - Black Death Remember RobertDonald
|
|||
|
|
Whiplash
Joined: Jul 28, 2003 Posts: 893 Status: Offline |
Why do I have the feeling yer talking about the Sale Price Formula :P Well I didn't know about the Sumproduct function, I was only using my basic Excel knowledge. I kept thinking there had to be an easier way but at the time of creation I thought what the heck... I think I see what yer saying and I can edit it by myself but I don't think I understand the use of the function totally. Maybe ye can edit one of the smaller sheets like Apoth or Still and I'll do the rest, just so I can see what ye mean.
I'm not quite sure what ye are referring to. I know about naming cells and tables but I'm confused about what ye mean about dragging the formulas through. Thanks for the suggestions mates and looking at Silverstars sheet gave me a couple more ideas like Flag Discounts and such. I'll see what I can do about those. Keep the comments coming. ---------------------------------------- SO Good Omens
|
|||||||
|
|
Silverstar
Joined: Oct 15, 2003 Posts: 517 Status: Offline |
I think what they were referring to is the ability to just copy and paste a formula from one cell to another, without having to make any changes. Earlier versions of my spreadsheet were hardcoded, but V3 is when I switched to using data regions to refer to tables, and named cells. Instead of doing $A$1 to tell it to not change the value, I can just say 'TaxRate' - at the same time, I do use cell references when I want to be able to copy the formula from one cell to another, but want the values to be relative to where the inquiry is - $A1 to always look in column A, or A$1 to always look in row one... actually, this is less a benefit now, but earlier revisions of V3 had whole tables that had formulas I could copy to several columns - the spreadsheet is more complex now, so while I can copy and paste inside one column, seperate columns still are different. For all that mine is complex, I think it doesn't need to be - it could be a mostly static sheet that showed data for all the ships at once, but I like the dynamic price rate selection. ---------------------------------------- Silvermoon, First Mate, Silver Dragon Trading Company Silverstar, Instructor, Puzzle Pirates Academy (retired) |
|||
|
|
Whiplash
Joined: Jul 28, 2003 Posts: 893 Status: Offline |
Ok I tried out the Sumproduct in what I think was the way ye were referring to, but it won't work the way the sheets are set up now. The arrays are different dimensions. I would have to switch around the upper left table for it to work. Now I am kinda against doing that because: A) it looks good right now in my opinion :P B) Since ye would be transferring numbers from the Manage Prices screen in order to use the sheets it makes it less confusing going top-down. Of course if this isn't what ye were talking, feel free to correct me. I take criticism well :) ---------------------------------------- SO Good Omens
|
|||
|
|
54x
Joined: Jul 28, 2003 Posts: 7142 Status: Offline |
You can use commas to connect disconnected fields in a SUM() operation. eg. SUM(A1-A4, A6-A8, A11) will sum all of those areas together. This way the spreadsheet can be arranged however you want and you can still sum things. Combined with naming cells this makes for a very portable spreadsheet. ---------------------------------------- Diamondblade, Cartographer, Crimson Tide. from Midnight. Dear sir or madam can you read my book, it took me years to write, will you take a look? |
||
|
|
katwarrior
|
Not looking at the spreadsheet right now, I remember thinking what you needed to do was multiply iron prices, then use sumproduct on everything after whatever followed iron (don't remember now). Add the two quantities and that is total price. That way the arrays being multiplied were of the same length. Is the fact that one is vertical, the other horizontal a problem ? Irksome if so. I didn't know about sumproduct either until I had to make a spreadsheet for the shop ... I couldnt believe there wouldnt be some function built in, was just too tedious to do by hand, and got lucky and was right. There is some comma thing you can use for sumproduct, but i don't think it has the desired property. Havent experimented to check though. ---------------------------------------- Freefunctor NakedChef Mostly an inactive player |
||
|
jaywilson
 Joined: Apr 12, 2004 Posts: 32 Status: Offline |
Whiplash, Silverstar excellent work with the sheets! Thank you for sharing them with the community :) /me hands out rum ---------------------------------------- otherwise known as: Jamalia (on all oceans) |
||
|
|
Tedv
|
Does this spreadsheet tell you profit per labor spent? I find that much of the time, the queue is the bottleneck for a shop (although less-so now, given the labor crunch). |
||
|
|
Excerebrose
|
It is a very nice piece of work. However, I've inputted all the info for my weavery and the order prices(with tax) do not add up to the actual order prices. Judging from the amount of descrepancy it was fairly simple to find the error in the equations. On the Weavery page, column T (Sale price w/ tax) references G5-G7 for labor calculations, which should be instead H5-H7. In effect it is using the labor paid to workers instead of the Labor Use Price to calculate the final price. The same reference error is made in column S (Sale price w/o tax). Simple enough to fix, just though you should be aware of it. I have not checked the other shops pages to see if similar miscalculations exist. I recommend if you're using this (or any tool like it, for that matter) with your shop you double-check it against the actual game prices. Still, this is saving me a ton of time over the similar project I'd started working on, so thank you very much. --Excerebrose |
||
|
|
blue_jacket
Joined: May 1, 2004 Posts: 894 Status: Offline |
bump This is a great tool. Any plans to update this now that the labor tax has been added? ---------------------------------------- -Yaten YPPedia administrator. Mostly retired. |
||
|
|
Silverstar
Joined: Oct 15, 2003 Posts: 517 Status: Offline |
My shipyard spreadsheet is constantly being updated. v5 is pretty different from v4, and already has the new tax system inplace. but I need to make some tweaks, still, redo the formulas for one whole subsection of the spreadsheet, and add a (third) dropdown menu. I also have to update large amounts of notes. Nonetheless, I should be able to post my new version, soon. It's much improved. ---------------------------------------- Silvermoon, First Mate, Silver Dragon Trading Company Silverstar, Instructor, Puzzle Pirates Academy (retired) |
|||
|
|
blue_jacket
Joined: May 1, 2004 Posts: 894 Status: Offline |
Thanks mate. I think I was able to figure the labor tax out, and me numbers are coming out right. I'll be looking for yer version soon, though! ---------------------------------------- -Yaten YPPedia administrator. Mostly retired. |
||
|
|
Mofiru
|
Great work Whiplash!! i have made it my self too just on a paper:P ---------------------------------------- The Big and Mighty ''Triton's Might'' - don't now yet what(Officer or Senior Officer :)) There's a fine line between an attitude problem and thinking clearly |
||
|
|
Whiplash
Joined: Jul 28, 2003 Posts: 893 Status: Offline |
Ya hmm updates....er right :P Danged new labor tax /em grumbles something about making us spreadsheet makers lives more difficult I'm sure I'll get around to it sometime, I've been really busy with work and such and have also taken a step back temporarily from the shop business. But if ye really want MY spreadsheet updated, I'll see what I can do :) ---------------------------------------- SO Good Omens
|
|||
|
|
Queanie
Joined: Apr 12, 2004 Posts: 658 Status: Offline |
Yay Whiplash! I for one would be very grateful if ye could update yer spreadsheet as a result of the changes to taxes. ~ Jez ---------------------------------------- Jezabella Supposedly retired, but recently seen skulking around on various botes |
||
|
|
BehindCurtai
Joined: May 25, 2004 Posts: 12118 Status: Offline |
Try mine (shipyards): http://www.nccom.com/~michael/ftp/ShipYard-final.model www.quantrix.com 1. Takes into account the effect of rent and work usage. 2. Takes into account both calculated cost and desired selling price. 3. Allows three seperate sets of expenses and charges, for what-if's 4. Computes both poe and percentage profits 5. Compares the three sets of expenses to show you the percentage range that each piece can vary. 6. Fits on an 800x600 screen, no scrolling. 7. Can be easily expanded to include frigs (but it won't fit on screen at that point.) ---------------------------------------- The political and economic blog of a strict constitutionalist http://StrictConstitution.BlogSpot.com/ |
||
|
|
Whiplash
Joined: Jul 28, 2003 Posts: 893 Status: Offline |
Well since I took a step back from Shopkeeping at the moment, I kinda left it...its still on my computer though. I do know that a flagmate of mine has gone through the process of adding in the new tax format. Faulkston is his name. Try contacting him to see if he would let ye get a peek at it :) As for myself doing some updating on my own, I'll get around to it eventually. Schools starting soon so I gotta do something to pass the time in class :P ---------------------------------------- SO Good Omens
|
|||||
|
|
Queanie
Joined: Apr 12, 2004 Posts: 658 Status: Offline |
Well..... as a matter of fact straight after posting I thought.. why don't I have a stab... and as a result I have unashamedly spent this morning re-developing your spreadsheet (when I probably should have been, you know, doing the work I get paid for :P) but only for the ship yard so far -should be easy, or at least not too hard to update it to include the other shop types. Anyhoo, once I get home and make some last tweaks whilst I can be logged into game I am happy to post it for download (for as long as my bandwith allocation can stand it) if anyone is interested in seeing it/using it.* I hope you don't mind that I have used your speadsheet as the base Whiplash :S Jez ~ enjoying herself mucking about with spreadsheets *disclaimer - I'm not a VLookUP data grouping guru so it's not uber sophisticated or anything. ---------------------------------------- Jezabella Supposedly retired, but recently seen skulking around on various botes |
||
|
|
Jacktheblack
Joined: Mar 24, 2004 Posts: 1637 Status: Offline |
Currently the updated spreadsheet is being worked on. I have been testing it for Faulkston. So far the only things not done are the Tailor and Ironmonger. We find a little problem with compatibility between open office and excel that we are trying to fix. This should be done sometimes in the near future. |
||
|
|
MizzUnique
|
BUMP As a new manager of a ironworker stall, I'd love a new spiffy spreadsheet to work on some costs, my mind is boggling right now lol. Has the final version been released yet Faulkston? Thanks, Mizz ---------------------------------------- Mizzunique Sheogorath says: Nemo calls everyone lad or lass though, since he's like a god and we are his children. Sheogorath says: I can't believe I lost a war to a girl in pink. Next time you won't be so lucky. |
||
|
|
Faulkston
Joined: Jun 4, 2004 Posts: 20555 Status: Offline |
I'm currently working on the iron monger sheet. There seems to be an incompatibility between Excel and Open Office data validation rules, though this only affects the cells for entering the colors of the guard and handle of swords. EDIT: The whole edifice is complete, now I'm getting someone with Excel to port it from OpenOffice. ---------------------------------------- Avatar by Carribean
---------------------------------------- [Edit 1 times, last edit by Faulkston at Aug 9, 2004 6:01:30 AM] |
|||
|
|
Fluffy
|
very nice features all; but short of spending $1k USD, how would anyone view this masterpeice? Can i get a show of hands, how many people have quantrix? -FluffyCloud |
|||
|
|
BehindCurtai
Joined: May 25, 2004 Posts: 12118 Status: Offline |
You get a 30 day trial. When I developed that I had no idea that quantrix was $1000. I just found that it was a new version of Improv, and since I can't stand normal spreadsheets (I seriously considered buying a used slab just to get Improv), I used it. ---------------------------------------- The political and economic blog of a strict constitutionalist http://StrictConstitution.BlogSpot.com/ |
||
|
|
[Show Printable Version of Thread] [Post new Thread] |
Powered by mvnForum
mvnForum copyright © 2002-2006 by MyVietnam.net