Morgan Stanley Financial Analysis
Model 3 years of Morgan Stanley financials in Excel and visualize in Tableau.
Introduction
30 Second Summary
The fastest way to stand out in a finance interview is to walk in having already done the job. When you can say "I analyzed your firm's actual performance and here's what I found," you prove initiative, genuine interest, and the technical skills to back it up.
In this project, you will analyze Morgan Stanley's real financial data from public SEC filings, build a structured Excel model with banking-specific ratios, and create an interactive Tableau dashboard you can publish and share with recruiters.
What You'll Build
A two-part financial analysis portfolio project combining an Excel workbook (3-year income statement data, 8+ ratio calculations) with an interactive Tableau Public dashboard visualizing Morgan Stanley's revenue trends, segment mix, and key performance metrics.
By the end of this project, you'll have:
- A structured Excel workbook analyzing Morgan Stanley's FY2022-FY2024 financial performance with calculated banking-specific ratios including ROTCE, efficiency ratio, and pre-tax margin.
- An interactive Tableau Public dashboard with four views: revenue trend line, segment breakdown, KPI scorecard, and segment composition chart.
- A published portfolio piece you can link on your resume, demonstrating the exact Excel and Tableau skills Morgan Stanley's Finance Analyst Program lists in their job requirements.
- Secret Mission: Add a peer comparison benchmark by pulling Goldman Sachs or JPMorgan data for a side-by-side performance analysis.
Are there any prerequisites?
You should have basic Excel skills (formulas, cell references) and familiarity with financial statements like income statements and balance sheets. Prior experience building a DCF model is helpful but not required.
Before We Start
You are about to build a financial analysis of Morgan Stanley using the same data their own Finance division works with.
Before diving into the numbers, you need three things ready: a Tableau Public account for publishing your dashboard, the SEC earnings releases that contain Morgan Stanley's financial data, and an Excel workbook to organize it all.
In this step, get ready to:
- Verify your Tableau Public account is active and ready for web authoring.
- Open Morgan Stanley's earnings releases from the SEC website.
- Create your Excel workbook for the analysis.
Verify your Tableau Public account
Tableau Public is a free platform for publishing interactive data visualizations. You will use it later to build and share your Morgan Stanley dashboard.
- Open your web browser and navigate to public.tableau.com.
- Click Sign In in the top-right corner.
✔️ I can sign in and see Create a Viz
You are all set. Confirm you can see the Create a Viz option on your profile page, then move on to the next substep.
ⓧ I don't have a Tableau Public account yet
- On the Tableau Public homepage, click Sign Up.
- Fill in your name, email address, and a password.
- Check your email inbox for a verification message from Tableau.
- Click the activation link in that email to verify your account.
- Sign in at public.tableau.com with your new credentials.
- Confirm you can see the Create a Viz option on your profile page.
What is Create a Viz?
Create a Viz is Tableau Public's web authoring feature. It lets you upload data and build visualizations directly in your browser without installing any desktop software.
Open Morgan Stanley's earnings releases on the SEC website
All the financial data for this project comes from Morgan Stanley's official earnings releases filed with the SEC. You need two documents: the Q4 2024 release (which contains FY2024 and FY2023 data) and the Q4 2022 release (which contains FY2022 data).
- Open a new browser tab and navigate to the Q4 2024 earnings release:
https://www.sec.gov/Archives/edgar/data/895421/000089542125000079/a4q24msearningsrelease.htmWhat is this document?
This is Morgan Stanley's Fourth Quarter 2024 earnings release, filed with the SEC on January 16, 2025. It contains the full-year consolidated income statement for FY2024 and FY2023, plus segment-level results for Institutional Securities, Wealth Management, and Investment Management.
- Open a second browser tab and navigate to the Q4 2022 earnings release:
https://www.sec.gov/Archives/edgar/data/895421/000115752323000051/a53203138ex99_1.htmWhy do I need a separate document for FY2022?
The Q4 2024 release only compares FY2024 to FY2023. To get three full years of data (FY2022 through FY2024), you need the Q4 2022 release which contains that year's figures.
- Keep both tabs open. You will reference these documents throughout the project when entering data into your Excel workbook.
Create your Excel workbook
You need a single workbook to house all your financial data, calculated ratios, and Tableau-ready exports.
- Open Microsoft Excel.
- Create a new blank workbook.
- Save the workbook immediately. Click File then Save As.
- Choose a location you can easily find (such as your Desktop).
- Name the file Morgan_Stanley_Analysis.xlsx and click Save.
Why save an empty workbook now?
Saving early means you have a named file ready to go. In the next step, you will add multiple sheets to this workbook for income statement data, segment revenues, and key metrics.
Can't find your saved file?
Check the location you selected in the Save As dialog. If you saved to your Desktop, the file Morgan_Stanley_Analysis.xlsx should appear there. On macOS, look in Finder. On Windows, look in File Explorer.
Help me find my saved Excel file.
Your tools are ready. Next up, you will start entering Morgan Stanley's income statement data into your workbook and building the financial model.
Set Up and Collect Data
Every financial analysis starts with reliable source data. In investment banking, analysts pull numbers directly from regulatory filings rather than third-party aggregators because SEC filings are the authoritative source.
In this step, you will confirm your tools are ready and open the two SEC earnings releases that contain all the raw financial data for this project.
In this step, get ready to:
- Confirm your Tableau Public account is active and ready for publishing.
- Open Morgan Stanley's FY2024 and FY2022 earnings releases from SEC EDGAR.
- Prepare your Excel workbook for data entry.
Confirm your Tableau Public account is ready
You will use Tableau Public later in this project to build and publish your interactive dashboard. Before diving into data collection, confirm your account is ready to go.
- Switch back to your Tableau Public tab at public.tableau.com where you are already signed in.
- Click your profile icon in the top-right corner.
- Select My Profile.
- Confirm you can see the Create a Viz button on your profile page.
What is Tableau Public?
Tableau Public is a free cloud platform for creating and sharing interactive data visualizations. Any visualization you publish is publicly accessible, which makes it perfect for portfolio pieces.
Morgan Stanley's Finance Analyst Program explicitly lists Tableau as a required tool. Having a published viz on your profile gives you a concrete link to share with recruiters.
Don't see Create a Viz?
Make sure your email address is verified. Tableau Public requires email verification before you can author visualizations. Check your inbox for a confirmation email from Tableau.
help me verify my Tableau Public account so I can create visualizations
Open the FY2024 earnings release from SEC EDGAR
Morgan Stanley files its quarterly earnings results with the SEC. The Q4 2024 earnings release contains full-year consolidated results for both FY2024 and FY2023, giving you two years of data from a single document.
- Open a new browser tab and navigate to the following URL:
- https://www.sec.gov/Archives/edgar/data/895421/000089542125000079/a4q24msearningsrelease.htm
- Wait for the page to fully load. You should see a document titled with Morgan Stanley's financial results for the fourth quarter and full year 2024.
Why SEC filings instead of a finance website?
SEC filings are the primary source. Sites like Yahoo Finance and Bloomberg pull from these same documents. Using the original filing means your numbers are verifiable and match what Morgan Stanley's own finance team reported.
In interviews, being able to say "I pulled data directly from the 8-K filing" signals you understand the reporting chain.
Open the FY2022 earnings release and prepare your workbook
The Q4 2024 release only compares FY2024 to FY2023. For a proper 3-year trend analysis, you need FY2022 data from the earlier filing.
- Open another browser tab and navigate to the Q4 2022 earnings release:
- https://www.sec.gov/Archives/edgar/data/895421/000115752323000051/a53203138ex99_1.htm
- Confirm the document loads and shows Morgan Stanley's fourth quarter and full year 2022 results.
- Return to Microsoft Excel and open your Morgan_Stanley_Analysis.xlsx workbook that you created earlier.
You should now have three browser tabs open (Tableau Public, Q4 2024 release, Q4 2022 release) plus your Excel workbook ready for data entry.
Keep your tabs organized
Consider arranging your browser tabs in order: Tableau Public, Q4 2024 earnings, Q4 2022 earnings. You will switch between the two earnings tabs frequently in the next step as you pull numbers into Excel.
Your workspace is ready. Next up, you will start pulling numbers from these filings into a structured Excel data model with income statement data, segment revenues, and key performance metrics.
Build the Financial Data Model in Excel
In Step 1, you gathered your data sources and created a blank workbook. Now it's time to fill it with real financial data.
Morgan Stanley reports financial results across three business segments and dozens of line items. Your job is to structure this into a clean, analysis-ready format. In this step, you will build three sheets that organize the raw numbers from the SEC filings into a model you can calculate ratios from and eventually visualize in Tableau.
In this step, get ready to:
- Enter consolidated income statement data for FY2022, FY2023, and FY2024.
- Build a segment revenue breakdown with percentage-of-total formulas.
- Record key performance metrics reported in the earnings releases.
Create the Income_Statement sheet
The consolidated income statement is the foundation of your financial model. It shows how much revenue Morgan Stanley generated and how much flowed through to profit.
- In your Morgan_Stanley_Analysis.xlsx workbook, right-click the default sheet tab at the bottom and select Rename.
- Type Income_Statement and press Enter.
- Set up your column headers: type FY2022 in cell B1, FY2023 in cell C1, and FY2024 in cell D1.
- Enter the following row labels in column A, starting at cell A2: Net revenues, Provision for credit losses, Compensation expense, Non-compensation expenses, Pre-tax income, Net income applicable to MS.
- Enter the data values from the table below, starting at cell B2. All figures are in $ millions.
FY2022 FY2023 FY2024
Net revenues 53671 54143 61761
Provision for credit losses 449 532 264
Compensation expense 24765 24558 26178
Non-compensation expenses 14383 17240 17723
Pre-tax income 14074 11813 17596
Net income applicable to MS 11029 9087 13390What does this data show?
Each row is a major line item from Morgan Stanley's consolidated income statement. FY2024 shows record net revenues of $61.8 billion, a significant jump from FY2023.
Pre-tax income tells you how profitable the firm was before taxes. Net income applicable to MS is the bottom line after taxes and after excluding minority interests.
- Save your workbook by pressing Cmd+S (macOS) or Ctrl+S (Windows).
- Verify that cell D2 shows 61761 and cell B7 shows 11029.
Numbers look different from what you expected?
Make sure you are entering values in millions without commas or dollar signs. Excel may reformat your numbers if the cells are set to currency format. Select the data range, right-click, choose Format Cells, and set to Number with 0 decimal places.
Help me fix number formatting in my Income_Statement sheet.
Create the Segment_Revenue sheet
Morgan Stanley operates three business segments. Understanding how revenue splits across them reveals where the firm's growth is coming from.
- Click the + icon next to the Income_Statement tab to create a new sheet.
- Rename the new sheet to Segment_Revenue.
- Set up your column headers: type FY2022 in cell B1, FY2023 in cell C1, and FY2024 in cell D1.
- Enter the segment names in column A starting at A2: Institutional Securities, Wealth Management, Investment Management.
- Enter segment net revenues from the table below (all in $ millions):
FY2022 FY2023 FY2024
Institutional Securities 24444 23060 28080
Wealth Management 24408 26268 28420
Investment Management 5367 5370 5861- In cell A5, type Total. In cell B5, enter the formula =B2+B3+B4 to sum the three segments. Copy this formula across to cells C5 and D5.
Why don't the segment totals equal consolidated net revenues exactly?
Segment totals differ slightly from consolidated net revenues due to intersegment eliminations. This is normal for financial services firms. The variance is small and does not affect your analysis.
- Now add percentage-of-total formulas. In cell A7, type % of Total as a section header.
- In cell A8, type Institutional Securities %. In cell B8, enter the formula:
=B2/B$5- Repeat this pattern for Wealth Management (=B3/B$5 in B9) and Investment Management (=B4/B$5 in B10).
- Copy the formulas in B8:B10 across to columns C and D.
- Select cells B8:D10, right-click, choose Format Cells, and set to Percentage with 1 decimal place.
What does the dollar sign in B$5 do?
The $ before the row number locks that reference when you copy the formula across columns. This way, every percentage formula divides by the total row (row 5) regardless of which column it is in.
- Save your workbook.
- Check that cell D8 shows approximately 45% and cell D9 also shows approximately 46%. These confirm that Institutional Securities and Wealth Management each contribute roughly half of Morgan Stanley's revenue in FY2024.
Seeing 0% or #DIV/0! errors in your percentage rows?
This usually means your Total row formula is not referencing the correct cells. Click on cell B5 and verify it shows =B2+B3+B4 in the formula bar. Also confirm that segment values are entered in rows 2, 3, and 4.
Help me fix my percentage-of-total formulas in Excel.
Create the Key_Metrics sheet
Banking analysts track specific KPIs that reveal how efficiently a firm uses shareholder capital and controls costs. These metrics go beyond basic revenue and profit numbers.
- Create another new sheet by clicking the + icon next to your existing tabs.
- Rename it to Key_Metrics.
- Set up your column headers: type FY2022 in cell B1, FY2023 in cell C1, and FY2024 in cell D1.
- Enter the following metric names in column A starting at A2: EPS diluted, Return on equity, ROTCE, Pre-tax margin, Efficiency ratio, Book value per share, Compensation as % of revenue.
- Enter the performance data from the table below:
FY2022 FY2023 FY2024
EPS diluted 6.15 5.18 7.95
Return on equity 12.6% 9.4% 14.0%
ROTCE 15.3% 12.8% 18.8%
Pre-tax margin 26% 22% 28%
Efficiency ratio 73% 77% 71%
Book value per share 55.17 55.50 58.98
Compensation as % of revenue 46% 45% 42%What do these metrics mean?
EPS (earnings per share) tells you how much profit each share of stock earned. ROE measures how effectively the firm uses shareholder equity to generate profit.
ROTCE (return on tangible common equity) strips out intangible assets like goodwill for a cleaner view of capital efficiency. The efficiency ratio shows what percentage of revenue goes to expenses. A lower efficiency ratio means the firm keeps more of each dollar it earns.
- For percentage values (ROE, ROTCE, pre-tax margin, efficiency ratio, compensation %), enter them as decimals and format as percentages. For example, enter 0.126 for 12.6% ROE.
- Alternatively, type 12.6% directly. Excel will recognize the percent sign and store it as 0.126.
- Save your workbook.
- Verify that cell D4 shows 18.8% (FY2024 ROTCE) and cell B6 shows 73% (FY2022 efficiency ratio).
Percentages showing as tiny decimals?
If you entered 12.6 instead of 0.126 or 12.6%, Excel will interpret it as 1260%. Select the cells, format as Number, then re-enter the values using either the decimal form or by typing the percent sign directly.
Help me fix percentage formatting in my Key_Metrics sheet.
✔️ Awesome, I've got everything!
Great. Double check you have saved your workbook with all three sheets populated.
ⓧ I'd like to double check the full data
Here is a summary of all three sheets and their expected values:
=== Income_Statement Sheet ===
Headers: A1 blank | B1: FY2022 | C1: FY2023 | D1: FY2024
A2: Net revenues | B2: 53671 | C2: 54143 | D2: 61761
A3: Provision for credit losses | B3: 449 | C3: 532 | D3: 264
A4: Compensation expense | B4: 24765 | C4: 24558 | D4: 26178
A5: Non-compensation expenses | B5: 14383 | C5: 17240 | D5: 17723
A6: Pre-tax income | B6: 14074 | C6: 11813 | D6: 17596
A7: Net income applicable to MS | B7: 11029 | C7: 9087 | D7: 13390
=== Segment_Revenue Sheet ===
Headers: A1 blank | B1: FY2022 | C1: FY2023 | D1: FY2024
A2: Institutional Securities | B2: 24444 | C2: 23060 | D2: 28080
A3: Wealth Management | B3: 24408 | C3: 26268 | D3: 28420
A4: Investment Management | B4: 5367 | C4: 5370 | D4: 5861
A5: Total | B5: =B2+B3+B4 | C5: =C2+C3+C4 | D5: =D2+D3+D4
A7: % of Total
A8: Institutional Securities % | B8: =B2/B$5 | (copy across C8, D8)
A9: Wealth Management % | B9: =B3/B$5 | (copy across C9, D9)
A10: Investment Management % | B10: =B4/B$5 | (copy across C10, D10)
Format B8:D10 as Percentage, 1 decimal place.
=== Key_Metrics Sheet ===
Headers: A1 blank | B1: FY2022 | C1: FY2023 | D1: FY2024
A2: EPS diluted | B2: 6.15 | C2: 5.18 | D2: 7.95
A3: Return on equity | B3: 12.6% | C3: 9.4% | D3: 14.0%
A4: ROTCE | B4: 15.3% | C4: 12.8% | D4: 18.8%
A5: Pre-tax margin | B5: 26% | C5: 22% | D5: 28%
A6: Efficiency ratio | B6: 73% | C6: 77% | D6: 71%
A7: Book value per share | B7: 55.17 | C7: 55.50 | D7: 58.98
A8: Compensation as % of revenue | B8: 46% | C8: 45% | D8: 42%Your financial data model is complete. You have three years of income statement data, segment breakdowns with calculated percentages, and key performance metrics all organized in one workbook. Next up, you will build banking-specific ratios that reveal deeper trends in Morgan Stanley's performance.
Calculate Banking-Specific Ratios
You now have three sheets of raw financial data in your workbook. Raw numbers tell part of the story, but derived ratios reveal the trends that matter to analysts and hiring managers.
In this step, you will build a dedicated Ratio_Analysis sheet that calculates year-over-year growth rates, breaks down revenue composition by segment, and concludes with trend commentary that demonstrates genuine analytical thinking.
In this step, get ready to:
- Calculate YoY growth rates for revenue, net income, and EPS using cross-sheet cell references.
- Build a revenue composition analysis with segment shares and conditional formatting.
- Write trend commentary that shows you can interpret the numbers, not just crunch them.
Calculate YoY growth rates
Year-over-year growth tells you how fast a metric is moving. A single formula pattern works for all three metrics: divide the current year by the prior year, then subtract 1.
- In your Morgan_Stanley_Analysis.xlsx workbook, create a new sheet and name it Ratio_Analysis.
- In cell A1, type the header Metric. In B1, type FY2023 YoY. In C1, type FY2024 YoY.
- In cell A2, type Revenue Growth YoY.
- In cell B2, enter this formula:
=Income_Statement!C2/Income_Statement!B2-1- In cell C2, enter this formula:
=Income_Statement!D2/Income_Statement!C2-1- In cell A3, type Net Income Growth YoY.
- In cell B3, enter:
=Income_Statement!C7/Income_Statement!B7-1- In cell C3, enter:
=Income_Statement!D7/Income_Statement!C7-1- In cell A4, type EPS Growth YoY.
- In cell B4, enter:
=Key_Metrics!C2/Key_Metrics!B2-1- In cell C4, enter:
=Key_Metrics!D2/Key_Metrics!C2-1What do these formulas do?
Each formula divides the later year's value by the earlier year's value, then subtracts 1. This gives you the percentage change. For example, Revenue Growth FY2023 divides $54,143M by $53,671M and subtracts 1, returning approximately 0.9%.
The cross-sheet references (Income_Statement!C2) pull live values from your data sheets. If you ever update a number on the Income_Statement sheet, these ratios recalculate automatically.
- Select cells B2:C4.
- Right-click and select Format Cells.
- In the Number tab, select Percentage and set Decimal places to 1.
- Click OK.
Your cells should now display values like 0.9% for FY2023 Revenue Growth and 14.1% for FY2024 Revenue Growth.
Seeing 0% or a wrong number?
Double-check that your Income_Statement sheet has Net revenues in row 2 and Net income applicable to MS in row 7. If your rows are in a different order, adjust the row number in the formula to match.
Also verify that FY2022 data is in column B, FY2023 in column C, and FY2024 in column D on both the Income_Statement and Key_Metrics sheets.
Help me troubleshoot my YoY growth formula in Excel
Build a revenue composition analysis
Knowing how much each business segment contributes to total revenue shows whether the firm is diversified or overly dependent on one line of business. You will also calculate the dollar change year-over-year so you can spot which segments are driving growth.
- In the same Ratio_Analysis sheet, move down to row 7.
- In cell A7, type the section header Revenue Composition Analysis and bold it.
- Set up column headers in row 8: A8 = Segment, B8 = FY2022 Share, C8 = FY2023 Share, D8 = FY2024 Share, E8 = YoY Change FY2023 ($M), F8 = YoY Change FY2024 ($M).
- In cells A9 through A11, enter the segment names: Institutional Securities, Wealth Management, Investment Management.
Now enter the share formulas. These reference your Segment_Revenue sheet where totals are already calculated.
- In cell B9, enter the formula for Institutional Securities' FY2022 share:
=Segment_Revenue!B2/Segment_Revenue!B5- Copy this pattern across C9 and D9 (adjusting column references for FY2023 and FY2024), then down for rows 10 and 11 (adjusting row references for Wealth Management in row 3 and Investment Management in row 4 of your Segment_Revenue sheet).
- Format cells B9:D11 as percentages with 1 decimal place.
Next, calculate the YoY dollar change for each segment.
- In cell E9, enter:
=Segment_Revenue!C2-Segment_Revenue!B2- In cell F9, enter:
=Segment_Revenue!D2-Segment_Revenue!C2- Copy the E9:F9 formulas down to rows 10 and 11, adjusting the row references for each segment.
What should I see?
Institutional Securities should show a negative YoY change for FY2023 (around -$1,384M as revenues declined from $24,444M to $23,060M) and a strong positive change for FY2024 (around +$5,020M). Wealth Management should show positive changes in both years.
Now apply conditional formatting so growth appears green and decline appears red at a glance.
- Select cells E9:F11 (the YoY dollar change values).
- Click the Home tab in the ribbon.
- Click Conditional Formatting, then Highlight Cells Rules, then Greater Than.
- Enter 0 in the value field and select Green Fill with Dark Green Text from the dropdown.
- Click OK.
- With E9:F11 still selected, click Conditional Formatting, then Highlight Cells Rules, then Less Than.
- Enter 0 in the value field and select Light Red Fill with Dark Red Text from the dropdown.
- Click OK.
You should see the Institutional Securities FY2023 change highlighted in red and all FY2024 changes highlighted in green.
Conditional formatting not showing colors?
Make sure the cells contain numeric values, not text. If the formulas returned correctly (you see numbers like -1384 or 5020), the formatting should apply. If you see text-formatted numbers, clear the cells and re-enter the formulas.
Help me fix conditional formatting on my YoY dollar change cells
Add trend commentary
Numbers alone are not enough. Finance analysts at Morgan Stanley are expected to synthesize data into insights. Adding a brief commentary section demonstrates that you can interpret what the ratios mean for the business.
- Move down to row 14 on your Ratio_Analysis sheet.
- In cell A14, type the section header Trend Commentary and bold it.
- Select cells A15:F15, then click Merge & Center in the Home tab. Repeat for rows 16 and 17.
- In the merged cell at row 15, type your first bullet point. For example:
• FY2024 represents record net revenues of $61.8B, a 14% increase over FY2023, driven by strong Institutional Securities performance with IB revenues up 35% YoY.- In the merged cell at row 16, add a second observation. For example:
• Wealth Management and Institutional Securities each contribute ~46% of firm revenues in FY2024, showing balanced diversification across the firm's two largest segments.- In the merged cell at row 17, add a third insight. For example:
• Efficiency ratio improved from 77% (FY2023) to 71% (FY2024), indicating the firm grew revenues faster than expenses, a key indicator of operating leverage.Why write commentary in a spreadsheet?
In investment banking, a model without commentary is incomplete. Analysts are expected to surface the "so what" behind every data point. This section proves you can do more than just plug in numbers.
Feel free to write your own observations based on the ratios you calculated. The examples above are starting points.
- Save your workbook by pressing Ctrl+S (Windows) or Cmd+S (macOS).
Your Ratio_Analysis sheet should now have three clear sections: growth rates at the top, segment composition in the middle, and commentary at the bottom.
✔️ Awesome, I've got everything!
Great. Double check you have saved your workbook before moving on.
ⓧ I'd like to double check the full sheet
Here is the complete layout of your Ratio_Analysis sheet for reference:
Row 1: Metric | FY2023 YoY | FY2024 YoY
Row 2: Revenue Growth YoY | =Income_Statement!C2/Income_Statement!B2-1 | =Income_Statement!D2/Income_Statement!C2-1
Row 3: Net Income Growth YoY | =Income_Statement!C7/Income_Statement!B7-1 | =Income_Statement!D7/Income_Statement!C7-1
Row 4: EPS Growth YoY | =Key_Metrics!C2/Key_Metrics!B2-1 | =Key_Metrics!D2/Key_Metrics!C2-1
(All of B2:C4 formatted as Percentage, 1 decimal place)
Row 7: Revenue Composition Analysis (bold)
Row 8: Segment | FY2022 Share | FY2023 Share | FY2024 Share | YoY Change FY2023 ($M) | YoY Change FY2024 ($M)
Row 9: Institutional Securities | =Segment_Revenue!B2/Segment_Revenue!B5 | (C,D cols same pattern) | =Segment_Revenue!C2-Segment_Revenue!B2 | =Segment_Revenue!D2-Segment_Revenue!C2
Row 10: Wealth Management | (same pattern, row 3) | ... | ... | ...
Row 11: Investment Management | (same pattern, row 4) | ... | ... | ...
(B9:D11 formatted as Percentage, 1 decimal; E9:F11 conditional formatting: >0 green, <0 red)
Row 14: Trend Commentary (bold)
Row 15: [Merged A15:F15] • FY2024 represents record net revenues of $61.8B...
Row 16: [Merged A16:F16] • Wealth Management and Institutional Securities each contribute ~46%...
Row 17: [Merged A17:F17] • Efficiency ratio improved from 77% to 71%...Your ratio analysis is complete. Next up, you will restructure this data into a flat format that Tableau can read, setting the stage for your interactive dashboard.
Prepare Tableau-Ready Data Export
Your ratio analysis sheet now gives you clear insight into Morgan Stanley's performance trends. The next challenge is getting this data into Tableau for visualization.
Tableau expects data in a specific "flat" or "long" format where each row represents a single data point. Your current Excel sheets use a wide format (years as columns), which works great for reading but not for charting. In this step, you will reshape your data into Tableau-friendly tables and export them as a clean, standalone file.
In this step, get ready to:
- Create a flat/long-format data table with all key metrics unpivoted into rows.
- Add a segment-specific table optimized for breakdown charts.
- Export the data as a standalone Excel file for Tableau import.
Create the Tableau_Data sheet with a long-format metrics table
Most charting tools (including Tableau) work best when each row in your data represents exactly one observation. This is called "long format" or "unpivoted" data. Instead of years spread across columns, each year-metric combination gets its own row.
- In Morgan_Stanley_Analysis.xlsx, create a new sheet and name it Tableau_Data.
- In row 1, enter the following column headers:
Year Metric_Category Metric_Name Value- Starting in row 2, enter one row per data point. Populate the table with the following data:
Year Metric_Category Metric_Name Value
2022 Revenue Net_Revenues 53671
2023 Revenue Net_Revenues 54143
2024 Revenue Net_Revenues 61761
2022 Revenue ISG_Revenue 24444
2023 Revenue ISG_Revenue 23060
2024 Revenue ISG_Revenue 28080
2022 Revenue WM_Revenue 24408
2023 Revenue WM_Revenue 26268
2024 Revenue WM_Revenue 28420
2022 Revenue IM_Revenue 5367
2023 Revenue IM_Revenue 5370
2024 Revenue IM_Revenue 5861
2022 Profitability EPS 6.15
2023 Profitability EPS 5.18
2024 Profitability EPS 7.95
2022 Profitability ROE 12.6
2023 Profitability ROE 9.4
2024 Profitability ROE 14.0
2022 Profitability ROTCE 15.3
2023 Profitability ROTCE 12.8
2024 Profitability ROTCE 18.8
2022 Efficiency Efficiency_Ratio 73
2023 Efficiency Efficiency_Ratio 77
2024 Efficiency Efficiency_Ratio 71
2022 Efficiency Pre_Tax_Margin 26
2023 Efficiency Pre_Tax_Margin 22
2024 Efficiency Pre_Tax_Margin 28Why this flat structure?
Each row is one observation: a single metric for a single year. The Metric_Category column lets you filter charts by theme (Revenue vs. Profitability vs. Efficiency). The Metric_Name column lets you pick which specific KPI to plot.
This format means Tableau can create any combination of chart without you restructuring data again. One flat table powers dozens of different visualizations.
- Verify your table has 27 data rows (9 metrics x 3 years) plus the header row.
Values not matching the earnings releases?
Double-check that all revenue figures are in $ millions (no decimals for whole millions). Percentage metrics like ROE and ROTCE are entered as plain numbers (e.g., 14.0 not 0.14). Help me check my Tableau_Data values against the source data.
Add the segment breakdown table
The first table works well for line charts and scorecards. For segment breakdown charts (stacked bars, pie charts), Tableau needs a dedicated table where each row is one segment in one year.
- On the same Tableau_Data sheet, leave one blank row below your first table.
- Enter the following column headers for Table 2:
Year Segment Net_Revenue_MM Pct_of_Total- Populate the segment table with 9 rows (3 segments x 3 years):
Year Segment Net_Revenue_MM Pct_of_Total
2022 Institutional Securities 24444 45.5
2022 Wealth Management 24408 45.5
2022 Investment Management 5367 10.0
2023 Institutional Securities 23060 42.6
2023 Wealth Management 26268 48.5
2023 Investment Management 5370 9.9
2024 Institutional Securities 28080 45.5
2024 Wealth Management 28420 46.0
2024 Investment Management 5861 9.5What does this table do?
The Pct_of_Total column shows each segment's share of total net revenues for that year. These percentages come from your Segment_Revenue sheet calculations.
This table will power your stacked bar chart and pie chart in Tableau. The Segment column becomes the color dimension, and Net_Revenue_MM becomes the size measure.
- Verify your segment table has exactly 9 data rows plus the header.
- Confirm the Pct_of_Total values for each year sum to approximately 100% (slight rounding differences are normal due to intersegment eliminations).
Percentages not summing to 100%?
Segment revenue totals do not perfectly sum to consolidated net revenues due to intersegment eliminations. This is normal for financial services firms. If your totals are within 1-2% of 100%, your data is correct. Help me understand why my segment percentages don't add up perfectly.
Export the data as a standalone file for Tableau
Tableau Public imports entire workbooks. If you connect your main analysis file, Tableau will see all five sheets including your formula-heavy ratio calculations. A clean, standalone data file keeps your Tableau connection simple and avoids import issues.
- First, save your main workbook by pressing Cmd+S (macOS) or Ctrl+S (Windows).
- Right-click the Tableau_Data sheet tab at the bottom of Excel and select Move or Copy.
- In the dialog, set the To book dropdown to (new book).
- Check the Create a copy checkbox.
- Click OK. A new workbook opens containing only your data sheet.
- Save this new workbook: click File > Save As.
- Select Excel Workbook (.xlsx) as the format.
- Name the file MS_Tableau_Data.xlsx and save it to the same folder as your main workbook.
Why a separate file?
Tableau Public supports Excel files (.xlsx) as a data source. By isolating just the flat data tables, you avoid Tableau trying to interpret your formula sheets, conditional formatting, or merged cells from other tabs.
This also mirrors real-world practice. Analysts typically maintain a "source" workbook for analysis and a separate "export" file for visualization tools.
- Verify MS_Tableau_Data.xlsx exists in your folder by checking in Finder (macOS) or File Explorer (Windows).
- Open MS_Tableau_Data.xlsx to confirm it contains one sheet with both your flat tables (the metrics table and the segment table).
Only seeing one table in the exported file?
Make sure both Table 1 and Table 2 are on the same Tableau_Data sheet in your main workbook before copying. If they ended up on separate sheets, move Table 2 onto the same sheet separated by one blank row, then redo the export. Help me fix my Tableau data export.
✔️ Awesome, I've got everything!
Great. Double check you have saved both Morgan_Stanley_Analysis.xlsx (with the new Tableau_Data sheet) and the separate MS_Tableau_Data.xlsx file.
ⓧ I'd like to double check the full data
Here is what your Tableau_Data sheet should contain. Table 1 starts at row 1 and Table 2 starts after a blank row below Table 1.
TABLE 1 - Key Metrics (Long Format)
Year Metric_Category Metric_Name Value
2022 Revenue Net_Revenues 53671
2023 Revenue Net_Revenues 54143
2024 Revenue Net_Revenues 61761
2022 Revenue ISG_Revenue 24444
2023 Revenue ISG_Revenue 23060
2024 Revenue ISG_Revenue 28080
2022 Revenue WM_Revenue 24408
2023 Revenue WM_Revenue 26268
2024 Revenue WM_Revenue 28420
2022 Revenue IM_Revenue 5367
2023 Revenue IM_Revenue 5370
2024 Revenue IM_Revenue 5861
2022 Profitability EPS 6.15
2023 Profitability EPS 5.18
2024 Profitability EPS 7.95
2022 Profitability ROE 12.6
2023 Profitability ROE 9.4
2024 Profitability ROE 14.0
2022 Profitability ROTCE 15.3
2023 Profitability ROTCE 12.8
2024 Profitability ROTCE 18.8
2022 Efficiency Efficiency_Ratio 73
2023 Efficiency Efficiency_Ratio 77
2024 Efficiency Efficiency_Ratio 71
2022 Efficiency Pre_Tax_Margin 26
2023 Efficiency Pre_Tax_Margin 22
2024 Efficiency Pre_Tax_Margin 28
(blank row)
TABLE 2 - Segment Breakdown (Flat Format)
Year Segment Net_Revenue_MM Pct_of_Total
2022 Institutional Securities 24444 45.5
2022 Wealth Management 24408 45.5
2022 Investment Management 5367 10.0
2023 Institutional Securities 23060 42.6
2023 Wealth Management 26268 48.5
2023 Investment Management 5370 9.9
2024 Institutional Securities 28080 45.5
2024 Wealth Management 28420 46.0
2024 Investment Management 5861 9.5Your data is now in the exact shape Tableau needs. Next up, you will connect this file to Tableau Public and build an interactive dashboard with revenue trends, segment breakdowns, and a KPI scorecard.
Build and Publish the Tableau Dashboard
Your flat data tables are exported and ready for visualization. Now it is time to transform those numbers into an interactive dashboard that tells a visual story about Morgan Stanley's financial trajectory.
In this step, you will upload your data into Tableau Public, build four distinct chart types, and publish a live dashboard anyone can view. This is the deliverable you can link on your resume and reference in interviews.
In this step, get ready to:
- Upload your data file and create a revenue trend line chart.
- Build segment breakdown, KPI scorecard, and composition charts.
- Assemble a dashboard with interactivity and publish it live to Tableau Public.
Upload your data and create the revenue trend chart
The first visualization shows Morgan Stanley's net revenue trajectory over three years. A line chart makes growth and dips immediately visible at a glance.
- Return to your Tableau Public profile at public.tableau.com.
- Click Create a Viz.
- On the Connect to Data page, click the Files tab.
- Upload your MS_Tableau_Data.xlsx file.
- On the Data Source page, drag your data tables onto the canvas area.
- Check the data preview at the bottom of the screen to verify your values match what you entered in Excel.
What is the Data Source page?
The Data Source page is where Tableau shows the structure of your uploaded file. You can see column names, data types, and preview rows before building any charts.
Confirming the data here prevents surprises later when you drag fields onto a chart.
- Click the New Worksheet tab at the bottom of the workbook to create your first worksheet.
- Drag Year to the Columns shelf.
- Drag Net_Revenue (or Value filtered to Net Revenues) to the Rows shelf.
- Tableau draws a line chart by default when it detects a time dimension on Columns and a measure on Rows.
- To add dollar value labels, click Label on the Marks card and check Show Mark Labels.
You should see a line chart rising from approximately $53.7B in FY2022 to $61.8B in FY2024, with each data point labeled.
Don't see a line chart?
If Tableau shows a bar chart instead of a line, click the Show Me panel in the top right and select the line chart icon.
Also confirm that Year is recognized as a dimension (blue pill), not a measure (green pill). If Year shows as a continuous green pill, right-click it on the Columns shelf and select Dimension.
Help me troubleshoot my Tableau line chart
Create three additional worksheets
Each additional worksheet highlights a different angle of Morgan Stanley's performance. Together, the four views give a recruiter or interviewer a complete picture in seconds.
Start with the segment breakdown chart.
- Create a new worksheet by clicking the New Worksheet tab at the bottom.
- Drag Year to Columns.
- Drag Net_Revenue_MM to Rows.
- Drag Segment to the Color button on the Marks card.
- In the Show Me panel, select the stacked bar chart type.
You should see a stacked bar for each year, colored by Institutional Securities, Wealth Management, and Investment Management.
Next, create the KPI scorecard.
- Create another new worksheet.
- Build a text table by dragging Metric_Name to Rows and Year to Columns.
- Drag Value to the Text button on the Marks card.
- Filter Metric_Name to show only ROTCE, Efficiency Ratio, EPS, and Pre-tax Margin.
- Add color encoding by dragging Value to the Color button on the Marks card.
- Edit the color legend to use green for improvement and red for decline compared to the prior year.
Why color-encode a scorecard?
A plain numbers table forces the reader to mentally compare values across columns. Green and red coloring makes trends pop instantly.
For example, the efficiency ratio dropping from 77% to 71% is immediately visible as a green improvement (lower is better for efficiency).
Finally, create the segment composition chart for FY2024.
- Create one more new worksheet.
- Drag Segment to the Color button on the Marks card.
- Drag Net_Revenue_MM to the Angle button (for a pie chart) or to Size (for a treemap).
- In Show Me, select either the pie chart or treemap chart type.
- Filter Year to show only 2024 data.
- Add labels showing each segment's name and percentage of total.
Your chart should show Wealth Management and Institutional Securities each contributing approximately 46% of total revenues, with Investment Management at roughly 9%.
Seeing all years instead of just FY2024?
Right-click Year in the Data pane and drag it to the Filters shelf. In the filter dialog, select only 2024 and click OK.
Help me filter my Tableau chart to one year
Assemble and publish the dashboard
A dashboard combines all four worksheets into a single interactive view. This is what you will share with recruiters and reference in interviews.
- Click the New Dashboard icon at the bottom of the workbook (next to the worksheet tabs).
- In the Dashboard pane on the left, you will see your four worksheets listed.
- Drag each worksheet onto the dashboard canvas to arrange them in a 2x2 grid layout.
- Double-click the dashboard title area at the top and type: Morgan Stanley Financial Performance Analysis (FY2022-FY2024).
- Add a subtitle below the title with your name.
Now add a filter action so clicking a year in the revenue trend chart filters the other views.
- In the top menu, click Dashboard, then Actions.
- Click Add Action and select Filter.
- Set the source sheet to your revenue trend line chart.
- Set the target sheets to all other worksheets on the dashboard.
- Under Run action on, select Select.
- Click OK to close both dialogs.
What does a filter action do?
When you click a data point on the revenue trend chart, Tableau automatically filters the other three views to show data for that year only. This interactivity is what separates a static report from a professional dashboard.
Now publish your dashboard to make it live on the web.
- Click Server in the top menu bar.
- Select Tableau Public, then click Save to Tableau Public.
- Sign in with your Tableau Public account credentials if prompted.
- Enter a descriptive title for your workbook (e.g., Morgan Stanley Financial Performance FY2022-2024).
- Click Save.
What happens when you publish?
Tableau Public creates an extract of your data and hosts the interactive dashboard at a public URL. Anyone with the link can view, interact with, and download your visualization.
The URL is what you add to your resume or LinkedIn profile as a portfolio piece.
Publish option not appearing?
Make sure a worksheet or dashboard tab is active, not the Data Source tab. The publish option only appears when you have created at least one visualization.
If you are using the web authoring interface instead of Tableau Desktop Public Edition, look for File then Publish or Save As instead.
Help me publish my Tableau dashboard
Your browser will open to your Tableau Public profile showing the live dashboard. Copy the URL from your browser bar. This is the link you will share on your resume and LinkedIn.
Your interactive financial dashboard is now live. Next up, the Secret Mission challenges you to add a peer comparison that benchmarks Morgan Stanley against a competitor.
Secret mission
Peer Comparison Benchmark
Ready to take your analysis further? Add a competitive benchmark by pulling Goldman Sachs data from their public earnings release and creating a side-by-side performance comparison. This is exactly how analysts at Morgan Stanley contextualize firm performance against key competitors.
Clean Up Your Resources
Clean Up Your Resources
Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally and on Tableau Public (free), so there are no ongoing costs.
Resources you used:
- Morgan_Stanley_Analysis.xlsx (local Excel workbook with 6 sheets).
- MS_Tableau_Data.xlsx (local Tableau data source file).
- Published Tableau Public dashboard (hosted at public.tableau.com).
Keep everything running
No action needed. Choose this if you're still actively building or want to keep your portfolio piece live.
- Your Excel files remain on your Desktop for future reference or iteration.
- Your Tableau Public dashboard stays live and shareable. You can link it on your resume or share the URL with recruiters.
- There are no costs associated with keeping any of these resources.
Pause - I'll come back to this later
Since all resources are either local files or hosted for free on Tableau Public, there is nothing to pause. Your dashboard remains published at no cost and your files stay on your Desktop.
- If you want to hide your dashboard temporarily, sign in to Tableau Public, go to your profile page, and set the viz visibility to hidden. This keeps the viz saved but removes it from public view.
- Your local Excel files require no action. They sit on your Desktop until you move or delete them.
Delete - I don't want to use this again
Remove all project resources and start fresh. Follow these steps to clean up everything.
- Delete your published Tableau Public dashboard. Sign in to public.tableau.com, navigate to your profile page, locate your Morgan Stanley Financial Performance Analysis viz, open its options menu, and select the delete option to remove it permanently.
- Delete your local Excel files. On macOS, run:
rm ~/Desktop/Morgan_Stanley_Analysis.xlsx ~/Desktop/MS_Tableau_Data.xlsx- On Windows, run:
Remove-Item ~\Desktop\Morgan_Stanley_Analysis.xlsx, ~\Desktop\MS_Tableau_Data.xlsx- Confirm deletion by checking your Desktop. The two Excel files should no longer appear.
Nice Work!
Nice Work!
You just built a complete financial analysis portfolio piece using Morgan Stanley's real public filings. You now have a published, interactive dashboard and a structured Excel model that demonstrates the exact skills their Finance Analyst Program requires.
Here's what you learned:
- Built a structured Excel financial model analyzing Morgan Stanley's 3-year performance (FY2022-FY2024) from real SEC filings, calculating banking-specific KPIs like ROTCE, efficiency ratio, and pre-tax margin.
- Prepared a Tableau-ready flat data export by transforming wide-format spreadsheet data into the long-format structure that visualization tools need for flexible charting.
- Published an interactive Tableau Public dashboard with revenue trend lines, segment breakdowns, a KPI scorecard, and filter actions, creating a shareable portfolio piece you can link on your resume.
- Secret Mission: Added a peer comparison benchmark against Goldman Sachs, demonstrating comparative analysis skills across key financial ratios.
Ready to quiz yourself?