Help With Creating a Projection/Forecasting Workbook

[Protectyaneck1]

Good morning everyone,

I was hoping to get some input/ideas for a project I am working on to run projections/forecasting for a bunch of medical doctors projecting billing.

So I have a bunch of data collaborated together for each medical provider; such as their calendar, number of patients, charge per patient, and most important avg amount of days it takes for them to complete their report for each visit.

So for example: Doctor A has 5 patients on 5/20. Dr. A normally bills out $500 a report and takes 20 days to bill out this report. So I would want my projection numbers for June (5/20+20 days) to be the est sum of accounts billed (5 patients*$500).

Now obv this is easy to do one by one, but I am trying to figure out how to do this on a grand scale as we have 50 providers who see patients on different, charge different amounts with time ranges anywhere from 5 days to 35 days.

I would like to be able to enter the quantity amount of patients each provider has on each day, and then be able to view the projected billings based off when we can anticipate the bill going out.

Any help or information as to how to get this going would be greatly appreciated!

Thank you!

 

[oldbrewer]

											A	20	500
											B	35	400
			number of patients				C	5	450
	01/05/2016	02/05/2016	03/05/2016	04/05/2016	05/05/2016	06/05/2016	07/05/2016	08/05/2016	09/05/2016	10/05/2016	11/05/2016	12/05/2016	13/05/2016	14/05/2016	15/05/2016
A	5	4	3	6	4	4	5	5	4	3	6	4	4	5	3
B	4	4	5	5	4	3	4	4	5	5	4	3	5	4	3
C	4	5	5	4	5	5	4	5	5	4	5	5	4	4	4
	01/05/2016	02/05/2016	03/05/2016	04/05/2016	05/05/2016	06/05/2016	07/05/2016	08/05/2016	09/05/2016	10/05/2016	11/05/2016	12/05/2016	13/05/2016	14/05/2016	15/05/2016	16/05/2016	17/05/2016	18/05/2016	19/05/2016	20/05/2016	21/05/2016	22/05/2016	23/05/2016	24/05/2016	25/05/2016	26/05/2016	27/05/2016	28/05/2016	29/05/2016	30/05/2016	31/05/2016	01/06/2016	02/06/2016	03/06/2016	04/06/2016	05/06/2016	06/06/2016	07/06/2016	08/06/2016	09/06/2016	10/06/2016	11/06/2016	12/06/2016	13/06/2016	14/06/2016	15/06/2016	16/06/2016	17/06/2016	18/06/2016	19/06/2016	20/06/2016	21/06/2016	22/06/2016	23/06/2016	24/06/2016	25/06/2016	26/06/2016	27/06/2016	28/06/2016	29/06/2016	30/06/2016	01/07/2016	02/07/2016	03/07/2016	04/07/2016	05/07/2016	06/07/2016	07/07/2016	08/07/2016	09/07/2016	10/07/2016	11/07/2016	12/07/2016	13/07/2016	14/07/2016	15/07/2016
A																					2500	2000	1500	3000	2000	2000	2500	2500	2000	1500	3000	2000	2000	2500	1500	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
B																																			1600	1600	2000	2000	1600	1200	1600	1600	2000	2000	1600	1200	2000	1600	1200	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
C						1800	2250	2250	1800	2250	2250	1800	2250	2250	1800	2250	2250	1800	1800	1800	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
					using the right start point and a look up table for the rate - would this do ?

<colgroup><col><col span="5"><col span="72"></colgroup><tbody>
</tbody>

 

[Protectyaneck1]

Thanks for the reply. I think this is exactly what I am looking for, just trying to figure out how you did it! So correct me if I am wrong, your first table has the number of patients for each provider and the second table is taking the estimated billing based off the number of patients and putting the amount in the expected bill date based off their avg? How do you get the second table to produce the amounts in future dates?

I greatly appreciate your assistance in this!!
Thanks!

 

[oldbrewer]

if you really want to predict cash flow in 3 months time it can only be based on "averages". Say you have a year's worth of data for the doctors. You could average their income over the whole year, or last 6 months, or last 3 months.... it would be your call......

(I did actual calculations eg if doctor bills in 35 days and sees 7 patients on May 1, then fee multiplied by 7 will be billed June 4..........)