Financial Analyst

 

Category 1: Financial Modeling & Valuation

 

1. Sanity-Checking a Financial Model

Act as a meticulous Director of Investment Banking. I have built a 3-statement financial model for `[Company Name]`, a `[SaaS/Manufacturing/Retail]` company. Before I present it, I need you to perform a rigorous sanity check. I will provide key assumptions like `[e.g., revenue growth, gross margin, D&A as % of Capex, etc.]`. Your task is to:
1. Identify 5 assumptions that seem most aggressive or prone to error.
2. For each identified assumption, provide a bear-case and bull-case alternative, justifying your rationale with industry-specific logic.
3. List 10 critical stress tests I should run on this model (e.g., ‘Increase churn rate by 200 bps,’ ‘Decrease new customer acquisition by 15%,’ ‘Simulate a 10% price reduction across all product lines’).
4. Suggest 3 specific cross-checks to ensure the balance sheet always balances and cash flow statements articulate correctly with the other statements.

 

2. Generating Valuation Commentary

Assume the role of a senior equity research analyst. I have completed a Discounted Cash Flow (DCF) valuation for `[Company Name]` which resulted in an intrinsic value of `$X` per share, and a comparable company analysis (CCA) which implies a value of `$Y` per share. The current stock price is `$Z`.
Your task is to write a concise, 250-word ‘Valuation’ section for an equity research report. The commentary must:
• Synthesize the outputs of both the DCF and CCA methodologies.
• Address the likely reasons for the premium or discount between the intrinsic value and the current market price.
• Incorporate a discussion of key qualitative factors (e.g., `[management strength, competitive moat, regulatory risk]`) that are not fully captured in the quantitative analysis.
• Conclude with a clear investment thesis based on the valuation gap.

 

3. M&A Accretion/Dilution Analysis Narrative

I am modeling the acquisition of TargetCo by AcquirerCo. The initial analysis shows the deal is `[e.g., 5% accretive in Year 1 and 8% accretive in Year 2]` to AcquirerCo’s EPS. Act as a Corporate Development manager preparing a summary for the CFO.
Your task is to:
1. Generate a list of the 5 most critical assumptions driving the accretion/dilution result (e.g., `[synergy realization, financing costs, revenue assumptions for TargetCo, purchase price allocation]`).
2. For each assumption, describe the potential risk and its impact if the assumption proves false.
3. Draft a 3-bullet point executive summary explaining *why* the deal is accretive, focusing on the core financial drivers (e.g., ‘The deal is accretive primarily due to significant cost synergies and AcquirerCo’s ability to finance the deal with low-cost debt, which outweighs the goodwill amortization…’).

 

4. Building a Dynamic Scenario Toggle

I am building a financial model in Excel and want to create a dynamic scenario analysis feature. My key input drivers are `[Revenue Growth Rate, Gross Margin, Customer Acquisition Cost, and R&D % of Sales]`.
Provide the VBA code or Excel formulas (using `CHOOSE` or `INDEX/MATCH`) to create a single drop-down cell (in cell `B1`, for instance) with three options: ‘Base Case,’ ‘Upside Case,’ and ‘Downside Case.’ The code/formulas should automatically update all key input driver cells throughout the model when the scenario is selected from the drop-down.

 

5. Selecting the Right Valuation Multiple

I am performing a comparable company analysis for a late-stage, high-growth, currently unprofitable tech company in the `[e.g., cybersecurity]` sector. Traditional P/E multiples are not applicable.
Act as a seasoned venture capital analyst. Your task is to:
1. Recommend the three most appropriate valuation multiples to use (e.g., EV/Revenue, EV/Gross Profit, Price/Sales).
2. For each multiple, provide a strong justification for why it is suitable for this specific type of company and industry.
3. For each multiple, list 2-3 potential drawbacks or distortions I need to be aware of (e.g., ‘EV/Revenue can be misleading if there are vast differences in gross margin between comps’).

 

 

Category 2: FP&A: Forecasting & Variance Analysis

 

6. Deconstructing a P&L Variance

Assume the persona of a Senior FP&A Manager preparing for a monthly business review. Our `[Specific P&L Line, e.g., ‘Sales & Marketing Expense’]` came in `[15%]` over budget.
Your task is to generate a structured inquiry plan.
1. Hypothesize four distinct and plausible root causes for this variance (e.g., `[‘Higher-than-expected spending on performance marketing channels,’ ‘Unbudgeted headcount/T&E,’ ‘Timing pull-forward of a campaign from next quarter,’ ‘FX impact on international marketing spend’]`).
2. For each hypothesis, formulate one sharp, data-driven question to ask the department head.
3. Suggest a specific data report or analysis that would be needed to validate each hypothesis (e.g., ‘Request a detailed spend report by marketing channel vs. budget,’ ‘Analyze the payroll register for new hires not in the budget model’).

 

7. Improving Forecast Accuracy

My team’s revenue forecast has had an average variance of `[+/- 12%]` to actuals over the past four quarters. I need to improve its accuracy. Our business model is `[e.g., subscription-based with enterprise clients]`.
Act as a strategic finance expert. Propose a new forecasting methodology. Your response should:
1. Critique the likely flaws of a simple, straight-line or purely historical growth-based forecast.
2. Outline a more robust, driver-based forecasting model. Suggest 5-7 key operational drivers to use (e.g., `[Sales pipeline value, historical close rates, average deal size, churn rate, upsell/cross-sell rates]`).
3. Provide a step-by-step process for building this new forecast, from data collection to final output.
4. Suggest one statistical method (e.g., `rolling average`, `exponential smoothing`) to apply to a key driver to improve its predictive power.

 

8. Building a Rolling Forecast Template

Our company is shifting from an annual budget to a 12-month rolling forecast. Act as a finance systems consultant. Design a best-practice template structure for this new process.
Describe the optimal layout in a markdown table. The columns should include:
• Previous 3 months (Actuals)
• Current month (Forecast)
• Next 11 months (Forecast)
• Full Quarter Views (Calculated)
• Full Year View (Calculated)
Explain the logic behind this structure and provide 3 key process recommendations for a successful implementation (e.g., ‘Establish clear submission deadlines,’ ‘Lock down actuals before the new forecast cycle begins,’ ‘Focus on key drivers, not minutiae’).

 

9. Analyzing Customer Lifetime Value (CLV) and Customer Acquisition Cost (CAC)

I am analyzing the unit economics of our B2B SaaS company. I have the following data points: `[Average monthly recurring revenue per customer (ARPU), Customer lifetime in months, Gross Margin %, and Customer Acquisition Cost (CAC)]`.
Your task is to:
1. Provide the formula and calculate the Customer Lifetime Value (CLV).
2. Calculate the CLV:CAC ratio.
3. Interpret the result. Act as a strategic analyst and explain what this ratio signifies about our company’s health and scalability.
4. Suggest three actionable strategies to improve the CLV:CAC ratio, with one strategy for each component: increasing CLV, decreasing CAC, and a third, more creative strategy (e.g., ‘Implement a referral program to lower CAC’).

 

10. Headcount and Payroll Forecasting

I am responsible for forecasting the company’s largest expense: payroll. The business plans to hire `[e.g., 25 new employees]` over the next six months across different departments and seniority levels.
Act as an FP&A specialist. Create a detailed checklist of all the data points and assumptions I need to gather to build an accurate, bottom-up payroll forecast. The list should include at least 15 items, covering salaries, taxes, benefits, bonuses, hiring dates, and other related costs (e.g., `Average salary by role/level`, `Bonus accrual rates and payout timing`, `State-specific payroll tax rates (SUI/SUTA)`, `401k matching percentage`, `New hire recruitment fees`).

 

 

Category 3: Strategic Insight & Narrative Building

 

11. Drafting an MD&A Narrative

Act as a Director of Finance. I need to draft the Management’s Discussion and Analysis (MD&A) section for our quarterly report. I will provide you with the key financial results:
• Revenue: `[e.g., $15M, up 12% YoY, down 3% QoQ]`
• Gross Margin: `[e.g., 65%, up 200 bps YoY]`
• Operating Income: `[e.g., $2M, down 5% YoY]`
I will also provide key business context: `[e.g., ‘We launched a new product line, but faced macroeconomic headwinds in Europe.’]`
Your task is to write a 300-word narrative that explains these results. The narrative must connect the financial numbers to the business context, providing a clear and defensible story for investors. Use professional, SEC-compliant language and structure it to discuss Results of Operations, starting with Revenue and flowing down the P&L.

 

12. Identifying Key Performance Indicators (KPIs)

Our company is in the `[e.g., direct-to-consumer e-commerce]` industry. The executive team wants to refine our key performance indicators beyond just revenue and profit.
Act as a strategic business consultant. Your task is to:
1. Propose 3 ‘North Star’ KPIs that align with long-term value creation for this industry. For each, explain *why* it is a North Star.
2. Propose 5 additional ‘Tier 2’ operational KPIs that directly influence the North Star metrics.
3. For one of the Tier 2 KPIs (e.g., `Average Order Value`), suggest a formula for its calculation and list three specific business initiatives that could be implemented to improve it.

 

13. Competitor Financial Analysis

I have the latest 10-K filings for our two main competitors: `[Competitor A]` and `[Competitor B]`. I need to perform a rapid comparative financial analysis for our leadership team.
Your task is to create a template for this analysis. Structure the output as a memo with the following sections:
1. Executive Summary: A 3-bullet point summary of the most critical competitive insight.
2. Key Financial Ratios: A markdown table comparing our company, Competitor A, and Competitor B on 5 key ratios (e.g., `Gross Margin %`, `Operating Margin %`, `ROIC`, `Debt-to-Equity`, `Inventory Turnover`).
3. Strategic Commentary: For the most divergent ratio, provide 2-3 hypotheses explaining *why* their performance is different from ours (e.g., ‘Competitor A’s higher gross margin may be due to superior supply chain management, a different product mix, or offshore manufacturing’).
4. Red Flags: Identify one potential weakness or risk for each competitor based on their financials.

 

14. Preparing for Investor Questions

We are a publicly traded company preparing for our quarterly earnings call. Our results were mixed: `[e.g., We beat on revenue but missed on EPS due to higher investment spend]`.
Act as an experienced Investor Relations consultant. Generate a list of the 10 toughest, most probable questions that a skeptical Wall Street analyst might ask our CEO/CFO during the Q&A session. For the top 3 most difficult questions, draft a concise, strategic, and transparent sample answer.

 

15. Translating Financials for a Non-Financial Audience

I need to present the Q3 financial results to the Head of Sales. This person is brilliant at sales but not financially savvy. The key message is that `[e.g., revenue grew, but profitability declined because of higher commission payouts on lower-margin deals]`.
Your task is to draft a 150-word talking points script for me. The script must:
• Avoid jargon (e.g., replace ‘gross margin contraction’ with ‘we made less profit on each dollar of sales’).
• Use an analogy to explain the core issue.
• Clearly connect the sales team’s actions (selling certain deals) to the financial outcome.
• End with a collaborative, forward-looking question to engage the Sales leader in solving the problem.

 

 

Category 4: Data Analysis & Automation

 

16. Python Script for Financial Data API

Act as a Python developer specializing in financial data. I need a Python script using the `yfinance` library to download historical stock price data for `[AAPL, MSFT, GOOGL]`.
The script must:
1. Download daily data for the last 5 years.
2. Calculate the daily percentage change (return) for each stock.
3. Calculate and print the annualized volatility for each stock.
4. Plot the cumulative returns of all three stocks on a single, clearly labeled Matplotlib chart.
Ensure the code is well-commented so I can easily understand and adapt it.

 

17. SQL Query for Cohort Analysis

I have a database with two tables: `Orders` (`order_id`, `customer_id`, `order_date`, `order_value`) and `Customers` (`customer_id`, `signup_date`). I need to perform a customer retention cohort analysis.
Provide me with a SQL query that:
1. Determines the acquisition month for each customer.
2. For each acquisition cohort (e.g., ‘Jan 2024 Cohort’), calculates the percentage of customers who made another purchase in Month 1, Month 2, Month 3, etc., after their acquisition month.
The output should be a table with `acquisition_month` as the first column, followed by columns representing the percentage of retained customers for each subsequent month (`M+1`, `M+2`, etc.).

 

18. Structuring a Capital Expenditure Request

A department has requested `$250,000` for `[a new software system]`. I need to build a standardized financial justification template for all CapEx requests.
Act as a corporate finance manager. Design the template. It should be a one-page document outline with the following sections, and for each section, list 2-3 guiding questions the requestor must answer:
• Executive Summary
• Problem Statement & Business Case
• Quantitative Analysis (e.g., ‘What is the projected ROI, NPV, and Payback Period? Show your calculations and assumptions.’)
• Qualitative Benefits (e.g., ‘What non-financial benefits will this provide, such as improved employee morale or reduced compliance risk?’)
• Risk Assessment
• Alternatives Considered

 

19. Automating Variance Report Commentary

I want to automate the first draft of my monthly variance commentary. My data output from my financial system looks like this: `{‘Account’: ‘Travel Expense’, ‘Budget’: 10000, ‘Actual’: 15000, ‘Variance’: -5000}`.
Write a Python function that takes a dictionary with this structure as input. The function should use f-strings and conditional logic to generate a human-readable sentence. For example:
• If variance is negative and significant (>10%), it should output: ‘Travel Expense was over budget by $5,000 (a 50% unfavorable variance), requiring further investigation.’
• If variance is positive and significant (>10%), it should output: ‘Travel Expense was under budget by $X (a Y% favorable variance), reflecting successful cost control.’
• If variance is insignificant, it should output: ‘Travel Expense was effectively on budget.’

 

20. Cash Flow Forecasting Logic

I am building a direct cash flow forecast model and need to project cash collections from sales. Our company has the following Accounts Receivable aging profile: `[40% of sales are collected in the month of sale, 50% in the following month, and 8% in the month after that, with 2% bad debt]`.
Provide the Excel formulas or logic needed to accurately forecast cash receipts for a given month (`Month N`) based on the sales forecasts for `Month N`, `Month N-1`, and `Month N-2`.

 

 

Category 5: Career & Skill Development

 

21. Structuring a Financial Analyst’s Learning Plan

Act as a mentor and Finance Director. I am a Financial Analyst with 2 years of experience, strong in Excel but weaker in `[data visualization and SQL]`. I want to be promoted to Senior Financial Analyst in the next 18 months.
Create a detailed 3-quarter professional development plan for me. For each quarter, specify:
1. One ‘Hard Skill’ to focus on (e.g., ‘Q1: SQL Fundamentals’).
2. One ‘Soft Skill’ to focus on (e.g., ‘Q2: Storytelling with Data’).
3. Specific, actionable learning resources (e.g., ‘Complete the “SQL for Data Science” course on Coursera’).
4. A practical, on-the-job project I can propose to my manager to apply the new skill (e.g., ‘Volunteer to rebuild the weekly sales dashboard in Tableau instead of Excel’).

 

22. Elevating Executive Presence

I am presenting my financial analysis to the C-suite for the first time next week. I feel confident in my numbers but nervous about my executive presence.
Act as an executive communication coach. Provide me with 5 actionable tips to improve my presence during the presentation. The tips should be specific to a financial presentation context (e.g., ‘Lead with the “so what,” not the “what.” Start with the key insight, then show the data that supports it,’ or ‘Prepare a “back-pocket” slide with deep-dive data to handle tough, detailed questions confidently’).

 

23. Transitioning from FP&A to Corporate Development

I am a Senior FP&A Analyst looking to transition into a Corporate Development (M&A) role internally.
Act as a career advisor who has made this exact move.
1. Identify the top 3 skill gaps I likely have coming from FP&A (e.g., `[Valuation modeling, due diligence processes, negotiation tactics]`).
2. Suggest 3 specific ways I can start demonstrating M&A-relevant skills in my current FP&A role (e.g., ‘Volunteer to model the financial impact of a potential partnership,’ ‘Analyze competitor 10-Ks to identify potential acquisition targets’).
3. Draft a short, compelling email I could send to the head of Corporate Development to request an informational interview.

 

24. Justifying a Technology Investment for the Finance Team

My team is wasting dozens of hours per month on manual data consolidation in Excel. I want to propose that we invest in a modern FP&A software tool like `[Anaplan, Pigment, or Vena]`.
Act as a finance manager building a business case for your CFO. Structure a one-page proposal that includes:
• Problem Statement: Quantify the current inefficiency (e.g., ‘`X` analysts spend `Y` hours per month on manual tasks, costing the company `$Z` in lost productivity’).
• Proposed Solution: Briefly describe the software category and its core benefits.
• Financial Justification: Create a simple ROI calculation, estimating software cost vs. benefits (time savings, reduced errors, faster decision-making).
• Implementation Plan: A high-level, 3-step plan.
• Call to Action: A clear next step.

 

25. Formulating a Hypothesis for Ad-Hoc Analysis

My boss, the CFO, just asked me an open-ended question: ‘Why is our freight expense so volatile month-to-month? Dig into it and let me know what you find.’ This is a classic ‘blank sheet of paper’ analysis.
Act as a seasoned analyst. Before I dive into the data, provide a structured thinking process.
1. Formulate three distinct, testable hypotheses for the volatility (e.g., Hypothesis 1: ‘The volatility is driven by the mix of shipping methods (air vs. ground) based on customer order urgency.’ Hypothesis 2: ‘It’s caused by fluctuating fuel surcharges from our primary carriers.’ Hypothesis 3: ‘It’s correlated with the geographic mix of our customers each month.’).
2. For each hypothesis, specify the exact data I need to request to prove or disprove it.