Calculating the "P" (Project) Value in Excel helps you to foretell shopper trends, inventory supply needs or sales revenues. One technique used to calculate this value is the "Forecast" formula. It is based on the "X_Known" and "Y_Known" value ranges and the average gap between each set of known factors to obtain a realistic probable figure. A little experimentation will show you how Excel can help forecast what you need based on the Xs and Ys that you already know.
- Skill level:
Other People Are Reading
Create a table and then click on cell E4. Next, click on the "Insert Function" key. Scroll to the "Forecast" function and click it.
Enter D4 for the "X" value. This is the input area, where numerical values will be added once they become available for you to enter.
Enter "Known_Y's" cell range as "C3:C7." These figures represent the actual Revenues from Shoes Sold in 2008 in this example.
Enter "Known_X's" cell range as "B3:B7." These figures represent the actual Revenues from Shoes Sold in 2007 in this example. Next, click "OK" to exit.
Copy cell E4 and paste into cells E5, E6 and E7.
Select cell E5 and change the formula to read "=FORECAST(D5,C3:C7;B3:B7)". The range must match the same as E4, with the exception of the input cell, which in this case is D5.
Select cell E6 and change the formula to read "=FORECAST(D6,C3:C7;B3:B7)". The range must match the same as E4, with the exception of the input cell, which in this case is D6.
Select cell E7 and change the formula to read "=FORECAST(D7,C3:C7;B3:B7)". The range must match the same as E4, with the exception of the input cell, which in this case is D7.
Delete the contents of cells D4, D5, D6, and D7. Notice that without any figures, the average for the upcoming year goes down dramatically.
Enter the actual "Revenues from Shoes Sold for the 1st Qtr 2009" as "17,000." The new forecast for 1st Qtr 2010 is automatically calculated based on average known values. Repeat this process for the other quarters when new known figures become available.
Tips and warnings
- Highlight the "Input Areas" in the tables you create to differentiate them from other cells containing formulas. This will help deter overwriting accidents that could increase the likelihood of formula errors due to rushed recalculations.
- Create a backup of your spreadsheet because recreating formulas may not be very rewarding.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for