Best way to lay out financial data to build a dashboard

[Skullflame]

Hi everyone,

My Excel skills are pretty basic, as I have not had the need to do more than the basics. However, my manager is asking me to provide more robust financial reports, so I would like to try building dashboard.

The information I need to include in my data:

- Cost center number
- Expense type (salary, T&E, and disc)
- Fiscal month
- AOP
- FCST
- Actual spend
- Spend plan

The goal is to provide our leadership team and VP with a visual to see our spend status for the full fiscal year in order to assess risk/opp as a whole. There are 30 cost centers in our organization, so it's a lot of data.

I'd like for the data visual to show the variance between what they spent (actuals) vs AOP or FCST or completed months, and the variance between what they are planning to spend in incomplete/future months vs AOP and FCST. They have asked to see the variance as a total (salary + T&E + disc), but also individually (salary var, T&E var, etc.). But, if anyone out there has experience with large corporate budgets like ours, I welcome your input on other ways to display the data.

Basically my question is really how to structure the data so that I can achieve the visuals that will give them a quick takeaway of their status. Our organization is growing, and it's too much to continue to do manually.

Thank you in advance for your help!
Deb

 

[GraH]

The challenge is getting your data into perfect shape to enable "simple" reporting and dashboarding.
Then learn the basics of data modelling (and DAX) so you can create your dashboards based on powerpivots & charts. Your ETL will start with Power Query.
30 cost centers is nothing. I deal with over thousand at work.