Hi All,
My average (to perhaps below average) Excel skills have been exposed at this point and while I'd like to solve this on my own and pick up a ton of knowledge along the way, time does not permit and I need help.
I'm trying to build a basic call volume forecast model (attached) and here's the premise:
An advertising campaign starts on a certain day and 3 business days later calls start coming in. A reminder letter/email is sent about halfway through, and again calls spike a bit. Finally, the last 5 days of the campaign bring in the heaviest calls when a final reminder is sent. The balance of expected calls are spread out over the remaining days of the campaign (which vary in length), except for days 1-3. The 'Call Flow' tab is my attempt at modeling that and I'm fine with that part for now as this is what my research suggests happens for the most part.
What I need to do however, is then automatically "paste" the 34 days for Company 1's campaign into the appropriate 34 days on the 'Volume' tab and I'm lost as to how to do that. I'm trying to automate this as much as possible such that whoever would eventually own it could just put in the start, reminder, and end dates, as well as # of people and enrollment %, and everything else would calc itself, but it's hard to make it idiot-proof when you're the idiot designing it...
Thanks in advance for any help or ideas for improvement! PM me if you need any clarification on anything.
FEI7774
Used combined index & match function to resolve.
Bookmarks