DIY Automation Quick Study

How to Use Excel's Solver

The Solver add-in to Excel expands your analytical toolkit by optimizing your model output to fit your goals, such as minimizing or maximizing profits, costs, portfolio returns or units produced. It’s like a powered up version of goal-seek. Dr. Kevin Kelly shows how to put Solver on your toolbar, set the model objective, set up your model, establish operational constraints, and let it run!

 

DIY Automation Quick Study

Automated Forecasting in Excel

What is the easiest way to have Excel create an automated forecast leveraging your time-series data? Dr. Kevin Kelly takes a quick trip to the “Forecast Sheet” in Excel’s data tab to show how to instantly create a forecast, including seasonality and adjusting for confidence intervals.

DIY Automation Quick Study

Web Scraping with Power Query

Getting your data into Excel can be a chore, but Dr. Kevin Kelly, shows how to “scrape the web” to pull data in from URLs, transform and load it via Power Query to create a table ready for use. You can follow his methodology to pull in data from many external sources, including PDFs, databases and SaaS platforms.

DIY Automation Quick Study VIDEO SIX:

Beyond Excel: Apply Power Query, Power Pivot and Pivot Tables to Your Analysis

Structuring your data in Excel into a tabular format makes it easier to use Power Query, Power Pivot and Pivot Tables, and that helps you create faster accurate, auditable, dynamic data pulls. Paul Barnhurst presents five rules to power your analysis!

Download the Attached Spreadsheets Here & Here

DIY Automation Quick Study VIDEO FIVE:

Using Automation & AI to Improve Reporting

Automation for FP&A exists for more than just calculation, it also helps for communication! Ben Wann shares two of his favorite tips for improving and expediting his variance reports with simple tools that add natural language generation and self-service editing to his toolkit.

DIY Automation Quick Study VIDEO FOUR:

Low-code/No-code Tools for Automation

A continuing challenge for FP&A is getting our data in in the right place and in the right format so that we can work on it! Erik DeMar shares an example of how low-code/no-code tools can help you to link data sources and uses, and more!

DIY Automation Quick Study VIDEO THREE:

MONTE CARLO SIMULATIONS IN EXCEL

Finance's response to uncertainty is to maintain multiple points of view simultaneously. Monte Carlo simulations can support this by providing probabilities for different outcomes and in this video, Danielle Stein Fairhurst shows how to build one in Excel. Her spreadsheet is also available to download so you can work through the example.

Download the Companion Spreadsheet Here.

DIY Automation Quick Study VIDEO TWO:

DATA IMPORTS

Extracting data from various systems and putting it into a usable form is a continuous challenge for FP&A professionals. Carl Seidman of Seidman Financial demonstrates simple tips to apply SUMIF and INDIRECT to these challenges with the click of a button, making you a more efficient finance pro.

Download the Companion Spreadsheet Here.

DIY Automation Quick Study VIDEO ONE:

BUILDING A ROLLING FORECAST IN EXCEL

Danielle Stein Fairhurst of Plum Solutions demonstrates how to build a rolling forecast, including dynamic formulas and dynamic seasonal linear trends (using FORECAST functions). Danielle also shares her excel file to download for your own use.

Easy helps and hacks for your FP&A life, from AFP. 

Download the Companion Spreadsheet Here.

We know that the world does not stop at the end of calendar year, and that updating models is both a significant effort and risk factor.

  • Building a rolling forecast can help you to reset your model to focus on time periods without using Lookup functions or adding infinite columns.
  • Dynamic formulas make your models flexible and changeable and can save you time while decreasing model risk. 
  • Automated forecasting tied to rolling forecasts provides a view to any period. In addition, it creates a  baseline, “naïve forecast” to compare actual results versus business projections.
  • Graphically integrating actuals, budget and forecast can help to deliver your finance message.