How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Michael Hannigan Your Own Question
Michael Hannigan
Michael Hannigan, Office Specialist
Category: Microsoft Office
Satisfied Customers: 11865
Experience:  25+ Years Experience in Computers and Electronics. Microsoft Certified MCSE, MCDBA, MCP, Microsoft Partner
Type Your Microsoft Office Question Here...
Michael Hannigan is online now
A new question is answered every 9 seconds

I need an EXCEL EXPERT! I own a company that mails out sales

Customer Question

I own a company that mails out sales letters each month - the quantity varies monthly
We accurately track:
1. Unique inbound CALLS
2. The MEETINGS that result from those CALLS
3. The SALES that result from those MEETINGS
We are trying to GOAL SEEK? [Predict] future business (takeup rates, etc) based on past LETTER SENDING activity using the stats we have already entered into the spreadsheet I'll supply you.
Prospective Clients respond during the month we mail the letter and up to 3 months later, but if you're clever with Excel (which I am not), you'll be able to show the distribution month by month.
I am happy to stay up for you to ask more questions (I'm in Australia and it's later here). This should be a simple solution for the right person.
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  Richard replied 2 years ago.

Hi there and welcome

To clarify, you want to see responses from clients seperared in months Mark?

Customer: replied 2 years ago.
Hi Richard, I can send you the spreadsheet I have. I just need you to populate the yellow areas, based on the ACTUAL DATA I have entered so that I can predict FUTURE business on past results.
Expert:  Richard replied 2 years ago.

ok... ill look at it and see what it is your asking for.

Attach it here via the paperclip.

Customer: replied 2 years ago.
Here it is
Expert:  Richard replied 2 years ago.

ok, and this is what I initially asked Mark, what is it you want done in the yellow boxes?

Please remember I know nothing about this sheet, so please explain what you want there and from which data?

Customer: replied 2 years ago.
OK, the data you see is ACTUAL stats. Based on the data from October 2014, I need to see the response rate per month from January 2015 to August 2015 and projected response rate from September 2015 thru June 2016, based on the historical data.
Customer: replied 2 years ago.
i.e. if I sent out 30 letters in October, 40 in November and 60 in December, based on the data you see for Inbound calls from those letters, I need to map a scenario of past (Jan-Aug) and future calls based on that data. Row 3 is what I need populated initially and that will allow us to more easily populate rows 6, 9 and 17
Customer: replied 2 years ago.
I hope that was clear enough? I know that I can make a flatline estimate by dividing total Inbound Calls by Letters Sent and dividing by the number of months but I'm after more accuracy than that.
Expert:  Richard replied 2 years ago.

ok, sorry but its not very clear what you require. Its common as you know this sheet inside out.

Explain it like this.

What formula should be applied. Tell me this in your own words

eg: 40% of the letters sent out in October should be shown in September.

Customer: replied 2 years ago.
OK, rows C4 - M4 total 12,415 Sales Letters Sent. If I never sent any more letters out again, I could just wait a couple more months and then simply work out my AVERAGE monthly response rate based on the number of inbound calls. But we mail out Letters every month and the qty varies. What I want to see is based on that historical data, what is the relationship in % of Row 7 to Row 4?
Expert:  Richard replied 2 years ago.

ok, so if you put in January =F4/F7

Then you get 15 showing 15% meaning 15% of the recipients replied.

Is this what you want?

Customer: replied 2 years ago.
No, I know how to do that already. What I am looking for is the relationship between the Row 3 figures to the Row 7 figures, once we know that, we can work out the rest.There will be a "lag" - e.g. XX% of the letters for October generate Inbound Calls in January, February and March
Then XX% of the letters fro November will also contribute to the Inbound calls in January, February and March and April
Then XX% of the Letters sent in December will bring inbound calls to January, February and March April and May.If it's too difficult, I can get a colleague at work to help.
Expert:  Richard replied 2 years ago.

No sorry I am not following what it is you want. I think its best I opt out and an expert that can better understand statistics as opposed to Excel can assist you. You do not need to do anything, you will be notified when a new expert picks up your case.

Sorry I could not solve this for you.

Thank you

Customer: replied 2 years ago.
Thanks Anyway Richard, Goodnight.
Expert:  Michael Hannigan replied 2 years ago.

Hello. My name is***** will try to provide you with some additional assistance. In order to accomplish this, it needs to be boile down basically to an Excel formula which contains math and functions offered by Excel. Do you have an idea of what this formula will be? I'm referring to the formula in the predictive cells? As I'm sure you know it can't simply be predictive as there isn't a function that provides a predictive result – but if you could give a concrete example of what it would be in terms of and equation I should be able to help you get it into something Excel will understand.