Forecasting the financial data model in Excel

Dear everyone, Hope you all are doing well,

Can we forecast the financial data and predict the future value by analyzing in Excel. Is that real or possible. That's what you may think.

Yes it is possible to forecast the data in excel.

I'm exicted to share you about how that we can forecasting the data with the help of Excel. This is our second part of Learning new things everyday.

Let's know what is Forecasting is, It means making a prediction about what might happen in the future based on current information and past experiences. It's like using clues from the present and the past to estimate what is likely to occur down the road. Businesses often use forecasting to plan for things like sales, expenses, and growth.

There are many ways to forecast the data in excel and I'm going to show you how we can forecast the data with one of the method called Goal seek analysis method, If you have a input and want to find the certain output to reach that goal, you can use Goal Seek.

Let's get started, begin with financial forecasting of JK company (own made up company). We are going to face scenarios to find the target value with goal seek analysis.

 

FINANCIAL DATA MODEL

Creating the output financial data model by calculating,

Revenue = Sales price * Units sold

Cost of goods sold = Manufacturing cost * Total number of units sold

Gross profit = Revenue - Cost of goods sold

Marketing cost = Customer acquisiton cost * Total number of units sold

Total profit = Gross profit - Rent + Marketing expenses

Profit percentage = Total profit/ Revenue

FORECASTING THE FINANCIAL DATA 

Now we use Goal seek analysis in excel to find expected value,

Select the Data tab -----> Go to what-if analysis --> Select Goal seek

Questions

1. How do we get a 70% profit margin ?

Let's say to get profit to 70%, How much Customer Acquisition Cost need to achieve 70%.

• Using goal seek method we have selected profit percentage value, to value 70% (0.70), by changing customer acquisiton cost (CAC). As you can see below, to achieve 70% profit we need to set the CAC cost from 135 to -110. Wonderful right.

Forecasted Customer Acquisition cost :

2. How can we get 300K in gross profit ?

Find, what sales price gets us to 300K? How many units get us to 300K ?

• Again using goal seek method we have selected gross profit total, to value 300,000 (gross profit) , by selecting sales price value (450). As you can see below, we need to set the sales price to 535 to achieve gross profit 300K.

• Find how many units get us to 300K ?. We have selected gross profit total, to value 300,000 (gross profit) , by selecting sales unit value (1000). As you can see below, we need to set the sales unit to 1395 achieve gross profit 300K.

Forecasted sales price :

Forecasted sales unit :

CONCLUSION

I hope you find this information new and useful. Share with others to know about forecast the data in excel and comment your th. You never know what is possible without finding the solution for it. Learning new things everyday makes our work productive. Always learn new things to update yourself, do it.