Want to Forecast Inventory and Impact

Excel Help for Want To Forecast Inventory And Impact in Presenting and Reporting Data

Want To Forecast Inventory And Impact

Rate this: (3/5 from 1 vote)

	Assume a real estate business has 5 homes in inventory for sale, and a sixth home has just closed to a homeowner. 1 home is C for closed (now belongs to someone else) 3 Homes are U for unsold 1 Home is S for Conditional Sold (needs to go firm, then close) 1 Home is F for firm Sale (closing in 2 months) I want a spreadsheet that allows one to forecast out when the remaining inventory will go through the U, S, F, and C stages (possible format suggested below) Also, by selecting a period of time, one can also see how many units are U, S, F, and C through that period, and look at the revenue and cashflow impact. Lastly, one must be able to view the forecasted inventory at a future point in time (this will depend on the forecast). So if everythign is U today, but a forecast for S F and C in the future, one should be able to look at a point in time in the future to see what their USF and C statuses are, and calculate necessary revenue, cashflow, and remaining inventory values. A suggested template is laid out below, but the only true source data is Address, Status and Price Forecasted Status ----> Excel Spreadsheet: Columns C through G are the forecast of status, columns A and B are the current data   A B C D E F G 1  Address  Status  1/31/2015  2/28/2015  3/31/2015  4/30/2015  5/31/2015 2 1 My Street  C           3 2 My street  U  s   f   c  4 3 My Street  U    s   f  5 4 My Street  U   s  f c  6 5 My Street  S  f    c     7 6 My Street  F    c       Notes: I thought Pivot Table would be a solution, but it doesnt work as good because I have column headings as dates, instead of the forecast data as dates. Pivots allow good use of date and period analysis, but my format seems to have neutralized that. I am open for format changes.
	Posted by nottajake on 24 Nov 2014

Replies - Displaying 1 to 3 of 3

Order Replies By: Most Recent | Chronological | Highest Rated

	Forecast time period instead of date Rate this: (3/5 from 1 vote) I reformatted the data, so you may want to check and Edit to line up the values again. In any case, I would make the calculations based on known values for real estate that have statuses of U, S, F and C. Instead of using dates, I would use the number of days on average between each status so that to estimate each value for a given inventory, the date can be used against the estimated number of days to forecast the date expected for the statuses of the new property. A better and more statistically robust way would be to use external factors and run multiple regression analysis to create a prediction formula for forecasting the number of days and/or cash flow etc. from many known observations.
	Excel Business Forums Administrator
	Posted by Excel Helper on 24 Nov 2014

Clarifying Rate this: (3/5 from 1 vote) Hi, I performed additional edit, and had to remove the price field to convey the story.   Column A should be "address", Column B should be "status". To clarify a point, "value" is a given and does not need to be forecast.   the only forecast that is needed is the timing of when the unit transitions through its various statuses up until closing. Although what I am most after is how to display the forecasted data into a concise activity report. So assuming I have the right values, and I ahve the right forecast for timing of status transition, how can one report  on the activity in the portfolio between say 2/28/2015 and 4/30/2015, with a restated inventory status at 4/30/2015

Posted by nottajake on 24 Nov 2014

	INDEX and MATCH functions Rate this: (3/5 from 1 vote) If I am correct, we need to match the status of a particular address given a specified date. In this case, it would be best to have continuous status codes in the table that we have so that we can match using the INDEX and MATCH functions. If we wanted to know the status of the row 4 (3 My Street) at 4/30/2015 we would want to have cell F4 as code "s" as it is follows on from E4.  Then we can grab the value using the MATCH on the date and INDEX on the code row. Since F1 is the date we require, the formula is: =INDEX(C4:G4,MATCH(F1,C1:G1,0)) The MATCH gets the offset 4 from the dates which is then used in the index of codes to return the correct status.  We can also use different match types (less than, greater than) for when the dates do not match exactly.  I hope this helps.
	Excel Business Forums Administrator
	Posted by Excel Helper on 24 Nov 2014

Displaying page 1 of 1