Talent Program Tracker for Banking

Build a KPI tracker and executive report for a commercial banking analyst program.

Introduction

30 Second Summary

Every week, program managers across banking receive spreadsheets full of raw data about their analyst cohorts. The real skill is not collecting that data. It is turning numbers into a story that tells leadership exactly what to do next.

In this project, you will step into the role of a Commercial Banking Project Analyst, build a program tracker in Excel that calculates KPIs for a 16-person cohort, spot a hidden performance gap, and present your findings in a polished executive PowerPoint report.

What You'll Build

You walk into the Monday leadership meeting with a 4-slide presentation that shows exactly where the program is thriving, where one location is falling short, and what to do about it.

By the end of this project, you'll have:

An automated KPI dashboard in Excel that tells you at a glance whether 16 analysts across New York, Charlotte, and Houston are on track or falling behind.
Color-coded scores and comparison charts that make a hidden performance gap in one location impossible to miss.
A 4-slide executive report in PowerPoint that communicates your findings and data-driven recommendations to senior stakeholders in under 2 minutes.
Secret Mission: Build a risk tracker that flags at-risk analysts and draft a professional escalation email to the Program Director.

Are there any prerequisites?

None. You just need a web browser and a free Microsoft account (you can use any existing email, including Gmail, to create one). This guide walks you through every step of setup.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters for the role you want.

Set Up Excel and PowerPoint for the Web

The JPMorgan Project Analyst role requires "expertise with Microsoft Office suite, particularly Excel and PowerPoint." Before you can build any analytics, you need the same tools the job uses.

In this step, you will set up free versions of Excel for the web and PowerPoint for the web through a Microsoft account. These are the browser-based versions of the same tools you would use on the job.

In this step, get ready to:

Create a free Microsoft account (or sign in with an existing one).
Open Excel for the web and create a new blank workbook.
Confirm formulas work by typing a test calculation.

Sign in to Microsoft 365

Microsoft offers free web versions of Excel and PowerPoint to anyone with a Microsoft account. You can create one using any email address you already have, including a Gmail or Yahoo address.

Open your web browser.
Navigate to Microsoft365.com.

✔️ I already have a Microsoft account

Click Sign in on the Microsoft365.com page.
Enter your email address and password.
You should land on the Microsoft 365 home page showing app icons for Word, Excel, PowerPoint, and more.

ⓧ I need to create a new account

Click Sign up for free on the Microsoft365.com page.
Enter any email address you already use (Gmail, Yahoo, or any other email works).
Create a password when prompted.
Complete the verification step (Microsoft will send a code to your email).
After verification, you should land on the Microsoft 365 home page showing app icons for Word, Excel, PowerPoint, and more.

Why is this free?

Microsoft provides free web versions of Word, Excel, and PowerPoint to anyone with a Microsoft account. You get 5 GB of OneDrive cloud storage included. No credit card or subscription required.

Can't sign in or create an account?

Make sure you are at Microsoft365.com (not office.com or outlook.com). If you see an error about your email already being associated with an account, try clicking Sign in instead and use the Forgot password link to recover access.

Still stuck? Help me sign in to Microsoft 365 for the web.

Create a new blank workbook in Excel for the web

Now that you are signed in, you will create the workbook that becomes your program analytics tracker for the rest of this project.

On the Microsoft 365 home page, click the app launcher icon (a grid of dots) in the upper-left corner of the page.
Click Excel from the list of apps.
Click New blank workbook to open an empty spreadsheet.

You should see a blank Excel spreadsheet open in your browser with a grid of cells ready for input.

Click the workbook name at the top of the page (it will say something like Book or Workbook).
Replace the name by typing CB Analyst Program Tracker and pressing Enter.

The workbook title at the top of the page now reads CB Analyst Program Tracker. This workbook auto-saves to your OneDrive.

Don't see the app launcher or Excel option?

If the app launcher grid is not visible, try refreshing the page. Some browsers block pop-ups or redirects. Make sure you are signed in at Microsoft365.com and look for your profile picture or initials in the top-right corner as confirmation.

Need more help? Help me find and open Excel for the web.

Confirm formulas work with a test calculation

The entire project depends on Excel formulas calculating correctly. A quick test now prevents confusion later.

Before you try this, what do you think will appear in the cell after you press Enter?

Click on cell A1 (the very first cell in the top-left corner of the spreadsheet).
Type =1+1 and press Enter.

Cell A1 now displays 2. Excel evaluated your formula and returned the result. This confirms that Excel for the web is running and formulas work correctly in your browser.

Why test a formula?

In later steps, you will use formulas like COUNTIF and AVERAGEIF to calculate program KPIs automatically. If formulas do not work now, nothing else in the project will produce correct results.

Cell shows the text =1+1 instead of 2?

This happens when the cell is formatted as text instead of a number. Click on cell A1, then look at the Number Format dropdown in the Home ribbon. Change it from Text to General. Then re-type =1+1 and press Enter again.

Still not working? Help me get formulas working in Excel for the web.

Your tools are ready. Next up, you will build the program roster with real analyst data across three banking locations.

Build the Program Roster with Raw Data

Your workbook is open and formulas are confirmed working. Now you need data to analyze.

The JD says "Own/advance program analytics (rosters, participation, outcomes, survey data)." Before calculating any KPIs or spotting trends, you need a clean, structured roster to work from. In this step, you'll build a program roster for a 16-person analyst cohort spread across New York, Charlotte, and Houston.

In this step, get ready to:

Create column headers for the program roster.
Enter data for 16 analysts across three office locations.
Format the data as a named Excel Table with filters.

Set up the column headers

Every analytics tracker starts with a clear schema. Your columns will capture who each analyst is, where they're based, how far along they are in the program, which training modules they've completed, and how satisfied they are.

In your CB Analyst Program Tracker workbook, click on cell A1 (it currently shows your test value of 2).
Delete the contents of A1 by pressing the Delete key on your keyboard.
Type the following headers across Row 1, starting in cell A1 and pressing Tab after each entry to move to the next column:

A1: Analyst Name
B1: Location
C1: Start Date
D1: Current Rotation
E1: Rotation Number
F1: Module 1
G1: Module 2
H1: Module 3
I1: Module 4
J1: Survey Score

What do these columns track?

Analyst Name and Location identify who is in the cohort and where they sit.
Current Rotation and Rotation Number track which banking function (Finance, Risk, Treasury, Lending) each analyst is assigned to and how many rotations they've completed.
Modules 1-4 record whether each required training module is Complete or Incomplete.
Survey Score captures analyst satisfaction on a 1-5 scale.

You should now see 10 column headers running from column A to column J in Row 1.

Headers spilling into one cell?

If all text ended up in cell A1 instead of spreading across columns, you likely pressed Enter instead of Tab.

Clear the row and retype, pressing Tab after each header to move right.

help me fix my Excel headers

Enter the New York analyst data

You'll enter your 16 analysts in three batches, one per office location. This lets you verify each group before moving on.

Click cell A2 to start entering data in the row directly below your headers.
Enter the following 5 rows of New York analyst data, pressing Tab between columns and Enter at the end of each row:

Sarah Mitchell    New York    6/17/2024    Finance     3    Complete    Complete    Complete    Complete      5
James Chen       New York    6/17/2024    Risk        3    Complete    Complete    Complete    Incomplete    4
Priya Patel      New York    6/17/2024    Treasury    2    Complete    Complete    Complete    Complete      4
David Kim        New York    6/17/2024    Lending     3    Complete    Complete    Incomplete  Complete      5
Rachel Foster    New York    6/17/2024    Finance     2    Complete    Complete    Complete    Complete      4

What does this data represent?

These are 5 analysts in the New York office. They started in June 2024 and are rotating through banking functions like Finance, Risk, Treasury, and Lending.

Notice that most New York modules are marked Complete and survey scores are 4 or 5. This is a high-performing location.

You should see data filled in from Row 2 through Row 6, with all 10 columns populated for each analyst.

Enter the Charlotte and Houston analyst data

Click cell A7 and enter the following 6 Charlotte analysts:

Michael Torres    Charlotte    6/17/2024    Risk        3    Complete    Complete    Complete    Incomplete    4
Ashley Johnson   Charlotte    6/17/2024    Treasury    2    Complete    Complete    Complete    Incomplete    4
Brandon Lee      Charlotte    6/17/2024    Lending     3    Complete    Complete    Complete    Complete      5
Olivia Brown     Charlotte    6/17/2024    Finance     2    Complete    Complete    Complete    Incomplete    4
Kevin Wright     Charlotte    6/17/2024    Risk        3    Complete    Complete    Complete    Complete      4
Natalie Davis    Charlotte    6/17/2024    Treasury    1    Complete    Incomplete  Complete    Incomplete    4

What pattern do you notice?

Charlotte is solid but not perfect. Most analysts have one incomplete module, and scores sit at 4 with one standout at 5. Natalie Davis is only on Rotation 1, which is behind the rest of the cohort.

Your spreadsheet should now have data from Row 2 through Row 12. Scroll down to confirm all 11 rows of analyst data are visible.

Click cell A13 and enter the final 5 Houston analysts:

Marcus Green      Houston    6/17/2024    Lending     2    Complete    Complete    Complete    Incomplete    3
Jennifer Walsh   Houston    6/17/2024    Finance     1    Complete    Complete    Incomplete  Incomplete    3
Carlos Rivera    Houston    6/17/2024    Risk        2    Complete    Complete    Complete    Incomplete    4
Amanda Scott     Houston    6/17/2024    Treasury    1    Complete    Complete    Incomplete  Incomplete    3
Tyler Hughes     Houston    6/17/2024    Lending     2    Complete    Complete    Complete    Complete      4

Why is Houston different?

Houston analysts have noticeably lower survey scores (3s and 4s compared to mostly 4s and 5s elsewhere). They also have more incomplete modules and two analysts are still on Rotation 1.

This gap is deliberate. Your job as a Program Analyst is to surface it with data, not just feel that something is off.

Scroll through your spreadsheet. You should now see 16 rows of analyst data (Rows 2-17) below your header row.

Missing rows or columns look misaligned?

If you see fewer than 16 rows, check whether you accidentally skipped a row or entered two analysts in the same row. Click into the cells and verify names match the list above.

If data appears in the wrong columns, a missed Tab press is usually the cause. Click the misaligned cell, cut its contents with Ctrl+X, click the correct cell, and paste with Ctrl+V.

help me fix misaligned data in my Excel roster

Format the data as a named Excel Table

Right now your data is just text in cells. Converting it to a formal Excel Table adds filter dropdowns, structured references for formulas, and automatic formatting that keeps everything organized as you add more data.

Click anywhere inside your data (for example, click cell A2).
Click the Insert tab in the ribbon at the top of the page.
Click Table.
A dialog box appears asking you to confirm the data range. Make sure the My table has headers checkbox is checked.
Click OK.

What just happened?

Excel detected the boundaries of your data and wrapped it in a structured Table object. You'll notice alternating row colors and small dropdown arrows on each column header.

Those dropdown arrows let you filter and sort by any column. In the next step, you'll use the structured table to write formulas that automatically reference the correct columns by name.

Before you check your work, predict what you expect to see when you click one of the dropdown arrows on a column header.

Click the dropdown arrow on the Location column header.

You should see a filter menu listing the three unique values: Charlotte, Houston, and New York. This confirms your data is structured correctly and the Table recognized all 16 rows.

Press Escape or click Cancel to close the filter menu without applying a filter.

Don't see dropdown arrows on your headers?

If the dropdown arrows are missing, the Table may not have been created successfully. Click anywhere in your data, go to Insert > Table again, and make sure the range covers all your data from A1 through J17.

If Excel shows an error about overlapping tables, you may have accidentally created a partial table earlier. Press Ctrl+Z to undo and try again.

help me format my data as an Excel Table

Your roster is built and structured. Right now, though, a Program Director glancing at this spreadsheet would see 16 rows of raw data with no clear takeaway. Next up, you'll write formulas that distill these rows into program-level KPIs that answer "how is the cohort doing?" in seconds.

Calculate Program KPIs with Formulas

Your roster is built. Sixteen analysts across three cities, each with their rotation status, training completions, and survey scores neatly organized in a filterable table.

A Program Director won't scroll through 16 rows to figure out how the program is doing. They need single numbers that answer "what percentage completed training?" and "which location needs attention?" You'll use COUNTIF and AVERAGEIF to create those decision-ready metrics now.

In this step, get ready to:

Calculate the overall training completion rate across all analysts.
Reveal per-location satisfaction scores that surface a hidden gap.
Measure rotation progress to see who's on track.

Calculate overall training completion rate

Every analyst has four training modules that are either Complete or Incomplete. The completion rate answers one question: out of all 64 module slots (16 analysts times 4 modules), how many are done?

Click cell A19 (two rows below your table) and type Program KPIs as a section header.
Press Enter twice to skip a row, then click cell A21 and type Overall Completion Rate as the label.
Click cell B21 and enter the following formula:

=COUNTIF(F2:I17,"Complete")/64

What does this formula do?

COUNTIF counts the number of cells within a range that meet a given criteria.

F2:I17 covers all four Module columns for all 16 analysts (64 cells total).
"Complete" is the criteria. Only cells containing the exact word "Complete" get counted.
Dividing by 64 converts the count into a percentage (e.g., 50 out of 64 = 0.78).

Press Enter. You should see a decimal number around 0.78 appear in cell B21.
To display it as a percentage, click cell B21, then click the Percent Style button (% icon) in the Number group on the Home tab.

Cell B21 now displays something like 78%.

Seeing 0 or an error?

Check that your Module columns are F through I. If your table columns are in a different order, adjust the range in the formula to match.
Make sure you typed "Complete" exactly as it appears in your table (capital C, no extra spaces).
help me fix my COUNTIF formula for completion rate

Calculate per-location average survey scores

The overall completion rate tells you the program is generally healthy. But is every location performing equally? AVERAGEIF lets you calculate the average survey score for each city separately.

Click cell A23 and type Avg Survey Score - New York as the label.
Click cell B23 and enter this formula:

=AVERAGEIF(B2:B17,"New York",J2:J17)

Press Enter. You should see a number around 4.3 appear.

How does AVERAGEIF work?

AVERAGEIF returns the average of all cells in a range that meet a given criteria. The syntax is AVERAGEIF(range, criteria, [average_range]).

B2:B17 is the range to check (the Location column).
"New York" is the criteria. Only rows where Location equals "New York" are included.
J2:J17 is the average_range. These are the Survey Score values to average for matching rows.

Now repeat for Charlotte. Click cell A24 and type Avg Survey Score - Charlotte.
Click cell B24 and enter this formula:

=AVERAGEIF(B2:B17,"Charlotte",J2:J17)

Press Enter. You should see a number around 4.1.
Now do Houston. Click cell A25 and type Avg Survey Score - Houston.
Click cell B25 and enter this formula:

=AVERAGEIF(B2:B17,"Houston",J2:J17)

Press Enter. You should see a number around 3.4.

Look at the three scores side by side. New York sits at roughly 4.3, Charlotte at 4.1, and Houston at 3.4. Houston is nearly a full point lower than the other two locations.

All three scores look the same?

Double-check that your original data has Houston analysts with lower survey scores (mostly 3s and some 2s or 4s, versus 4s and 5s for New York and Charlotte).
Confirm the location names in your formula match exactly what you typed in column B. "Houston" with a capital H must match the cells.
help me troubleshoot my AVERAGEIF scores

Calculate rotation progress

The final KPI answers: how many analysts are progressing through their rotations on schedule? An analyst on Rotation 2 or higher by this point in the program is considered on track.

Click cell A27 and type Rotation Progress (On Track) as the label.
Click cell B27 and enter this formula:

=COUNTIF(E2:E17,">=2")/16

What does this formula do?

E2:E17 is the Rotation Number column for all 16 analysts.
">=2" counts anyone with a rotation number of 2 or higher.
Dividing by 16 gives you the percentage of the cohort that is on track.

Press Enter. You should see a decimal around 0.81.
Click cell B27, then click the Percent Style button (% icon) on the Home tab to display it as 81%.

Seeing 100% or a strange number?

Make sure column E contains the Rotation Number (a digit like 1, 2, or 3), not the rotation name text.
If you see 100%, check that some analysts in your data have a Rotation Number of 1, meaning they haven't progressed yet.
help me fix my rotation progress formula

Before you review the full KPI section, take a moment to predict: which single metric do you think would concern the Program Director the most?

Look at your completed KPI section now. The overall completion rate of 78% is solid. Rotation progress at 81% is healthy. But that Houston average survey score of 3.4 stands out sharply against New York's 4.3 and Charlotte's 4.1.

✔️ Awesome, I've got everything!

Great. Confirm your workbook has auto-saved by checking for the word "Saved" in the top bar.

ⓧ I'd like to double check the full KPI section

Your Program KPIs section below the table should have these cells:

A19: Program KPIs (section header).
A21: Overall Completion Rate with B21 showing =COUNTIF(F2:I17,"Complete")/64 formatted as a percentage (around 78%).
A23: Avg Survey Score - New York with B23 showing =AVERAGEIF(B2:B17,"New York",J2:J17) (around 4.3).
A24: Avg Survey Score - Charlotte with B24 showing =AVERAGEIF(B2:B17,"Charlotte",J2:J17) (around 4.1).
A25: Avg Survey Score - Houston with B25 showing =AVERAGEIF(B2:B17,"Houston",J2:J17) (around 3.4).
A27: Rotation Progress (On Track) with B27 showing =COUNTIF(E2:E17,">=2")/16 formatted as a percentage (around 81%).

You've turned raw roster data into three clear KPIs that a Program Director can read in seconds. The numbers tell a story: the program is broadly healthy, but Houston is struggling. Next up, you'll make that story impossible to miss with visual formatting and charts that hit a senior stakeholder the moment they glance at your spreadsheet.

Visualize Trends with Conditional Formatting and Charts

In Step 3, you calculated the KPIs and spotted that Houston's average survey score (3.4) trails New York (4.3) and Charlotte (4.1).

But here is the problem: a senior stakeholder skimming your spreadsheet will never notice those numbers buried in a wall of text. The numbers alone do not communicate urgency. You need visual cues that make the gap impossible to miss, because "Prepare executive-ready reporting materials" means the insight has to jump off the page.

In this step, get ready to:

Apply conditional formatting to color-code survey scores by performance level.
Create a bar chart comparing average survey scores by location.
Create a second bar chart comparing training completion rates by location.

Apply conditional formatting to the Survey Score column

Conditional formatting applies color to cells based on their value. You will create rules that turn high scores green, middling scores yellow, and low scores red so anyone glancing at the column instantly sees where the problems are.

Your Survey Score column contains values between 1 and 5. You will apply three rules, one for each threshold.

Select all the Survey Score values in your table (just the numbers, not the header).
Click the Home tab in the ribbon.
Click Conditional Formatting.
Select Highlight Cells Rules, then Greater Than.
Type 4.4 in the value field.
In the format dropdown, choose Green Fill with Dark Green Text (or the closest green option available).
Click OK.

You should see any scores of 4.5 or 5 turn green immediately.

Why start with the highest scores?

Excel evaluates conditional formatting rules from highest priority to lowest. By creating the green rule first and then adding yellow and red rules on top, the newer rules take precedence for cells that match multiple criteria. Excel handles the layering for you.

With the same cells still selected, click Conditional Formatting again.
Select Highlight Cells Rules, then Less Than.
Type 3.5 in the value field.
Choose Light Red Fill with Dark Red Text (or the closest red/orange option).
Click OK.

Now scores below 3.5 appear in red or orange. The Houston analysts' lower scores should stand out immediately.

With the same cells still selected, click Conditional Formatting one more time.
Select Highlight Cells Rules, then Between.
Type 3.5 in the first value field and 4.4 in the second value field.
Choose Yellow Fill with Dark Yellow Text (or the closest yellow option).
Click OK.

Your Survey Score column is now a traffic light. Scroll through the table and look at the color pattern across locations.

What should I see?

New York and Charlotte analysts should be mostly green (scores of 4.5 and above) with occasional yellow. Houston analysts should show orange/red cells (scores below 3.5) with maybe one or two yellow cells. The color pattern makes the problem location obvious at a glance.

All cells are the same color?

Make sure you selected only the score values, not the entire column including the header. Also check that you typed the correct threshold values in each rule. You can review your rules by clicking Conditional Formatting then Manage Rules to see all active rules and edit them.

Why is my conditional formatting not showing the right colors?

Create a bar chart comparing survey scores by location

The conditional formatting helps you see the problem in the raw data. Now you need a chart that communicates the same story to someone who will never scroll through 16 rows. A bar chart of average survey scores by location puts the gap front and center.

You already calculated per-location averages in your Program KPIs section. You will select those values and create a chart from them.

Scroll down to your Program KPIs section.
Select the cells containing your three location names (New York, Charlotte, Houston) and their corresponding average survey score values. This should be a small 3-row, 2-column selection (labels in one column, scores in the next).
Click the Insert tab in the ribbon.
Click Chart (or Column or Bar Chart if you see that option directly).
Select a Clustered Bar or Clustered Column chart type.
Click OK if prompted.

A bar chart appears on your worksheet. You should see three bars with Houston's bar noticeably shorter than the other two.

Double-click the chart title text and change it to Survey Score by Location.
Click outside the chart to deselect it.

What does this chart show?

The three bars represent New York (~4.3), Charlotte (~4.1), and Houston (~3.4). Houston trails by nearly a full point on a 5-point scale. That gap is now impossible to miss in a quick glance, which is exactly what a senior stakeholder needs.

Chart looks wrong or shows unexpected data?

Make sure you selected exactly the location names and their score values, not extra cells. If the chart axes are swapped (locations on the wrong axis), click the chart, then look for a Switch Row/Column option in the Chart Design tab to fix it.

Help me fix my bar chart in Excel for the web

Create a bar chart comparing completion rates by location

One chart proves the satisfaction gap. A second chart showing completion rates by location confirms the pattern and makes the case undeniable. If Houston trails on both satisfaction and training completion, that is not a coincidence.

You need a small data set showing per-location completion rates. You will add these calculations to your KPIs section, then chart them.

In your Program KPIs section, find an empty area below your existing calculations.
Type the heading Completion Rate by Location in the first column.
In the rows below, type the three location labels: New York, Charlotte, Houston.
In the adjacent column for each location, calculate that location's completion rate using COUNTIF. For example, for New York, count how many of New York's module cells say "Complete" and divide by the total number of module cells for New York analysts (number of NY analysts times 4 modules).

How do I calculate per-location completion rate?

Each analyst has 4 module columns. If New York has 5 analysts, that is 20 total module slots. Count how many of those 20 cells contain "Complete" using COUNTIF, then divide by 20. Multiply by 100 if you want a percentage number rather than a decimal.

You can select only the module cells for each location's analysts (hold Ctrl to select non-adjacent cells) or reference the rows you know belong to each location. Use a formula like =COUNTIF(range,"Complete")/total*100 for each location.

Once you have completion rate values for all three locations, select the location names and their corresponding completion rate values.
Click the Insert tab.
Click Chart and select a Clustered Bar or Clustered Column chart.
Double-click the chart title and change it to Completion Rate by Location.

You now have two charts side by side on your worksheet. Both tell the same story: Houston is trailing.

What should I see?

Houston's completion rate bar should be 15-20% lower than New York and Charlotte. Combined with the survey score chart, the visual evidence is overwhelming. A stakeholder can glance at both charts and immediately understand that Houston needs attention.

Before you move on, take a moment to look at your whole worksheet. What do you think a senior leader would notice first: the raw numbers in your KPI section, or the charts and color-coded column?

The conditional formatting turns Houston analysts' survey scores orange and red while New York and Charlotte glow green. The bar charts show Houston trailing by 15-20% on completion and nearly a full point on satisfaction. The performance gap is now visually undeniable.

Chart not showing the right comparison?

If both charts look the same or show unexpected values, check that you selected different data for each chart. Chart 1 uses per-location average survey scores. Chart 2 uses per-location completion rates. You can click on a chart and look at the highlighted data range to confirm what it is pulling from.

Help me fix my completion rate chart in Excel

Your data now speaks visually. Next up, you will turn these insights into a 4-slide executive PowerPoint presentation that a Program Director can absorb in under 2 minutes.

Create the Executive PowerPoint Report

Your Excel tracker now tells a clear visual story. The conditional formatting and bar charts make Houston's underperformance impossible to miss at a glance.

The Program Director and senior stakeholders will never open your spreadsheet, though. They need a concise summary they can absorb in 2 minutes at their Monday morning meeting. In this step, you will build that summary as a 4-slide executive presentation in PowerPoint for the web.

In this step, get ready to:

Open PowerPoint for the web and create a presentation with a title slide and program health summary.
Build a key finding slide that highlights Houston's underperformance with supporting data and a chart.
Add a recommendations slide with data-driven action items for senior leadership.

Open PowerPoint and build the title and program health slides

Executive reports follow a simple structure: title, headline metrics, key finding, recommendations. You will build one slide for each of these.

Switch back to your Microsoft365.com browser tab.
Click the App Launcher icon (the 3x3 grid of dots) in the top left corner.
Click PowerPoint.
Click New blank presentation.

A new presentation opens with a default title slide layout. PowerPoint for the web autosaves to OneDrive, so you will not lose your work.

Click the file name at the top of the screen (it says Presentation) and rename it to CB Analyst Program - Q3 Status Update.
Click the title placeholder on Slide 1 that says "Click to add title".
Type Commercial Banking Analyst Program - Q3 Status Update.
Click the subtitle placeholder below the title.
Type your name and today's date (for example, Alex Rivera - August 13, 2026).

Your title slide is complete. You should see the presentation title in large text with your name and date underneath.

Why this structure?

Senior leaders scan, they do not read. A title slide tells them what this is about. A KPI slide tells them the headline. A finding slide tells them what needs attention. A recommendations slide tells them what to do. Four slides, two minutes, done.

Now build Slide 2 to display your program's headline metrics.

Click Home in the top menu, then click the dropdown arrow on the New Slide button.
Select the Blank layout from the menu.
Click Insert in the top menu, then click Text Box.
Click and drag near the top of the slide to create a text box.
Type Program Health and increase the font size to 28 using the Font Size dropdown on the Home tab.

Click Insert again, then click Text Box.
Click and drag below the heading to create a larger text box for your KPIs.
Type the following KPIs, each on its own line. Press Enter after each one:

Overall Completion: 78%
Avg Satisfaction: 4.0/5
On-Track Analysts: 81%

Select all three lines of text you just typed.
Increase the font size to 36 or larger using the Font Size dropdown on the Home tab.

Slide 2 now shows three headline metrics in large, readable text. A senior leader can absorb these in seconds.

Text box not appearing?

Make sure you are on the Insert tab (not Home) when clicking Text Box. After clicking Text Box, you must click and drag on the slide to draw the box. Just clicking once may create a very small box that is hard to see.

help me add a text box in PowerPoint for the web

Build the key finding slide

Slide 3 is where you deliver the insight that justifies taking two minutes of a leader's time. You will state the problem in a headline, support it with specific numbers, and include a chart.

Click Home, then click the dropdown arrow on New Slide and select Blank.
Click Insert, then Text Box. Click and drag near the top of the slide.
Type Houston Cohort Requires Attention as the headline.
Increase the font size to 28 and apply Bold from the Home tab.
Click Insert, then Text Box. Click and drag below the headline to create a supporting data box.
Type the following data summary:

Training completion rate 20% below other locations
Satisfaction score: 3.4 vs 4.2 average
3 of 5 Houston analysts showing warning signs

Now add the comparison chart from your Excel tracker to give the data visual weight.

Switch to your Excel browser tab where the CB Analyst Program Tracker is open.
Click on your "Survey Score by Location" bar chart to select it. You will see selection handles around the chart border.
Press Ctrl+C to copy the chart.
Switch back to your PowerPoint browser tab and make sure Slide 3 is selected.
Press Ctrl+V to paste the chart onto the slide.
Drag the chart to position it below or beside your data summary text.

Chart did not paste?

Chart copy-paste between Excel for the web and PowerPoint for the web can behave differently depending on your browser. If the paste did not work, you can recreate the chart directly in PowerPoint.

Click Insert in PowerPoint, then click Chart.
Select Bar as the chart type.
In the data sheet that opens, replace the placeholder data with your three locations and their average survey scores (New York: 4.3, Charlotte: 4.1, Houston: 3.4).
Close the data sheet. The chart now appears on your slide.

help me get my Excel chart into PowerPoint for the web

Slide 3 now shows a clear headline, specific data points, and a visual chart. The Houston gap is undeniable.

Build the recommendations slide

A finding without a recommendation is just a complaint. Slide 4 tells leadership exactly what to do next, grounded in the data you just showed them.

Click Home, then click the dropdown arrow on New Slide and select Blank.
Click Insert, then Text Box. Click and drag near the top of the slide.
Type Recommended Actions as the heading.
Increase the font size to 28 and apply Bold.
Click Insert, then Text Box. Click and drag below the heading to create the action items area.
Type the following recommendations, pressing Enter between each one:

1. Schedule check-in with Houston site leads to identify local barriers
2. Deploy supplemental training support for Modules 3-4 where most incompletions occur
3. Resurvey Houston cohort in 2 weeks to track response to intervention

Select the text and set the font size to 20 so all three items fit comfortably on the slide.

Why are these good recommendations?

Each action item is specific, time-bound, and tied directly to the data. "Schedule a check-in with Houston site leads" names who, what, and why. "Resurvey in 2 weeks" gives a timeline. Senior leaders want to know what to approve, not vague next steps.

This is the difference between "we should look into this" and a plan they can act on immediately.

Slide 4 is complete. You now have a full 4-slide executive presentation.

Before you review your finished presentation, think about this: if a senior leader flipped through these four slides in 90 seconds, what three things should they walk away knowing?

Click through all four slides using the slide thumbnails on the left panel.
Confirm that the flow tells a complete story: what this is, how the program is doing, where the problem is, and what to do about it.

You should see a 4-slide presentation that communicates three messages: the program is mostly healthy, Houston is the problem, and here are the specific actions to take.

Slides look cramped or messy?

Resize text boxes by clicking and dragging the corner handles. Keep plenty of white space between elements. If text is overlapping, reduce the font size by 2-4 points or shorten the wording. Executive slides should feel spacious, not packed.

help me make my PowerPoint slides look cleaner and more professional

Secret mission

Build a Risk Tracker and Draft an Escalation

Great program analysts don't wait for problems to escalate. They flag at-risk analysts proactively and communicate issues before they become crises. In this mission, you'll build an automated risk tracker and draft a professional escalation email grounded in specific data points.

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 on free Microsoft 365 web apps, so there are no ongoing costs.

Resources you used:

CB Analyst Program Tracker Excel workbook (stored on OneDrive).
CB Analyst Program - Q3 Status Update PowerPoint presentation (stored on OneDrive).
Microsoft account (free, used to access Excel and PowerPoint for the web).

Keep everything running

No action needed. Choose this if you want to reference your tracker or presentation later, or continue building on them.

Your Excel workbook and PowerPoint presentation are saved automatically on OneDrive.
You can access them anytime by signing in at Microsoft365.com.
Your Microsoft account remains active with 5 GB of free OneDrive storage.

Pause - I'll come back to this later

There are no running processes or costs to pause. Your files stay safely on OneDrive until you return.

Sign out of Microsoft365.com when you are done to keep your account secure on shared devices.
Your workbook and presentation will be exactly as you left them when you sign back in.

Delete - I don't want to use this again

Remove all project files from your OneDrive. Optionally close your Microsoft account if you created it only for this project.

Delete the Excel workbook: In Microsoft365.com, click My Content in the left sidebar. Right-click CB Analyst Program Tracker and select Delete.
Delete the PowerPoint presentation: Right-click CB Analyst Program - Q3 Status Update and select Delete.
Empty the recycle bin: Click Recycle bin in the left sidebar of OneDrive, then click Empty recycle bin to permanently remove the files.
Close your Microsoft account (optional): Go to account.live.com/closeaccount and follow the steps to schedule account closure. Only do this if you created the account solely for this project.

Nice Work!

You just built a complete program analytics workflow from raw data to executive presentation. That is exactly what a Commercial Banking Project Analyst does every week.

You've learned how to:

Build a program analytics tracker in Excel for the web and calculate KPIs using COUNTIF and AVERAGEIF formulas that surface cohort health at a glance.
Spot a hidden performance gap using conditional formatting and charts that make trends visually undeniable to stakeholders.
Create an executive-ready PowerPoint report with data-driven recommendations that a senior leader can absorb in under 2 minutes.
Secret Mission: Built a risk tracker with compound IF formulas combining multiple conditions and drafted a professional escalation email citing specific data points.

Ready to quiz yourself?