How to Calculate P Value in Excel

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.

...
You can calculate project value in Excel.

Step

Create a table and then click on cell E4. Next, click on the "Insert Function" key. Scroll to the "Forecast" function and click it.

Step

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.

Step

Enter "Known_Y's" cell range as "C3:C7." These figures represent the actual Revenues from Shoes Sold in 2008 in this example.

Step

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.

Step

Copy cell E4 and paste into cells E5, E6 and E7.

Step

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.

Step

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.

Step

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.

Step

Delete the contents of cells D4, D5, D6, and D7. Notice that without any figures, the average for the upcoming year goes down dramatically.

Step

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.