Dashboard using excel

Problem Statement:

Designing an Automated Dashboard and Forecasting System

Raw Data:

• Partner List: For 2018 & 2019, utilize COGS assumption to back-calculate COGS respectively.
• Transaction List 2018 to April 2020: Use 2018 & 2019 as a basis to calculate run rate if needed.
• 2018 & 2019 Actual Transactions & Revenue Numbers: Implement a sample summary table, with proposals for better layout while ensuring availability of each table.
• 2020 Plan Numbers: Including transactions, revenue, COGS, and gross profit.

Deliverables: Final Output

1. Plan Vs Actual Dashboard for 2019, 2020, 2021 by Month:

   • Input page for partner list, revenue item, fee item, forecast period, and setup table.
   • Auto-updating of actual revenue, COGS, and GP when transaction list is updated.
   • Interactive dashboard with drop down menu for partner, transaction type, total, and year selection.
   • Performance dashboard with separate charts for partner, P&L line, and dates.
   • Projection/run rate feature.

2. Forecast & Run Rate Sheet:

   • Calculate future data based on monthly run rate formula where no actual data is available.
   • Utilize actual data from 2018 to 2020 to calculate run rates.
   • Create a factor table allowing user assumptions for projected numbers.

3. Projected Period Options:

   • Choose between 6 or 12 months for the projected period.
   • Selection of projected period on the assumption page.
   • Configuration of start and end dates for projection.