MS Excel: Using What If Analysis to Forecast Profits Based on Sales Values in Excel (File in Link)
DescriptionThe video explains how to use different what-if analysis tools in Excel to forecast profits based on historical data and sales values. The tools used in this video include Scenario Manager, Goal Seek, Data Table, and Forecast Sheet.
The video starts by creating a new column and using the average formula to calculate the average sales. The percentage column is then created, where each item is displayed as a percentage of sales. For example, the cost of sales percentage is calculated by dividing the cost of sales by sales. This percentage is then used to forecast profits based on any new sales value entered.
Scenario Manager is then used to create three different scenarios: best, worst, and average. Each scenario is created by adding a new scenario name, specifying the cell reference for the changing cell (sales), and entering the value of the changing cell (sales) in the current scenario. By selecting any of these scenarios, the video shows how profits are affected under each scenario.
Goal Seek works in the reverse order of Scenario Manager. By specifying the amount of profits desired, Goal Seek computes the sales required to achieve those profits.
Data Table feature is then used to explore scenarios where profits may change while sales remain the same. Various percentages are entered in a column to calculate profits against each percentage, assuming average sales.
Finally, the Forecast Sheet tool is used to forecast profits based on historical data. The dates and profit values are selected, and the Forecast Sheet is used to draw a chart with the forecasted profits of future periods.
Excel File: https://docs.google.com/spreadsheets/d/12dR7_l1WmW_yqEnbXqfSLmpsmE0RpFfY/edit?usp=share_link&ouid=110287700425339553017&rtpof=true&sd=true
#WhatIfAnalysis #ExcelTips #Forecasting #DataAnalysis #ScenarioManager #GoalSeek #DataTable #ForecastSheet #ProfitForecasting #SalesAnalysis #BusinessPlanning #FinancialPlanning #ExcelFunctions #BusinessAnalysis
what if analysis,
cost of sales,
best case scenario,
worst case scenario,
data table feature,