From the course: Excel Power Tools for FP&A: Power Query and PowerPivot

What does FP&A look like without power tools?

From the course: Excel Power Tools for FP&A: Power Query and PowerPivot

Start my 1-month free trial Buy for my team

What does FP&A look like without power tools?

“

- [Instructor] Excel is used by close to a billion people. It's one of the most popular and widespread finance tools on the planet. But as great of a tool as Excel is, it won't always be the best option for all things FP&A. But with the additional functionality of Excel's power tools, like Power Query and Power Pivot, we're going to be able to take financial planning and analysis in excel to amazing new heights and so much further than ever before. When my clients ask me how I'm able to build such high-performing FP&A models and analysis, I often respond with the same answer, "It's all about the process." Many people don't think about processes in Excel for data management and model building, but that's the key to what we're going to do over here in this course. We'll be working with a company H+ Sports, a sportswear retailer with a large online e-commerce presence. The company is growing rapidly, but the CFO, whose name is Jamie Block, is struggling to make sense of her data. There are just too many disconnected files. Everything is done in Excel, and Jamie's FP&A analyst, Jocelyn Chen, is doing a lot of her modeling and reporting manually. Jamie and Jocelyn have come to you for support. They know that you'll be able to help them clean up their data, streamline their processes, and get a better sense of what's going on in their business. You've offered to help them implement Power Query and Power Pivot. With these tools, you'll be able to help Jamie and Jocelyn present their information in ways that are more meaningful. It'll reveal where the company is winning and where the company has issues. We'll standardize the data flows, models and reports so that meetings and presentations are more efficient uses of everyone's time. Here you can see that we have a very manual financial model. Product names are all hard-coded here in column B, dates are all inputted manually. Data is brought in through a series of rigid SUMIFS calculations, and the totals, they are not dynamic. If Jocelyn wants to update this easily, she can't do it. It's going to take her minutes, if not hours, to update her ranges and her datasets. But when I use Power Query to build dynamic tables, I can leverage them to build dynamic models like this one. This dynamic model gets me the exact same outputs as the manual model, but all of these outputs are 100% flexible. They are dynamically tied to the inputs and the data, which means that as the data updates, so does the model. These power tools, combined with dynamic modeling and reporting, will make FP&A at H+ Sports far more valuable. And that's what our objectives are throughout this course.

Contents