EVE Online Industry - Your first excel warehouse

[@wayneyessir6350]

I love the video. I have been digging deeper into excel this year and this video helped explained new functions. But even better it taught me for use in a pass time I love (video games) but gave me a skill I can use in my professional life (logistics management). Thanks

[@michaelmerriman]

Wow, I never thought I'd be able to sit still and pay close attention for 50 minutes straight on a video about Excel, but here I am, lol. Also this basically just taught me how to use spreadsheets in general because I never knew before.

[@DemersEvan]

Thanks for the great video! very detailed and clear explanations. This is literally a free class on Excel. Ill definitely keep watching and try to get some friends into your videos as well.. amazing information.

[@Mupersega]

You are very welcome, this is just one part of a modular approach that will equip viewers with a bunch of sheets that can all be tied together later. Thanks for watching 😀

[@curtisreker4956]

I must say i am an excel fanatic as well (just ask my wife...), but your level of detail blows my mind and has inspired me to take the bull by the horns. I am extremely excited to see future content.

[@sdeske]

Fantastic, perfect inspiration. Perfect instruction. Multi buy copy and paste, EPIC!

[@message3381]

This is an excellent guide. Thank you so much. I'm getting back into EVE and focusing on ship building and industry and this is so help me get organized. Thanks again. Fly safe.

[@shufflex704]

I was a bit hesitant when inserting the new columns as I thought I would need to alter the formula's to the new location, So happy when I saw that Excel does it automatically. Finished my first ever spreadsheet and I have not stopped smiling. Going to try the next one no assistance from this video. Thanks again

[@Mupersega]

Bravo

[@brianchandler3346]

Thank you so much for these videos on manufacturing and how to leverage excel with the client. It's been an eye opener.

[@Mupersega]

Brian Chandler, my pleasure. Project based learning makes it so much easier sometimes.

[@Ezokewn]

This is an awesome video! As a spreadsheet person, the is conditional statements are really helpful to know.

[@Mupersega]

Ezo Kewnuki, I’m glad it has helped 🤓

[@sandorosvath4848]

Thx for all ur hard work , thx to u i have my 1st warehouse. o7

[@trinitytruth5681]

Eve just morphed into life lessons. :-) Thank you. You have issued a challenge to apply these lessons, I accept... not today.... perhaps tomorrow. once I've watched this a few more times!

[@Mupersega]

Excel is life my friend :)

[@trinitytruth5681]

I did it! Thank you. Definitely learned a thing or two! Looking forward to more. For now I am trying out an experiment to mine and refine my own materials and cut out the middle man... kind of anyway. So I will enter the refining and manufacturing cost as the price for materials and see what happens!

[@Mupersega]

Chris S bravo good sir, refining costs are good but don’t forget also to account for the time spent attaining the materials and thus how much isk you will pay yourself per minute/ hour 🤯

[@trinitytruth5681]

Good point and thank you for the feedback. I'm pretty certain that this path is far from optimal. I can only play minimally and can't justify the omega at this time, so I thought it would be an interesting challenge to role play a poor upstart making his way thru the word. Plus it's teaching me the whole process. Keep up the good work! It's appreciated!

[@afr4y117]

You are amazing bro! Super useful info as I'm a noob interested in industry (i like numbers). I'm not done setting everything up just yet, but I have my 3 BnB sheets, plus my warehouse(minus the shopping list) Edit: this is actually my first experience with spreadsheets, im using google sheets and the shortcuts as well as formulas are identical so i can follow along easily. to test my understanding of your teachings, at 31:06, I paused and did the recieved sumif on my own. thanks so much man.

[@T800-k5b]

Rally useful video thanks, now I've got to add a job tracker and sales profit sheet :)

[@akiraofastra552]

Thanks for these videos. I've found the spreadsheet aspect of Eve to be the best part of playing!

[@Mupersega]

Thanks Akira, nothing beats a good spread. If you would like to see or learn more, please head on over to twitch.tv/mupersega where I stream monday and wednesday nights Australian time :)

[@FiLtheThriL]

Thanks for teaching me excel! A real spoonful of sugar situation

[@QualeQualeson]

You're right, it does seem a little overwhelming. But it's not because I actually tried it (in which case I'm sure it wouldn't be), it's because at a glance, this brings me back to exams and such in a subject I didn't like and that I feared :D That said, amazing practical helpful content! This kinda thing doesn't grow on trees, and despite my traumas, I might come around to your mantra down the road; Excel is life. Lol. Thank you.

[@KaraMooMoo]

Not sure if it was a recent addition, but there is an option when you press the gear icon on the top right of the wallet, with a check box for "show ISK Cents on Amounts" :D

[@Mupersega]

Woot, thank you, If I ever get to doing more with the wallet I shall be sure to mention it and credit you :)

[@WilhelmEdrasill]

Great video - Thank you sir. I will be watching all the other vids you have up and builing/ tweaking along. Couple things that stood out to me was: By bundling taxes into the total cost you cannot break out the amount paid in taxes - to show how much at scale that corp mates will lose if we dont use person X who has max trade efficiency. - it isnt needed but , still the representation could be used to quash any arguments with math.. Average price - the biggest issue here is that by averaging you lose the true representation of COGS ( cost of goods sold). It would seem like it might be better to set up a spread sheet that links all Purchase orders into Manufacturing orders whereby the manufacturing orders will use the cheapest priced item needed. Thoughts? And Finally, you can eliminate a lot of the manual here by adding drop down lists to the ore types for the input and output sections. Select the item cells >data>data validation>list - and ur golden. Or google it.

[@YouCanDo1t]

I appreciate this lesson.

[@gywaite]

Excellent, thank you, LSB, cheers

[@zephirus4483]

man.. those tutorials are nice! I hope you'll continue !

[@oliverleg8848]

That was an awesome experience! Keep it up!!!

[@jamie2511]

Thank you for the videos. Incredibly helpful content.

[@Mupersega]

Jamie Johnson my pleasure, glad it helps.

[@muri8108]

Really appreciate you doing this man. Subbed in hopes it will support you

[@Mupersega]

Really appreciate it Muri, if you want to see or learn more, please head on over to twitch.tv/mupersega where I stream monday and wednesday nights Australian time :)

[@DanShilling]

awesome tutorials man! Keep it up!

[@ravenofra1114]

Great video, I have one question how would you suggest pricing materials which you have sourced yourself? I am trying to use this to organise my PI and work out which things I need to purchase in over and above my production capabilities. But I guess when I get to minerals and stuff the same will hold true as I rat and repo the loot and moon mine quite a bit as well. I was thinking I could use Jita buy price on the input side does that seem sensible?

[@Mupersega]

It's an interesting question man, and one we face on stream a fair bit. The correlation between RL time spent and what you expect to get for that time spent in game I have trouble conceptualizing. I did try once to formulate it; ultimately though, IF I mined or when I do PI I do as you say, I price at jita buy. If however you find a beautiful and streamlined system please tell.. :)

[@smasher491]

It should be the price you would have gotten by selling it yourself by whatever means you would normally sell it. The "standard" then would probably mean the sell price at Jita minus brokers' fees and taxes.

[@Croccifixo]

@@smasher491 This is probably the best way in total to price materials, even if you know you have bought them at a lower price. Lets say you buy a bunch of Trit for 4 isk/unit. When you use it, you can sell it at Jita for 6 isk/unit. With hauling costs and taking off taxes, lets say your effective sell price at Jita is 5.5 isk/unit. Are you crafting for 4 isk/trit, or are you crafting for 5.5 isk/trit? I would say 5.5, as this is the opportunity cost you are paying for the production. It might of course be very cumbersome getting things to market, depending on where you are, which is why you can add a multiplier for the hauling price to find the proper opportunity cost... Seeing as the trit has different values though (the one you bought it at, and the opportunity cost), the calculation immediately becomes a lot more complex, as the opportunity cost might be below the purchase cost at some point, should you then still use this, or should you use the purchase cost?

[@shufflex704]

Extremely good video and very well presented, from someone who has never done anything in Excel before (too old) I am going to give it a try. Out of interest I have watched your other videos and was wondering how many different pages or sheets do you have with your warehouse? Is everything on the one sheet or spread out, So minerals on one sheet PI on another Ships on a third etc etc

[@Mupersega]

G'day Gary, I personally like to compartmentalise the data. Think in two ways, does the data need to be dynamic or will it be static and then break up those elements however pleases you. In my tech 2 ship manufacturing operation I use a single sheet 'warehouse' to: 1. Track all purchases of decryptors and datacores (this information is STATIC - each purchase is recorded) 2. Particulars of the current state of pricing (this information is DYNAMIC - the data pertaining to each datacore type or decryptor type is dependant on part 1) All that to say that the number of sheets you use is dependant on how much information you can comfortably compartmentalise, notice that I dedicate columns to operations. I don't put room for 5 entries and then another thing underneath it as I have then limited the amount of information I can enter before the system breaks. Re your example, YES store the STATIC information pertaining to particular pieces on it's own sheet. i.e. SHIP SHEET (example columns) Race- Class- Techlevel- Name- ManufacturingNow- Selling- Sold- BPOready Minmatar,frigate,1,Rifter,3,0,54,true etc etc This sheet then becomes a store of information you may reference later. ie "what should i build now??" if selling now = 0 and ManufacturingNow < 1 and BPOready = true then show "BUILD "+NAME

[@shufflex704]

Thank you very much for such a detailed reply, I really do appreciate it. Sadly though I really felt outside my comfort level towards the end. I think I really need to build the warehouse first and make different sheets for different compartments. They say repetition is a great teacher and hopefully by the time I have finished 20 or so pages I would have an understanding of that element and be comfortable moving on to the next level. I can only guess at the amount of work it takes to make these videos, especially as over 95% of the Eve player base would not have the slightest bit of interest in them it must seem as though they are (videos) unrewarding. I can't speak for everyone but would like to say that I for one find them a godsend. I have seen on your other videos a mention of having different spreadsheets available, are these spreadsheets something you will be doing in the future or have you drawn a line underneath it all and moved on to other things?. From a purely selfish aspect I would love it if you were to do more and eagerly await the next tutorial video you do.

[@blakebell8533]

What's a free alternative that you would recommend?

[@Mupersega]

you can use excel online or google sheets :) google sheets formulas will be different though, sometimes a little, sometimes a lot.

[@ricardonevesinem]

LOVE IT 😍

[@michaelpembroke4427]

Hey man. Loved this video. you clearly know your stuff and it was great watching this and making it at the same time. I have changed things like added in drop down lists, made a buy order price box (based off average mineral price*0.95) and also made a data base of what BPOs i have, the minerals it costs and the price needed to sell (with a mark up) so i can keep a real time account of the price i need to sell the items at to make a profit. My question now is that is there a way to make a drop down list in the OUT GOING part of the spreadsheet where i can select the item being produced and its quantity AND then get that info to update my current mineral stocks? any advice would be great. And once again, this is awesome

[@michaelpembroke4427]

Actually i worked it out. ill see if i can explain easily haha. First i had made sure i had a database of my BPOs (on a separate Sheet) and what minerals were needed PER RUN (had to add in a units/run column which then divides the cost by that number to get cost/unit). Then in the outgoing page i created a drop down list of all my current BPOS and added in along the top of all the minerals. Then under each one i created a SUMIF function with this function =IF(S2="","",(IF(R2="","",SUMIFS('PRICE TABLE'!B:B,'PRICE TABLE'!$A:$A,$R:$R)*$S:$S))) [price table being the sheet name for the BPO data]. i then changed each one for each mineral. In this case the *$S:$S is where i put the Runs being done. From here i altered the Removed column to a SUM function that married up with the minerals from the newly made table. after testing it out it all seems to be working well. The goal of this was to make it stream lined and so it automated the minerals used part rather than having to do it manually. Hope this all made sense. Also i added in on the Price Table sheet, a minimum sell cost, based off the minerals used multiplyed by the average cost then that multiplied by the markup cost. The function for this last bit is a huge string as i added in IFERROR messages and to get the cost of all minerals added together was quite a bit. took me a few turns to get it right. Thank you for reading.

[@michaelpembroke4427]

Just realised i need to add in the ME factor as when i go to say i have made something to take away from my stock, it doesnt take into account the decimal places from the upgraded BPOs

[@michaelpembroke4427]

And also the structure effectually factor

[@Mupersega]

Bravo, it sounds like you are well on your way. For exact figures regarding the inclusion of ME make sure that if you order minerals for 100 of something that the manufacturing run must also be 100. Also noting that if a run uses only 1 of something that regardless of ME it will always be 1. =if(qty_required_mineral_a=1,runs*1*qty,Runs*ME*qty) Keep going man, I bet you are making connections that if you started again you would already know where you can better your own system🤓

[@michaelpembroke4427]

@@Mupersega Hey mate. i actually finished it today and its all sorted regarless if i do a non round number like 32. i added in a round up function on the end ive run a couple of scenarios and it all seems to work out. Most of this all came from the base of your video so i thank you heaps. did you want a copy to have a look over?

[@mvwood55]

I am trying to make a Plex Buy/Sell tracker using the in-game wallet to copy from. When pasting the wallet into my spreadsheet the individual price does not have units but the total cost comes with ISK as the unit so excel recognizes it as a mixed variable and not currency. How do I get around this so that I don't to waste time manually entering my data but can still use the total cost data for calculations throughout the spreadsheet?

[@Mupersega]

Hi Matt, for me I use a macro, the steps being. Select column with the ISK values in it - find and replace ISK with nothing. That's the basic one, furthermore you might have a sheet called purchase data and create a macro that pastes all - clears isk values - remove "-" values and even sort and filter all in one.

[@lassenlautta]

can you tell me how to track average paid price of item? do i need to write my on program for this .. can't seem to find any tool for it.

[@merlin3717]

i love this idea but i have a question what data do you put in if you mine and refine your own ore into minerals? do you put 0 in the total isk of the input or do you input the lowest price that you get from market or a website like evepraisal?

[@Mupersega]

Hi David, with something like that you really need to price it yourself as mining is not free, you need to account for your time spent. A price of some sort is necessary though, you can as you say list it at the lowest market sell price :)

[@merlin3717]

Thank you for the reply mate I’m gonna make my own warehouse later on as I do a lot of mining and just getting into manufacturing as a little isk input. Again thank you so much

[@merlin3717]

also another question can i nest if functions command for example: if a is empty print blank else (if d is 0 get current price from z else D/C ) in my case z would be evepraisal so as to automate the pricing of mined minerals etc

[@merlin3717]

i got this idea working on google sheets ans the whole sheet works perfectly. i havent done the desired levels and the shopping list yet but input and output and the warehoue itself works fine if anyone would like to have a look let me know

[@kissimo9568]

Thank you, you are mega-mega great. Thank for my understanding than my excel table is a piece of sh*t. Thank you for my future sleepless nights)

[@taylormurphy2551]

Very detailed tutorial, but i'm confused about how you implemented the average price. To calculate the cost of the ship your building, wouldn't you want to compare it to the current price of the raw components (i.e. your opportunity cost, i think), rather than what you originally bought them for? Also, if I bought 100 3 months ago (and maybe already used those units), isn't that going to skew my average cost calculation?

[@Mupersega]

Taylor Murphy the average price of the ship or of the minerals? Yes the average price of the minerals will skew while ever you are buying at different costs. That’s the point of average cost, understand though that after a while you will need to start again and when you clear all the sales data you would reenter the balance as the quantity of remaining minerals at their last average price.

[@Mupersega]

Taylor Murphy if you are after exact job costs you would have to use something more like a job tracking system wherein you purchase only the requisite materials for the job/run. The average price problem is one that I understand but can be addressed by “balancing” your sheet every month or week.

[@shufflex704]

Hi, Sorry to keep bothering you but I have ran into a slight problem that I can not for the life of me figure out how to solve it. I loaded my minerals into the warehouse and ran a manufacturing job taking the minerals out of stock and logging them in the withdrawal section. the section updated giving me costings which was great. However today I did a bit of mining and reprocessed the ore into 3 minerals and added them to the warehouse. I divided the reprocessing cost by 3 as that gives me a true value of the minerals (Iknow it does not account for time but niether does anything else I sell ) Now the problem I have is that when I entered the prices it also changed the overall cost the job I had in withdraws. Is there a way of keeping the previous days jobs prices (The prices I actually paid) ? here is a screenshot, you will see that my tritanium has gone down from 5.4 to 0.2 as I only had 2 left when I started the job. That has inturn knocked the Trit price on the job (Or reasons for withdraw) down to 0.6 whereas it should be 5.4. prntscr.com/l4zxao

[@Mupersega]

Gary Noble, you have options. As soon as you withdraw the materials from the system,the info in the output area needs to be changed to values(from formulas). Do this by selecting the withdrawal entry, copying the entry and pasting it back into the same spot “as values”. This will then lock in the cell results that were being calculated rather than them updating.

[@shufflex704]

Thank you very much. I have managed a sort of Manufacturing sheet now that will also give give minimum corp selling price and minimum selling price on the open market. I am going to attempt a dedicated ship build one with drop downs. Thanks for all your help, without that I would never of even started.

[@Firespyer]

Can you show pulling Material value from Market cost? I mine all my materials so my cost would be 0, I'd like to get the avg market cost to fill in that value.

[@Mupersega]

Firespyer for now, I am not getting into esi as I am not the authority and while I use it, pulling into excel is a can of worms especially when you start needing auth tokens. I would recommend on a new sheet that you make a table and manually enter the price per item of each mineral type. Then use a sumif or vlookup to populate the cost per in your input section. 🤓

[@Firespyer]

Thanks for the reply, I'll give your suggestion a shot.

[@andtechie5210]

Loved it

[@robertrutherford6595]

Is there a reason you don’t import the API/ESI? Do you just prefer to do it by hand or am I jumping the gun in this comment. I haven’t watched whole video yet

[@Mupersega]

Robert Rutherford , I do use esi in some instances but getting data from an api endpoint is still just getting data. One spreadsheets not to get data but to manipulate, visualise, automate and develop systems to achieve a goal. Personalised api calls (those that require authentication) are notorious for being a pain when it comes to excel. These tutorials are not tutorials on esi, they are spreadsheeting and systems creation tutorials.

[@antwan1357]

Eve seriously needs a graphical update , I mean nothing still can challenge the economy of EVE Online , except maybe some new stuff coming out that uses a completly different theme like fantasy theme. For some reason that is working for complex economy themes cause Eve just doesn't allow a competitor in sci fi it seems as i cannot even think of another game , and dont say star citizen or elite dangerous i play those games they dont have anything as near as complex or detailed as eve .

[@Mupersega]

Thanks for your thoughts Antwan, I am gently considering a foray into Albion online, it may have some interesting economy dynamics.

[@antwan1357]

@@Mupersega now you got me curious all those commercials must be getting spammed to you too . The way they advertise , you think it was eve online on horses instead of spaceships.

[@Mupersega]

@@antwan1357 yep, I got baited

[@rickdawson6104]

try this game 5yrs ago

[@williamryder781]

shouldn't the value of the material in your warehouse be determined by the sum of the inputs subtracted by the sum of the outputs? Otherwise you end up with a value that can keep growing despite removing material from the warehouse and resulting in skewed numbers at low stock levels?

[@Mupersega]

I'm not sure I understand William, given enough time yes, averages will start taking over and thus a small stocktaking operation would be required to keep values as current as possible. Granted that this is quite an old video so I have moved well on from doing it this way haha.

[@mattc3581]

The flaw I see with this is that over time if you are adding more and more buy rows then your average price seems to be the combination of all the prices you have paid ever. That isn't a very realistic model for what the cost to build something today is. An improvement would be to have your formula pick up the average warehouse stock value of your mineral at the point in time you take it out, but then copy paste those averages as numbers to fix that price as the cost you incurred to make that item. Your warehouse then needs to calculate the total stock you have as the total received less the total used, and the total isk cost of that stock as the sum of cost prices less the sum of the value of the units used. Ideally you would actually have the average price for stock in your warehouse taken from the current market price, not what you happened to pay for it, potentially meaning your stock could go up or down in value while you are away. Then you would get a realistic cost of using that stock to make something vs just selling the stock back to the market for a profit. (Using the 100 trit you purchased for 100 isk to make something you can sell for 150 isk looks good, but if by the time you make it you could just sell the trit back to the market for 300 isk because its price has tripled, then the 150 doesn't look quite such a great use of your stock.)

[@marwanamr97]

29:53 WTF?!! xD xD xD xD

[@maxiyyt]

"Let's make a 'Warehouse' -Warhouse-, Not a Warhouse, actually, Yea " *Laugh hard. **Clicks - Like/Subscribe.

[@lupagod1759]

Теперь буквально Excel

[@steve7425]

test

[@Mupersega]

Definitely.