3.0. Core application of data analytics

3.1. Financial Accounting And Reporting

3.1.3.1. Carry out sensitivity analysis and scenario analysis on the forecast financial statements

Sensitivity analysis and Scenario analysis:

Analyzing forecasted financial statements through sensitivity and scenario analysis is a crucial step in the financial planning and decision-making process. Sensitivity analysis helps organizations understand how variations in key variables or assumptions impact their financial projections, allowing them to assess the resilience of their financial plans to different scenarios. Scenario analysis, on the other hand, involves exploring a range of potential future situations to evaluate the potential outcomes and risks associated with each. Together, these analytical approaches empower businesses to make informed decisions, enhance risk management, and navigate uncertain economic landscapes with greater confidence and agility.

The Importance of Financial Resilience

In an ever-changing economic landscape, businesses face a continuous need to adapt and thrive. Financial resilience, the ability to withstand external shocks and uncertainties, is a hallmark of successful organizations. This study delves into the critical role played by sensitivity and scenario analysis in enhancing financial resilience.

Sensitivity Analysis: Understanding Variability

Sensitivity analysis is a fundamental tool that helps organizations understand how changes in key variables or assumptions can impact financial projections. By varying one variable at a time while keeping others constant, businesses can quantify the degree of sensitivity and identify potential risks. For example, in the context of sales forecasts, sensitivity analysis can reveal how changes in pricing, demand, or market conditions affect revenue projections.

Example 1

Scenario: A retail company is forecasting its annual revenue for the next year. One of the critical factors affecting revenue is the selling price of its products. The company wants to assess how sensitive its revenue is to changes in product prices.

Data:

• Current projected annual revenue: $1,000,000
• Current average product selling price: $100 per unit
• Current projected unit sales: 10,000 units

Step 1: Baseline Scenario

In the baseline scenario, the company maintains the current projected annual revenue of $1,000,000, which is calculated based on the current selling price of $100 per unit and projected unit sales of 10,000 units.

Step 2: Adjusting the Variable

Now, the company wants to see how changes in product prices affect revenue. They consider two scenarios: an increase in selling price and a decrease in selling price.

1. Scenario 1: Price Increase
   • New selling price: $110 per unit
   • Calculated annual revenue: $110 * 10,000 units = $1,100,000
2. Scenario 2: Price Decrease
   • New selling price: $90 per unit
   • Calculated annual revenue: $90 * 10,000 units = $900,000

Step 3: Analyzing Sensitivity

By comparing the baseline scenario with the adjusted scenarios, the company can assess the sensitivity of its revenue to changes in product prices.

• In Scenario 1 (Price Increase), the revenue increases to $1,100,000, indicating that a 10% increase in selling price led to a 10% increase in revenue.
• In Scenario 2 (Price Decrease), the revenue decreases to $900,000, indicating that a 10% decrease in selling price led to a 10% decrease in revenue.

Conclusion:

This sensitivity analysis reveals that the company's revenue is sensitive to changes in product prices. A 10% change in selling price directly affects revenue by the same percentage. It provides valuable insights into how pricing strategies can impact the company's financial performance and helps in making informed decisions regarding pricing adjustments.

Performing sensitivity analysis in Excel

Performing sensitivity analysis in Excel involves setting up a data model that allows you to vary one or more input variables while observing the resulting changes in the output (e.g., revenue, profit). Here's a step-by-step guide on how to do sensitivity analysis in Excel:

Step 1: Set Up Your Data Model

Create a new Excel spreadsheet and label your columns. For this example, let's use the pricing scenario mentioned earlier, where we want to analyze the impact of selling price on revenue. Your columns might look like this:

• Column A: Selling Price (Base and Scenarios)
• Column B: Units Sold (constant)
• Column C: Revenue (Formula)

Step 2: Enter Your Baseline Data

• In cell A2, enter your baseline selling price (e.g., $100).
• In cell B2, enter the number of units sold (e.g., 10,000).
• In cell C2, calculate the baseline revenue using a formula. In this case, use =A2*B2.

Step 3: Set Up Scenarios

• In cell A3, enter a different selling price for your first scenario (e.g., $110).
• In cell A4, enter another selling price for your second scenario (e.g., $90).

Step 4: Calculate Revenue for Scenarios

• In cell C3, calculate the revenue for the first scenario using a formula, e.g., =A3*B2.
• In cell C4, calculate the revenue for the second scenario using a formula, e.g., =A4*B2.

Step 5: Analyze Sensitivity

Now, you can visually analyze the sensitivity of revenue to changes in selling price. Observe how the revenue changes in cells C3 and C4 based on the different selling prices in cells A3 and A4.

Step 6: Create a Data Table (Optional)

If you want to perform sensitivity analysis for a range of values, Excel's Data Table feature can be useful:

1. Select a range that includes your input cells (A2 and A3) and the corresponding output cells (C3 and C4).
2. Go to the "Data" tab and select "What-If Analysis" and then "Data Table."
3. In the "Row input cell" box, reference the cell containing the variable you want to change (e.g., A2).
4. Click "OK," and Excel will automatically fill in the table with the calculated values for each scenario.

This will create a table that shows how revenue changes for various selling prices, allowing you to easily observe the sensitivity of revenue to changes in the selling price.

By following these steps, you can effectively perform sensitivity analysis in Excel and gain insights into how changes in input variables affect your output results.

Example 2

One Variable Sensitivity Analysis

Example 3

Two Variable Sensitivity Analysis

Scenario Analysis: Preparing for the Unknown

Scenario analysis takes financial planning a step further by exploring multiple potential scenarios and assessing their impact on financial outcomes. Organizations create a range of scenarios, from best-case to worst-case, to evaluate the resilience of their financial plans. This approach provides a holistic view of the future, allowing for better decision-making and risk mitigation strategies.

An example of scenario analysis in the context of financial planning for a retail business:

Scenario 1: Best-Case Scenario - Economic Boom

In this scenario, the economy experiences a rapid and sustained period of growth. Consumer spending is strong, and the retail industry benefits from increased foot traffic and higher sales.

Financial Projections:

• Sales Revenue: 20% growth compared to the previous year.
• Gross Margin: Steady or slightly improved due to economies of scale.
• Operating Expenses: Controlled and in line with revenue growth.
• Profit Margin: Healthy profit margins due to increased sales.

Implications and Decisions:

• In a best-case scenario, the business may consider expanding by opening new locations or investing in marketing to capture more market share.
• Capital investment in technology and infrastructure could be justified to handle increased sales volumes efficiently.
• Hiring additional staff or enhancing employee training to meet higher customer demands might be necessary.

Scenario 2: Base-Case Scenario - Steady Growth

In this scenario, the economy maintains a stable growth rate, and the retail industry continues to perform as expected.

Financial Projections:

• Sales Revenue: 5% growth compared to the previous year, in line with historical trends.
• Gross Margin: Steady with minor fluctuations.
• Operating Expenses: Moderate growth to support business operations.
• Profit Margin: Stable profit margins.

Implications and Decisions:

• The business may focus on improving operational efficiency to maintain profitability.
• Monitoring and optimizing inventory levels to control costs can be a priority.
• Evaluating new product lines or services to diversify revenue streams may be considered.

Scenario 3: Worst-Case Scenario - Economic Recession

In this scenario, the economy experiences a recession, leading to reduced consumer spending and financial challenges for businesses.

Financial Projections:

• Sales Revenue: 10% decline compared to the previous year.
• Gross Margin: Pressure on margins due to lower sales and potential discounting.
• Operating Expenses: Cost-cutting measures required to mitigate revenue loss.
• Profit Margin: Declining profit margins, possibly resulting in losses.

Implications and Decisions:

• Cost reduction strategies, such as layoffs, reduced store hours, or renegotiating supplier contracts, may be necessary.
• Evaluating cash reserves and securing lines of credit to ensure the business's survival during tough times.
• Adjusting inventory levels to match reduced demand and avoid overstocking.

By conducting scenario analysis encompassing these three scenarios (best-case, base-case, and worst-case), the retail business can better understand how external factors could impact its financial performance. This holistic view enables the development of flexible strategies that can be deployed based on the actual economic conditions, enhancing the company's resilience and decision-making capabilities.

Example 4

Try this question and compare your solution bellow:

Amani Pharmaceuticals Limited is a mid-sized firm exploring prospects of future growth .The management of the company has prepared the statement of profit or loss for the year ended 30 June 2022 wuth forecast of expected growth rates in the various line items for the next three years

This information is presented below:


Aditional information:

1.	Assume a corporation tax rate of 30%
2.	The growth in equity is only attributable to changes in retained profits. Hint: In applying the growth rates, ignore changes in opening and closing balances.
3.	Total equity will grow only as a result of retained profits.
Required
(a)	Prepare a spreadsheet showing the forecast statement of profit or loss for the next three year scenarios.
(b)	Compute the following metrics for evaluating performance under each scenario: (i) Net profit margin (ii) Return on assets (iii) Return on equity
(c)	Present a summarised dash board with suitable graphs to highlight the revenues and net profit under each scenario,

Unlocking Financial Resilience

1. Strategic Risk Management: Sensitivity and scenario analysis enable organizations to identify vulnerabilities and prepare for potential risks. By proactively addressing these risks, businesses can minimize the impact of adverse events on their financial health.
2. Informed Decision-Making: Armed with insights from these analyses, decision-makers can make informed choices that align with their organization's goals. They can allocate resources wisely, prioritize initiatives, and adjust strategies as needed to navigate changing conditions.
3. Dynamic Financial Planning: Incorporating sensitivity and scenario analysis into financial planning processes transforms them into dynamic and agile processes. Organizations can adjust their plans in real-time based on evolving circumstances, fostering adaptability and resilience.

Embracing Financial Resilience

Mastering financial resilience through sensitivity and scenario analysis is essential for businesses looking to thrive in an unpredictable world. By harnessing these analytical tools, organizations can weather storms, make well-informed decisions, and position themselves for long-term success. Financial resilience is not just a goal; it's a strategic imperative for the modern business landscape.