Advanced Excel Interview Test Lecture Notes

Overview

• Focus: Advanced Excel interview test for business/finance roles
• Total Questions: 3 (progressively harder)
• Downloadable file available to test yourself

Question 1: Calculate Net Operating Income

• Task: Create a dynamic model based on a holding period (2 to 10 years)
  • Assume no sales proceeds at end of period.
  • Net Operating Income = Property Revenue - Operating Expenses.
• Instructions:
  • Create drop-down list for holding period: Alt + AVV
  • Implement rental income growth rate
  • Conditions: if holding period is exceeded, rental income = 0
  • Use if statement for condition management
  • Drag formulas across required cells for calculations:
    • Total Revenue: Only rental revenue
    • Operating Expenses: Calculate as percentage of revenue
    • Net Operating Income: Total revenue - total operating expenses
• Example:
  • Change holding period should update income statement dynamically.

Question 2: Top 5 and Bottom 5 Brands by Gross Margin %

• Task: Fill light blue area with top/bottom 5 brands
  • Top 5 & Bottom 5: Use Gross Margin %
  • Seemingly all Coca-Cola subsidiaries.
  • Margin percentages provided for each brand
• Instructions:
  • Use large function for top 5:
    • Syntax: =LARGE(array, k) where k is rank (1st, 2nd, etc.)
    • Drag down to get the top 5
  • Use XLOOKUP for brand names:
  • For Bottom 5:
    • Use small function: =SMALL(array, k)
    • Drag down to get the bottom 5
    • Same XLOOKUP method for brand names

Question 3: Monthly Salary Expense Table

• Task: Create prorated monthly salary expense table in light blue area
  • Consider prorated expense amounts given resignation date.
• Instructions:
  • Address proration of salary if resigned mid-month:
  • Implement nested formulas
  • Nested IF statements:
    • Check if individual quit job
    • For resignation month, calculate fraction of days worked
    • Multiply fraction by monthly salary
  • Use IF and DAY/ENDOFMONTH combo for prorated calculations
• Example Formula:
  • Checks if quit month = current month
  • If true, calculate prorated salary; otherwise, full salary
  • Drag formula across different years
• Sanity Check: Ensure salaries seem accurate based on resignation day.

Additional Resources

• Finance Evaluation Course: Investment banking, financial analyst expert guidance
• Financial Statement Analysis and Modelling
  • DCF, comparable companies, and present transactions valuation
  • Real-world data (Apple, Adobe)
• Presentation of investment thesis and stock pitch format

Useful Links:

• Excel shortcuts
• Company valuation on Excel

Call to Action: Like, subscribe, and explore more content