Top 5 Interesting Financial Models
May 27, 2021 | by: David Phillips
The team at Model Metrix has developed many interesting models over the years. We thought it would be fun to describe the top 5 standouts. The common theme is that they were integrated planning models that pulled data in from other systems, had high material impact on the business, and were previously managed with spreadsheets. It is sometimes amazing to think that companies would entrust siloed, error-prone spreadsheets on which to base consequential business decisions.
Without any further ado, they are as follows:
Aircraft Leasing model – this business development model was developed for a major aircraft manufacturer that needed to model out the cost and profitability of long-term leasing contracts. It featured the ability to set the aircraft configurations, duration of lease, preventive maintenance costs, and much more. The model drove an automated proposal creation process that also leveraged Microsoft Power Automate and Power BI. The collaborative proposal process had stakeholder input from different areas of the business. The modeled data was published to a data warehouse that allowed analysis of contract performance for continuous optimization.
Site and Water Management model – this complex set of integrated models for a mining operation calculates cost of production across mining and manufacturing locations. It uses operational data to drive the financial forecast. While each site has a dedicated model to reflect their nuances, the aligned output data then flows into a consolidation model (where other financial aspects are added) for a holistic view of operational costs and financial performance. The water management model uses information from the site models and calculates tailings pile growth and water reservoir balances and capacities, so all aspects of the business are using synchronized datasets. It informs critical decisions on accelerating water treatment/relocation, change in production or moving water to emergency ponds when heavy rainfall is forecasted.
Food Processing Recipe model – this “soup to nuts” model for a manufacturer of coffee concentrate incorporated different coffee types (BOMs), plant costs, production runs and much more to determine the lowest cost and highest margin for producing coffee concentrate products. It included a number of dynamic “what if” BOM and timing scenarios to derive the most cost effective “recipe” for production. The data was then pushed into the MRP system and incorporated into the production schedule.
University Planning by Cohort model – this waterfall enrollment model was commissioned to provide long range planning for a large national university. The model considered cohorts of students, the timing of those students as they moved through the university as well as periods of non-university participation (national service requirements) and the distribution of schools/departments of study. The model was used to plan expansions/contractions within a $1.3 billion operating budget with associated endowments.
Oil Products Production Pricing model – this comprehensive model was used for the distillation of crude oil into a variety of petroleum products such as gasoline, diesel, kerosene, jet fuel, heating oil, and finally resins and plastics. The full production lifecycle started with the purchase and receipt of crude oil and then modeled costs and prices along the production value chain to determine margin for individual products. It tracked the volume inventory of products and updated the going market price to dynamically present margin and profitability.
We continue to see novel use cases for modeling such as the development of model-driven data products, project finance for renewable energy, and more. As an example, check out the Solar Energy Project model we recently published to our Modeling-as-a-Service portal..
Have an interesting modeling challenge and want to see how we can provide a cost-effective solution, contact us for a discovery call.