Exercise 1, Task 4: Use Copilot in Excel to model What-If scenarios

Robin Kline, Fabrikam’s Finance Manager, asked you to update the Relecloud acquisition financials to reflect new assumptions, such as adjusted valuation multiples and projected synergy savings. Robin wants you to:

  • Update the Relecloud acquisition numbers based on a series of what-if scenarios.
  • Create charts that visualize the effect of these changes.

This task showcases Copilot’s ability to perform dynamic modeling and visualization, which are essential tools for any financial analyst preparing data-driven recommendations.

Perform the following steps to complete this task:

  1. Select the following link to download the Relecloud Acquisition Financials.xlsx file. Store the file in your OneDrive account for use by Copilot in your tenant.

  2. In your Microsoft Edge browser, go to the Microsoft 365 home page, select Apps in the navigation pane, and then select Excel from the Apps menu.

  3. In Excel for the web, select the Upload a file button, navigate to your OneDrive, and then select the Relecloud Acquisition Financials spreadsheet that you downloaded in step 1.

  4. On the Home tab ribbon, select Copilot. In the Copilot pane, leave the response mode selector set to Auto. Then verify the Edit with Copilot icon appears in the prompt field next to the plus (+) sign. If you don’t see it, select the plus sign and then select Edit with Copilot in the drop-down menu. The icon should now appear in the prompt field.

  5. Let’s begin by updating the Relecloud acquisition numbers based on a series of what-if scenarios. Verify you’re in the Financial Analysis sheet. In the Copilot prompt field, ask Copilot to perform a what-if scenario by updating the Relecloud acquisition financials to reflect a 1x increase in the earnings before interest, taxes, depreciation, and amortization (EBITDA) multiple and a 20% increase in synergy savings. Ask it to return the results in a new sheet.

  6. Review the results. Remain in this new what-if sheet and then ask Copilot to perform an EBITDA what-if scenario in which it generates the following charts in a new sheet to make it easy to visualize the magnitude and timing of improvements:

    • Column Chart comparing original vs. updated EBITDA and total synergy savings over time.

    • Line Chart showing EBITDA trend before and after the change.

  7. Review the results. Select the Financial Analysis sheet and then ask Copilot to perform another what-if scenario in which it updates the acquisition financial model based on the following what-if scenario: Model the impact if synergy savings are delayed by 12 months and only 75% are realized. Ask Copilot to return the results in a new sheet.

  8. Review the results. Remain in this new what-if sheet and then ask Copilot to generate the following charts in a new sheet that show both the timing and reduction in benefits, highlighting the impact on cash flow and ROI:

    • Stacked Column Chart showing annual synergy savings (original vs. delayed/reduced).

    • Line Chart for cumulative synergy savings over time.

  9. Review the results. Select the Financial Analysis sheet and then ask Copilot to perform a what-if scenario that updates the financial model based on operating expenses that are 10% higher due to integration challenges. Ask Copilot to return the results in a new sheet.

  10. Review the results. Remain in this new what-if sheet and then ask Copilot to generate the following charts in a new sheet that highlight the effect on profitability and expense trends:

    • Line Chart for operating expenses and EBITDA over time (original vs. scenario).

    • Column Chart comparing net income before and after the change.

  11. Review the results. Feel free to select any of Copilot’s suggested prompts if you want to update the spreadsheet even further.