Excellency AcademyOpen the app

Finance

63 lessons across 14 tiers. Start at Tier 0 with the basics, then work up.

Tier 0

  1. Navigate a General LedgerGrid navigation, Ctrl+End/Home
  2. Trial Balance CheckSUM
  3. Currency Conversion BasicsIF, basic arithmetic
  4. Relative and Absolute ReferencesAbsolute References ($)
  5. File Formats for Financial RecordsFile Formats
  6. Recovery: Undo, Autosave and Version HistoryUndo, AutoRecover, Version History
  7. Printing a Financial ReportPrint Area, Print Titles, Scaling
  8. Bold, Italic and Underline for a Simple ReportBold, Italic, Underline
  9. Alignment and Wrap TextHorizontal Alignment, Wrap Text
  10. Font Colour and Background FillFont Colour, Background Fill
  11. Insert and Delete Rows and ColumnsInsert Row, Delete Column
  12. Copy, Cut and Paste SpecialCopy, Cut, Paste Special

Tier 1

  1. Budget vs Actual VarianceSUMIF
  2. Accounts Receivable AgingSUMIFS
  3. Cash Flow Running BalanceCumulative SUM
  4. Sorting Safely Without Breaking AlignmentSort
  5. AutoFilter for Overdue PayablesAutoFilter, COUNTIFS/SUMIFS

Tier 2

  1. Track Expenses as a TableFormat as Table
  2. Flag Overspending with Conditional FormattingConditional Formatting - Formula Rule
  3. Rounding and Number Formats for CurrencyNumber Format vs ROUND
  4. Dependent Dropdowns: Account and Sub-accountDependent Data Validation
  5. IF/IFS for Expense Approval TiersIF, IFS

Tier 3

  1. Clean Up Imported Vendor NamesTRIM, PROPER/UPPER
  2. Split a Combined Invoice ReferenceFIND, LEFT, MID, RIGHT
  3. Extract the Domain from a Vendor EmailFIND, RIGHT, MID, LEN
  4. Remove Duplicate Vendor Payment RequestsTRIM, UPPER, SUMPRODUCT, COUNTIF

Tier 4

  1. Look Up an Account BalanceVLOOKUP, XLOOKUP
  2. Two-Way Lookup Across Region and QuarterINDEX/MATCH, XLOOKUP
  3. Discount Tier from a Purchase Volume TableVLOOKUP with TRUE, XLOOKUP match_mode -1
  4. Flag a Cost Centre Code That Isn't in the Chart of AccountsIFNA, VLOOKUP, XLOOKUP

Tier 5

  1. Monthly Loan RepaymentPMT
  2. Future Value of a Savings PlanFV
  3. Amortisation Split for a Property LoanIPMT, PPMT
  4. Break-Even Point for a New Service LineBreak-even analysis, ROUNDUP

Tier 6

  1. Average Transaction Size by CategoryAVERAGEIF
  2. Commission Tier by Sales QuartileQUARTILE.INC
  3. Rank Sales Reps by Commission EarnedRANK.EQ
  4. Forecast Next Quarter's RevenueFORECAST.LINEAR

Tier 7

  1. Days Sales OutstandingDate subtraction, IF
  2. Month-End Close DateEOMONTH
  3. Invoice Payment Deadline via WORKDAYWORKDAY
  4. Loan Maturity Date via EDATEEDATE

Tier 8

  1. Straight-Line Depreciation ScheduleSLN, running totals
  2. Tiered Interest Rate LookupIFS
  3. Running Cash PositionSCAN, LAMBDA, INDEX
  4. Round Petty Cash to the Nearest R5MROUND

Tier 9

  1. Reusable Annual Budget TemplateFill across, relative references
  2. ISO Week Payroll CutoffISOWEEKNUM
  3. Average Invoice Value, Excluding the Total RowAVERAGE
  4. Trimmed Mean Claim Settlement TimeTRIMMEAN

Tier 10

  1. Bold a Financial Statement HeaderBold
  2. Red Font for Negative BalancesFont Colour
  3. Wrap Text and Vertical Alignment for a Statement NoteWrap Text, Vertical Alignment
  4. Underline for a Subtotal BreakUnderline

Tier 11

  1. Protect a Finalised Financial StatementProtect Sheet
  2. Protect a Shared Ledger, but Allow SortingProtect Sheet - Allow Sort
  3. Protect an Invoice Register, but Allow FilteringProtect Sheet - Allow Filter
  4. Protect an Expense Claim Template, but Allow New RowsProtect Sheet - Allow Insert Row

Tier 12

  1. Block Duplicate Transaction IDsData Validation - Custom Formula
  2. Cap a Purchase Order Against Remaining BudgetData Validation - Custom Formula
  3. Restrict a Payment Run Date to Weekdays OnlyData Validation - Custom Formula (WEEKDAY)
  4. Require Each Ledger Balance to Exceed the One Above ItData Validation - Custom Formula (Row Comparison)

Tier 13

  1. Capstone: Build a Complete Departmental Budget ReportSUMIF, Cell Formatting, Data Validation, Protect Sheet

Start this track