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

Unlock this course with a free trial

Join today to access over 24,600 courses taught by industry experts.

Solution: Connect data to a financial model

Solution: Connect data to a financial model

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

Start my 1-month free trial Buy for my team

Solution: Connect data to a financial model

“

(upbeat electronic music) - [Person] All right, how did you do? This was a real challenge, not just an Excel exercise, but really thinking about how you would build a recurring FP&A data import process for updating this financial model. What did you come up with? Well, here's how I would go about accomplishing these five tasks. First, we wanted to create dynamic start months, end months, and months in the model, how can we go about doing this? First, I'm going to type in "=MIN," I'm going to go over here to my transactions through September, and I'm going to point to the "EOMONTH" column. There we go, if I hit Enter, this gives me January. Now, I'm going to use this same exact approach, except I'm going to copy this, go over to "End Month," paste it in and change the "MIN" to "MAX." There we go, now, I get September. Finally, I'm going to go "=month" from September "-month" to January, but I get eight. Why? Because nine minus one gives me eight, I'm going to need to add a one to get…

Contents