Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course




do you have a video or tips on how to project or forecast?


8 brokers with different targets by end of Month:

  • 1 Broker - 10%
  • 2 Brokers - 15%
  • 5 Brokers- 12%

I need to identify how many sales per day (weekdays only) do they still need to achieve their specific target by end of month, given that Oct1-10 had passed already and each of them have sales already.

Thank you.




At the stage that you appear to be at now I usually forego Excel altogether. I take a pen and paper. You will probably need ...

  1. an input sheet for targets.
    You probably need the brokers' names and the target, perhaps a target date. So, there would be two or three columns,
  2. an input sheet for data about the business the brokers concluded.
    You would probably need a date column, one for the broker's name, one for the volume done. Perhaps there will be a reference number or a customer names as well, in total at least 4 columns,
  3. an output sheet to show the relationship between target set and business done for each broker for a definable period.
    You would probably want cells to define the period (from date and to date). Then you would decide if you want a printable form which you can give to each broker with only his own data on it or whether you prefer a list of all 8 brokers,showing target and business volume for the selected period for all of them.
  4. In the latter case you would consider joining the first and third sheets into one. Or you might want to do both, create an evaluation column in the first sheet and prepare the individual evaluation sheet as well.

With that done you can turn to Excel. Start naming and formatting the columns. But most importantly, design the work flow. Above I was thinking of entering the targets on one sheet (perhaps monthly), the transaction data on another (probably daily) and evaluate both data on a third sheet whenever you want. The design of this flow must match your business environment.

With all of that done you can start entering your data. And once you have data to evaluate you can come back here to get help with the evaluation sheet.



Thank you. Yes I have a raw data already. Ill try to make a copy but the simplified form (will upload later). which shows Date, Brokers Name, Customers Name. I can get their daily targets by using Pivot. I now need to give them a projected/forecasted number on how many sales they still need to achieve their targets by End of MOnth. 
corpuzjunel (rep: 4) Oct 12, '18 at 1:29 am
Sample file has been uploaded
corpuzjunel (rep: 4) Oct 12, '18 at 6:24 am
Which of the three sheets I suggested you need is the sheet you currently have? It looks like the sheet for inputting transactions (#2 above) but that sheet doesn't need the targets. The targets in your sheet should be in sheet #1 (input for targets) but that sheet must match brokers with targets and doesn't need customer names or dates. I suggest you build your project based on the logic I have provided. From the sheet you have a projection can't be made because it lacks data.
Variatus (rep: 4889) Oct 12, '18 at 9:20 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login