HELP with Forecast / Projection

  • Thread starter Thread starter Fable
  • Start date Start date
F

Fable

Greeting,

How can I make Excel read from one cell a total amount and have that
total number distribute in proportional to what in a range already for
example:


Xyz Company is forecasting to hit $10,000.00 in revenues for February
2004, Xyz Company already has a certain amount of revenues on the books
let say $3,400 among the 29 days in the month with higher tendencies
during the weekends. How can I get excel to distribute the remaining
$6,600.00 among what’s already on the books following the trend?

Your advice would be deeply appreciated!

Fable
:)
 
Fable,

What's the trend?

Lacking more information, you would simply take $6600/29 and add that to
each date. But from your message, I don't believe you were looking for
that. You wanted something to capture more revenue on weekends. How much
higher on a weekend? All weekends the same? Without knowing what your
"trend relationship" looks like, I am unable to suggest how to distribute
the revenue amongst the days in the month.

Regards,
Kevin
 
One way:

Say B1:B29 are the revenues booked already, and C1:C29 are the
projected revenues remaining. Assume A1 contains the forcast amount
($10,000)

C1: =($A$1-SUM($B$1:$B$29))*B1/SUM($B$1:$B$29)

which can be simplified to

C1: =($A$1/SUM($B$1:$B$29)-1)*B1

which can then be copied down.

Of course, if

B32: =SUM($B$1:$B$31)

then

C1: =($A$1/$B$32)-1)*B1

And to keep from getting #DIV/0 errors if there's no booked revenue:

C1: =IF($B$32<>0, (($A$1/$B$32)-1)*B1,"")
 
Back
Top