[TOOL] Production chain capacity calculator ;)

Discussions about modding and share your mods with other players in this forum.
Post Reply
tromozil
Posts: 12
Joined: Fri Aug 07, 2015 12:58 pm

[TOOL] Production chain capacity calculator ;)

Post by tromozil »

Hello everyone!

I am new to the game and so far I like it very much. There is some information about the game mechanics, that I am missing however.
The most important thing is the calculation of the production capacity of a factory. It is one of the first and most important things any manager should do, when planing a new factory. To my knowledge this is impossible to do with the available information. Maybe the experienced players know how to do it, but I could not find any information in the game and in the forums (except few hints, that it relates to the price and production speed). To resolve this problem, I did some experiments and found the formulas for calculation of the production capacity of factories, farms and mines/oil fields/lumber mills.

SPECIAL CREDIT:

The acquired information I integrated into a spreadsheet, which I downloaded from the forums. The original spreadsheet is made by rmax (Thanks a lot!) and can be found here:
http://www.capitalismlab.com/forum/view ... =90#p11502

HOW IT WORKS:


The spreadsheet consists of 7 data sheets. You should copy the data from the corresponding DBF files into the first four data sheets. The last three are called Calc, Mfg Capacity and Production Chain. Calc I copied from rmax and just added the last column, which gives you the daily production of one large manufacturing unit for the corresponding product.

Mfg Capacity:

Mfg Capacity is the data sheet, where you can plan your production capacity. On top you see the end product box and below are the boxes for the required materials. Light blue means "Write data here" and dark blue means "Read data here".

You should choose your end product, factory size, number of manufacturing units and their training level. Inside the end product box, you will get the daily production capacity.

Inside each box for a required material you can choose factory size (for farms - only medium or large) and you will get:
- daily production for 1 unit
- required number of units to fulfill the needs of the end product mfg units
- daily production of all required units
- daily surplus of the produced material

NEW: Production Chain

If you want to start the production of a new product and close the entire production loop, you want to know how much production capacity you need for each raw material, that would go into the end product. This is not very easy to do with the Mfg Capacity data sheet, that is why I created the Production chain data sheet. As before, light blue means "Write data here" and dark blue means "Read data here".

At the top of the data sheet you can choose a product from the list menu Product, which you want to produce. In the same box you can write how many units you are planing to build, according to level and chosen factory size. You will get:
- daily production for the end product
Below is a bigger box, where the following information is given:
- materials required for the production and required daily amounts for all sub-products
- production/farm/processing/raw material units required to fulfill the demand according to size and level
- production surplus in units and %; you can enter surplus amounts from other factories, that can be taken into consideration

Attention:
Don't forget to check Internal sale, or your competition might eat up your materials and your factory will not get enough!
You should enable "Internal sale by default" in the options. If a competitor buys a single item, before you press the Internal sale button, he will continue to buy this item regardless of Internal sale. To correct this, you should delete the Sale Unit and place it again.

EXAMPLE:

This is example from my current game.
You want to enter the car market. You consider 12 Mfg units to supply all your automobile shops. First you choose "Car" from the falling menu "Product" in the data sheet "Production Chain". Write "12" in the next column "Mfg Units" against Level 1. Choose factory size "Large". You will see the daily production - 72 cars. Below is a list of all required materials: 72 car bodies, 72 engines, 288 tires, 3600 glass, 7200 plastic, 125280 steel, 17280 rubber, 3600 silica, 45 oil, 41760 coal and 41760 iron ore. Now you want to consider the surplus from your motorcycle factory - write it down in the last blue column: 2 engine, 14689 steel, 32 tires, 51782 coal, 61306 iron ore, 11147,4 rubber. You can see the result immediately. To support 12 Car Mfg units, you will need additionally the following production units: 9xCar body, 3xEngine, 4xWheel&Tire, 1xGlass, 1xPlastic,4xSteel, 1xRubber, 1xSilica, 1xOil. The rest of the materials you will get from the existing factories and mines.

FORMULAS: :geek:

If you want to know the formulas, here is what I found out:
Daily production of 1 Mfg Unit = (40000/PRICE)*(PROD_SPEED/100)*(1+0,2*(Level-1))*Size_Modifier
Yearly production of 1 Crop Unit = (10240000/PRICE)*(1+0,2*(Level-1))*Size_Modifier
Daily production of 1 Livestock Unit = (20000/PRICE)*(PROD_SPEED/100)*(1+0,2*(Level-1))*Size_Modifier

Size_Modifier: for large unit = 1; for medium unit = 0,5; for small unit = 0,25.

BUGS:

With some goods the calculation is not exactly correct. E.g. STEEL should give 30769/day, but instead it gives 30768/day. No idea why...

I haven't tested this spreadsheet extensively, so most probably there will be mistakes. I don't care what you do with it, but any feedback will be appreciated.

OTHER INFO:

I have created the spreadsheet with LibreOffice Version: 4.4.4.3. I have tested it with OpenOffice 4.1.1 and Excel 2007.
The spreadsheet contains raw data from the latest currently available Beta v. 2.7.11.
Next step is calculation of possible bottlenecks - sales units, transportation and retail purchase.

UPDATES

v.2 (20.08.2015) added new data sheet "Production Chain";
v.3 (21.08.2015) converted formulas to work with OpenOffice and Excell; added xls version of the file;

DOWNLOAD:
Attachments
MfgCapCalc_v3_Excel.zip
Converted into Excel file format (xls):
(42.63 KiB) Downloaded 426 times
MfgCapCalc_v3.zip
Production chain capacity calculator:
(64.72 KiB) Downloaded 311 times
Last edited by tromozil on Fri Aug 21, 2015 8:39 am, edited 1 time in total.
tromozil
Posts: 12
Joined: Fri Aug 07, 2015 12:58 pm

Re: [TOOL] Production chain capacity calculator ;)

Post by tromozil »

I am happy to see, that few people have downloaded the spreadsheet already, but I have received no feedback. Please, share your opinion!
I want to know if there is any interest in this tool - should I keep updating it or not? As for me - I use it and try to improve it.
I just uploaded a new version, that makes it easier to calculate the complete capacity for a closed production cycle - please, check the post above.
HansOlav
Level 3 user
Posts: 67
Joined: Wed Jan 19, 2011 8:41 am

Re: [TOOL] Production chain capacity calculator ;)

Post by HansOlav »

Hi. I tried this and it did not work as it shoud.

In OpenOffice the formulas work in the top table of "Production Chain" but not in the bottom table. Got a lot of #I/T errors in stead of formulas.

In Excel it was the opposite, not working on top table but it did work on the bottom table.

Don't know if this is a Language issue (I use Norwegian)?

Hans Olav
tromozil
Posts: 12
Joined: Fri Aug 07, 2015 12:58 pm

Re: [TOOL] Production chain capacity calculator ;)

Post by tromozil »

It seems some functions in LibreOffice (http://www.libreoffice.org) are not available in OpenOffice (like IFNA() IFERROR()). I just downloaded OpenOffice and will try to work that out somehow. If I can, I will make the spreadsheet compatible with both programs. In the meantime, if you wish you can try LibreOffice. I choose it, because it seems more active in terms of updates and improvements and its team branched out of OpenOffice.
HansOlav
Level 3 user
Posts: 67
Joined: Wed Jan 19, 2011 8:41 am

Re: [TOOL] Production chain capacity calculator ;)

Post by HansOlav »

No, I have a paid version of Office 2013, and have used only Excel sinns it first came out. But I can import from OpenOffice!
tromozil
Posts: 12
Joined: Fri Aug 07, 2015 12:58 pm

Re: [TOOL] Production chain capacity calculator ;)

Post by tromozil »

I have corrected the formulas and tested the new spreadsheet with OpenOffice and Excel. You can download it from the first post.
HansOlav
Level 3 user
Posts: 67
Joined: Wed Jan 19, 2011 8:41 am

Re: [TOOL] Production chain capacity calculator ;)

Post by HansOlav »

Yes, now it Works, but I think you forgot the "Size_Modifier" in "Production Chain". It is not doing anything when i change Unit Size to a smaller unit in cell G16.

I use the Excel Version.

It's a very nice tool, I like it a lot :)

Obs! When I tried to save it as a new name it say that some formulas are reffering to another spreadsheet?
piermario
Mod Expert
Mod Expert
Posts: 389
Joined: Sun Oct 31, 2010 6:12 am
Has thanked: 2 times
Been thanked: 1 time

Re: [TOOL] Production chain capacity calculator ;)

Post by piermario »

Nice tool i will use to balance new version of realworld mod
tromozil
Posts: 12
Joined: Fri Aug 07, 2015 12:58 pm

Re: [TOOL] Production chain capacity calculator ;)

Post by tromozil »

HansOlav, I don't have Excel on my computer and can not test it, but I haven't put any references to external files. I hope it works fine beside this problem.

Piermario, I am very glad that you find it useful :) Unfortunately, while I was testing the potential bottlenecks Sale Unit -> Input Unit, I found a game-braking bug (at least for me) and I will wait until I get some feedback before doing anything more on this spreadsheet. (you can check my thread: http://www.capitalismlab.com/forum/view ... =10&t=2814 )

Btw. I like your mod very much! I have just one suggestion: Please, publish your current source files as a separate file in moddb for the new versions!!!
Post Reply