Initial Attempt Using Data Table Feature

Initially, I attempted to derive the desired output using the Data Table feature under Excel's What-If Analysis tools. However, this feature is limited to accommodating only two variables — one for the row input and one for the column input. In my case, both input variables — Sales Change (%) and Expenses Change (%) — are placed in row format (cells B32 and B33), making it difficult to represent them together using the traditional data table method. Hence, I was only able to vary one parameter at a time (either Sales or Expenses) along a column axis.

2.Solution Using Python Script with AI Assistance

To overcome this limitation, I utilized a Python script embedded directly in Excel 365 using the new native Python integration. With the help of ChatGPT, I generated a script that dynamically calculates the entire Profit & Loss Account based on variable inputs for both Sales and Expenses. This allowed me to create a fully updated statement that reacts instantly to changes in both variables — something that would not be feasible using just Data Tables.

Prompt:

“  In sheet Named "Fin Stmt" I have profit ad loss account statement, in column A it has particulars and B has few metrics which can be ignored Cloumn C starts with year 2025 to column L 2034 I have input Cells in B32 for % change in sales in B33 for % change in Expenses For Every Change in Sales or expense the entire financial statement has to change in the output cell Financial Statement includes - Sales, Expenses, Depreciation, PBIT, Interest, PBT, Tax(20% of PBT), PAT, NP ratio i need python code in excel 365 with xl() function to denoe the sheets. The sheet name is "Fin Stmt" in the code add quotes to sheet name 'Fin Stmt' 

Python Script:

import pandas as pd

# Utility to clean Excel rows

def clean(row):

   return [x if x is not None else 0 for x in row]

# 1. Inputs

sales_change = xl("'Fin Stmt'!B32")

expenses_change = xl("'Fin Stmt'!B33")

sales_change = sales_change if sales_change is not None else 0.0

expenses_change = expenses_change if expenses_change is not None else 0.0

# 2. Read base values from 'Fin Stmt' sheet

sales       = clean(xl("'Fin Stmt'!C3:L3").values.tolist()[0])

expenses    = clean(xl("'Fin Stmt'!C4:L4").values.tolist()[0])

depreciation = clean(xl("'Fin Stmt'!C5:L5").values.tolist()[0])

interest    = clean(xl("'Fin Stmt'!C7:L7").values.tolist()[0])

# 3. Apply % changes

adj_sales   = [s * (1 + sales_change) for s in sales]

adj_expenses = [e * (1 + expenses_change) for e in expenses]

# 4. Compute PBIT, PBT, Tax, PAT

pbit = [s - e - d for s, e, d in zip(adj_sales, adj_expenses, depreciation)]

pbt = [p - i for p, i in zip(pbit, interest)]

tax = [0.2 * p if p > 0 else 0 for p in pbt]

pat = [p - t for p, t in zip(pbt, tax)]

# 5. Compute Ratios

np_ratio = [(p / s * 100) if s != 0 else 0 for p, s in zip(pat, adj_sales)]

# 6. Prepare Output as DataFrame

output = pd.DataFrame({

   "Sales": adj_sales,

"Expenses": adj_expenses,

"Depreciation": depreciation,

   "PBIT": pbit,

"Interest": interest,

   "PBT": pbt,

   "Tax": tax,

   "PAT": pat,

   "NP Ratio (%)": np_ratio

}, index=[str(year) for year in range(2025, 2035)]).T

output

3.Scenario Manager – A Partial Alternative

While the same multi-variable simulation can technically be achieved using the Scenario Manager, the issue lies in its implementation — it tends to overwrite or collapse the original dataset, requiring a workaround or duplicate setup. Although it delivers the desired result, it's not optimal for continuous modelling or dynamic visual analysis.

1. Advanced Output Table: Net Profit Ratio Matrix
2. Using Python, I was able to construct a matrix showing the average Net Profit Ratio (%) over 10 years across multiple combinations of changes in Sales (Turnover) and Expenses. The structure of the output table is as follows:

This type of cross-tabulated sensitivity table cannot be generated using either Scenario Manager or Data Tables. It can only be created using Python or VBA scripting. The major advantage is that with the support of AI-generated code, we can now build such complex analytical outputs in seconds.

           Prompt

Can you modify the code and give me one more table in following format - Change in Average NP Ratio of 10 years Particulars 20% inc in exp 10% inc in exp nochange 10% dec in exp 20% dec in Exp 20% increase in TO 10% increase in TO No change 10% Decrease in TO 20% Decrease in TO Above table should be based on input cell % change in Sales (B32) and %change in Expense (B33) and every change that is 10% inc/dec 20% inc/dec should be based on input cell (B34) change in %.

Python Code

# Get increment base from input cell

inc_base = xl("'Fin Stmt'!B34")

inc_base = inc_base if inc_base is not None else 0.1 # default to 10% if not provided

# Define scenarios for changes

sales_changes = [-2*inc_base, -inc_base, 0, inc_base, 2*inc_base]

expenses_changes = [-2*inc_base, -inc_base, 0, inc_base, 2*inc_base]

# Build result table

result_data = []

for s_change in sales_changes:

   row = []

   for e_change in expenses_changes:

       # Recalculate based on these scenario values

       adj_sales = [s * (1 + s_change) for s in sales]

       adj_expenses = [e * (1 + e_change) for e in expenses]

       pbit = [s - e - d for s, e, d in zip(adj_sales, adj_expenses, depreciation)]

       pbt = [p - i for p, i in zip(pbit, interest)]

       tax = [0.2 * p if p > 0 else 0 for p in pbt]

       pat = [p - t for p, t in zip(pbt, tax)]

       np_ratio = [(p / s * 100) if s != 0 else 0 for p, s in zip(pat, adj_sales)]

       avg_np_ratio = sum(np_ratio) / len(np_ratio)

row.append(round(avg_np_ratio, 2))

result_data.append(row)

# Create label text

def label(val):

   if val == 0:

       return "No change"

   elif val > 0:

       return f"{int(val*100)}% increase"

   else:

       return f"{abs(int(val*100))}% decrease"

# 8. Prepare summary DataFrame

summary_df = pd.DataFrame(

   result_data,

index=[f"{label(s)} in TO" for s in sales_changes],

columns=[f"{label(e)} in Exp" for e in expenses_changes]

)

summary_df.index.name = "Particulars"

summary_df

1. Heatmap Visualization with a Simple Prompt

Lastly, with just a simple prompt, I was also able to generate a heatmap for the above sensitivity matrix using Python in Excel — providing a clear visual of how changes in sales and expenses impact the Net Profit Ratio. This added visual layer enhances interpretability for management decision-making.

Prompt

           can you give me a python code to create a suitable chart for this table ?

           Python Code

import matplotlib.pyplot as plt

import seaborn as sns

# Assuming 'summary_df' from earlier is available

plt.figure(figsize=(10, 6))

sns.heatmap(summary_df, annot=True, fmt=".2f", cmap="YlGnBu", cbar_kws={'label': 'Avg NP Ratio (%)'})

plt.title("Impact of TO and Expense Changes on Avg NP Ratio (10 Years)")

plt.ylabel("Turnover Change")

plt.xlabel("Expense Change")

plt.xticks(rotation=45)

plt.tight_layout()

plt.show()

Conclusion: Python in Excel, powered by AI, enables dynamic financial modeling beyond the limits of traditional tools like Data Tables and Scenario Manager. Thank you