Project Cost Estimating, Tracking, Forecasting, and Reporting

Hello friends-

This is a loaded question. Please be civil. . .

Background -

My company runs hundreds of projects of different size and complexity every year through multiple project managers. I have been tasked to create a tool that has that has the functionality to estimate project costs, submit for AFE, track spend, forecast, and meet several reporting requirements in a consistent yet scalable way. (I.e. small projects require less rigor than large-dollar ones)

This tool will also need to be able to integrate with other programs and software for cost reconciliation. This may be as simple as export and import through excel. In addition, the tool needs to roll-up to show all projects' spend performance, which will incorporate budget, actual spend, forecast, and pull some information from the other programs or tools mentioned at the beginning of this paragraph. (dashboard etc)

The problem / question I pose to the forum members is what is the best software to use? As this will be a very large amount of information over time, should I lean towards a MS Access database type tool or is there a possible way to enable excel to function without bogging down? Another item of concern is MS Access' lack of calculation abilities. I have considered different ways to create stand-alone tools to perform some of the separate tasks but would like to centralize if possible.

Has anyone seen anything like this work successfully? There has been suggestions to utilize 3rd party programs like Killdrummy or EcoSys; however it is difficult to get the "silver bullet" without a budget...

Like I said, I know this is a broad question but would appreciate any of your professional input.

AlKey -

Thank you for your kind welcome and feedback. I am new to Access but feel like I have a general understanding of how it works. If I were to build the following tables would you suggest any changes or improvements?

Project List
Project ID - (AFE number)
PM ID
Project Name
Description

Level 3 List
Element Name - (AFE extension, when added to project id, this will be a budget for a specific scope of work such a civil, mechanical, etc.)
Element ID -
Project ID - one element name can be associated with many projects (civil, mech, electrical, etc.)

Level 4 List
Activity ID - activities will be planned for each afe (combo of project id and element)
Element ID
Category
Activity

Estimate List
Activity ID - should be linked back through all 3 previous tables
Item Name
Item ID - each activity ID may have many items
Estimate Unit
Estimate $/Unit

Actuals List
Actual ID - should be able to query to compare actual vs estimated for items, activities, and roll-up to overall budget
Item ID
Actual Unit
Actual $/Unit

Project Manager List
PM ID
Surname
First Name
Area
Field

Any feedback to get me started? Essentially this is a work breakdown structure or a cost breakdown structure for those project managers and project control people out there. I am currently researching table relationships so hopefully will be able to manage that on my own.

Thanks