Dear All
I hope I don't get a kick from the mods for starting a new thread but couldn't really find what I was looking for by searching.
I am a petroleum economist modeling several assets of an oil major in the UK. Data for each asset is entered into a worksheet on the "Inputs.xls" workbook. Assets have interactions like cost-sharing and economic cut-off calculation which is the reason why they are bundled together in the same workbook. I call this stage A.
Outputs of stage A are then fed into a standard template that generates the financial and economic results of each asset as a separate excel workbook. Let's call this stage B.
I attached a schematic at the end of this post which shows how it works. The only part I haven't talked about is the Engine which is written in VB6 and does this:
1. Opens "Inputs" workbook
2. Goes to "Asset A" sheet
3. Looks at the list of variables needed for the "output template" to do the final calculation. The list is in a worksheet called "import" in "output template". Let's say "production", "price" and "cost" to make it simple.
4. Looks for those keywords in the first Column of "Asset A" sheet.
5. Reads the values in front of that keyword which is actually the forecast over next few years let's say next 20 years. So, 20 values for "production", "price" and "cost" each.
6. Copies those values in proper rows in "import" sheet in "output template"
7. Saves it as "Asset A Output".
8. Repeats the same for all other assets.
You must be asking what the hell is the question by now!
Please bear with me for another paragraph. The engine has two problems. First, it is extremely slow. Second, it is a disconnection point between input and output and it should be run each time the input changes.
I am looking into replacing the engine with something faster and more resource-efficient.
My first thought was very simple, multiple copies of the "output template" each linked back, row by row, to the variables in the relevant input page. Problem with that is the lack of flexibility on changing the "output template". A change should be done on all asset outputs which is time-comsuming and prone to human errors. The other problem was adding a line by the user in inputs would disrupt the links.
Second solution was vlookup. Gives flexibility to location of the rows to be read but increses the process time dramatically. The problem of several instances of "output template" still exists.
Third, an excel-friendly database to send the data from "inputs" workbook to and read them into "output template" for each asset in some manner.
I am looking for advice from experts to arrive at the optimum solution before investing too much in one alternative and having to change it midway. What is important for me is the speed, clarity and use of a standard template to generate outputs.
Any idea or reference to any material that might help me in making the decision is very appreciated. If you are still with me at this point, I thank you for your patience!
Bests,
z3r0sUm
I hope I don't get a kick from the mods for starting a new thread but couldn't really find what I was looking for by searching.
I am a petroleum economist modeling several assets of an oil major in the UK. Data for each asset is entered into a worksheet on the "Inputs.xls" workbook. Assets have interactions like cost-sharing and economic cut-off calculation which is the reason why they are bundled together in the same workbook. I call this stage A.
Outputs of stage A are then fed into a standard template that generates the financial and economic results of each asset as a separate excel workbook. Let's call this stage B.
I attached a schematic at the end of this post which shows how it works. The only part I haven't talked about is the Engine which is written in VB6 and does this:
1. Opens "Inputs" workbook
2. Goes to "Asset A" sheet
3. Looks at the list of variables needed for the "output template" to do the final calculation. The list is in a worksheet called "import" in "output template". Let's say "production", "price" and "cost" to make it simple.
4. Looks for those keywords in the first Column of "Asset A" sheet.
5. Reads the values in front of that keyword which is actually the forecast over next few years let's say next 20 years. So, 20 values for "production", "price" and "cost" each.
6. Copies those values in proper rows in "import" sheet in "output template"
7. Saves it as "Asset A Output".
8. Repeats the same for all other assets.
You must be asking what the hell is the question by now!
Please bear with me for another paragraph. The engine has two problems. First, it is extremely slow. Second, it is a disconnection point between input and output and it should be run each time the input changes.I am looking into replacing the engine with something faster and more resource-efficient.
My first thought was very simple, multiple copies of the "output template" each linked back, row by row, to the variables in the relevant input page. Problem with that is the lack of flexibility on changing the "output template". A change should be done on all asset outputs which is time-comsuming and prone to human errors. The other problem was adding a line by the user in inputs would disrupt the links.
Second solution was vlookup. Gives flexibility to location of the rows to be read but increses the process time dramatically. The problem of several instances of "output template" still exists.
Third, an excel-friendly database to send the data from "inputs" workbook to and read them into "output template" for each asset in some manner.
I am looking for advice from experts to arrive at the optimum solution before investing too much in one alternative and having to change it midway. What is important for me is the speed, clarity and use of a standard template to generate outputs.
Any idea or reference to any material that might help me in making the decision is very appreciated. If you are still with me at this point, I thank you for your patience!
Bests,
z3r0sUm
