The course is presented in three parts. First, the course reveals the 3 shortcomings of presenting a pro-forma report. They are:

  • Hard to slice and dice
  • Hard to build a collapsible table on timeline
  • Not for self-service

Next, the course presents 3 challenges in building a Pro-Forma report and provide solutions for the challenges. They include:

  • How to Track On-hire Days for Each Vessel?
  • How to Track Daily Hire Rates for Each Vessel?
  • How to Calculate Net Hire Revenues?

Last, the course presents another 3 challenges in building a Dashboard report and provide solutions for the challenges. They include:

  • How to convert Pro-forma report to a Dashboard report?
  • How to build the Slice-and-dice function of a dashboard?
  • How to display budgets, actuals and off-hires all in one chart?

 

Course Key Concepts: Pro-Forma report, Dashboard report, Power Query, Unpivot, Reference, Merge queries, Slicer, Timeline, PivotChart, Combo Chart, Secondary Axis.

Learning Objectives
  • Explore and understand when and how to build a pro-forma report or a dashboard report.
  • Identify and master the “golden” formulas to track days based on start/end dates and this/next periods.
  • Discover the way to model “staggered” daily rates and summarize into one table.
  • Recognize how to convert a cross-table to a flat-table.
  • Explore and understand how to build a self-service dashboard that enables slice-and-dice.
Last updated/reviewed: August 8, 2023
Course Search
1 Review (10 ratings)

Reviews

4
Member's Profile
The test is too academic and does not help verify whether you understand the work in the class. Otherwise I feel there are a number of good tricks and approached and the material is relevant.
Prerequisites
Course Complexity: Foundational

Basic Excel knowledge
Excel Pivot Table: basics
Power Query: Ultimate Data Transformation
Example: be able to open one Excel file and connect to external data files, etc.
It is recommended to take other Excel Magic series courses by Lenny Wu.
Excel Magic 1: Building Dynamic Formulas
Excel Magic 2: Building Your Report Generation "Bot"
Excel Magic 3: Automating Data Entry with Data Feeds
Excel Magic 4: Ultimate API Reporting in Excel
Excel Magic 5: Building Interactive Budgeting Model in Excel
Excel Magic 6: Excel's 7 Languages
Excel Magic 7: Creating Excel Charts like a PRO
Excel Magic 8: Transforming a Pro-forma into a Dashboard
Excel Magic 9: Building a Project Tracking “Gantt” Chart

Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact: For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Instructor for this course
  Course Overview CPE
Course Syllabus
INTRODUCTION AND OVERVIEW
  Introduction to Excel Magic 8: Transforming a Pro-forma into a Dashboard0:24
  What is a Pro-forma Report0:56
  Dashboard comes to the rescue!1:20
  Instructor Introduction 1:09
  How this course is compared with “other similar courses”?1:21
  What you will get from this course?0:40
Business Case: Vessel Fleet Revenue Stream
  Business Case: Vessel Fleet Revenue Stream1:46
  Pro-Forma Reporting Challenges0:31
Challenge No. 1
  Challenge No.1: How to Track On-hire Days for Each Vessel (Part 1)4:30
  Challenge No.1: How to Track On-hire Days for Each Vessel (Part 2)3:43
  Challenge No.1: How to Track On-hire Days for Each Vessel (Part 3)2:10
  Challenge No.1: How to Track On-hire Days for Each Vessel (Part 4)5:16
Challenge No. 2
  Challenge No. 2: How to Track Daily Hire Rates for Each Vessel (Part 1)3:40
  Challenge No. 2: How to Track Daily Hire Rates for Each Vessel (Part 2)2:04
Challenge No. 3
  Challenge No. 3: How to Calculate Net Hire Revenues3:04
Dashboard Reporting Challenges
  Dashboard Reporting Challenges0:32
Challenge No. 4
  Challenge No. 4: How to Convert Pro-Forma Report to Dashboard Report (Part 1)5:55
  Challenge No. 4: How to Convert Pro-Forma Report to Dashboard Report (Part 2)2:53
Challenge No. 5
  Challenge No. 5: How to Build Slice-and-dice Function of a Dashboard (Part 1)4:34
  Challenge No. 5: How to Build Slice-and-dice Function of a Dashboard (Part 2)5:07
  Challenge No. 5: How to Build Slice-and-dice Function of a Dashboard (Part 3)1:18
Challenge No. 6
  Challenge No. 6: How to Display Budgets, Actuals and Off-hires all in one chart?3:28
conclusion
  Takeaways1:34
  Next Course & Q&A0:27
CONTINUOUS PLAY
  Excel Magic 8: Transforming a Pro-forma into a Dashboard58:18
SUPPORTING MATERIAL
  Slides: Excel Magic 8: Transforming a Pro-forma into a DashboardPDF
  Excel Magic 8: Transforming a Pro-forma into a Dashboard Glossary/ IndexPDF
  Workbook: Excel Magic 8: Transforming a Pro-forma into a DashboardXLXS
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam