Instructor for this course
more

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: June 19, 2021

1 Review (4 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

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 .
Course Syllabus
INTRODUCTION AND OVERVIEW
  0:24Introduction to Excel Magic 8: Transforming a Pro-forma into a Dashboard
  0:56What is a Pro-forma Report
  1:20Dashboard comes to the rescue!
  1:09Instructor Introduction
  1:21How this course is compared with “other similar courses”?
  0:40What you will get from this course?
Business Case: Vessel Fleet Revenue Stream
  1:46Business Case: Vessel Fleet Revenue Stream
  0:31Pro-Forma Reporting Challenges
Challenge No. 1
  4:30Challenge No.1: How to Track On-hire Days for Each Vessel (Part 1)
  3:43Challenge No.1: How to Track On-hire Days for Each Vessel (Part 2)
  2:10Challenge No.1: How to Track On-hire Days for Each Vessel (Part 3)
  5:16Challenge No.1: How to Track On-hire Days for Each Vessel (Part 4)
Challenge No. 2
  3:40Challenge No. 2: How to Track Daily Hire Rates for Each Vessel (Part 1)
  2:04Challenge No. 2: How to Track Daily Hire Rates for Each Vessel (Part 2)
Challenge No. 3
  3:04Challenge No. 3: How to Calculate Net Hire Revenues
Dashboard Reporting Challenges
  0:32Dashboard Reporting Challenges
Challenge No. 4
  5:55Challenge No. 4: How to Convert Pro-Forma Report to Dashboard Report (Part 1)
  2:53Challenge No. 4: How to Convert Pro-Forma Report to Dashboard Report (Part 2)
Challenge No. 5
  4:34Challenge No. 5: How to Build Slice-and-dice Function of a Dashboard (Part 1)
  5:07Challenge No. 5: How to Build Slice-and-dice Function of a Dashboard (Part 2)
  1:18Challenge No. 5: How to Build Slice-and-dice Function of a Dashboard (Part 3)
Challenge No. 6
  3:28Challenge No. 6: How to Display Budgets, Actuals and Off-hires all in one chart?
conclusion
  1:34Takeaways
  0:27Next Course & Q&A
CONTINUOUS PLAY
  58:18Excel Magic 8: Transforming a Pro-forma into a Dashboard
SUPPORTING MATERIAL
  PDFSlides: Excel Magic 8: Transforming a Pro-forma into a Dashboard
  PDFExcel Magic 8: Transforming a Pro-forma into a Dashboard Glossary/ Index
  XLXSWorkbook: Excel Magic 8: Transforming a Pro-forma into a Dashboard
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM