Employee head count

SPVM

New Member
Joined
Jan 14, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hi

I am busy with a 5 year financial forecast. My intention is to appoint two new employees each year. To keep it simple for now, each employees sales income and cost to company is equal. eg Sales = $50k, CTC $30k.
The variable is when the employees starts. My assumption is that once the employee starts he will continue until the end of the 5 year forecast.

January 2021= 2 Employees. Entire year 2 employees. Sales=$100k, CTC=$60k. They will continue for the entire forecast. 5x12=60 months.

January 2022+1. Therefore 3 employees until June. $150k, CTC $90k and continue for the rest of the 5 year forecast.
Then July 2022+ 1. Therefore end of year 2022. Employee head count 4. Sales=$200k, CTC=$120k. They will continue until the completion of the 5 year forecast.

January 2023+1. Therefore 5 employees until June. $250k, CTC $150k
Then July 2023+ 1. Therefore 6 employees to the end of year 2023. Head count 6. Sales=$300k, CTC=$180k. They will continue until the completion of the 5 year forecast.

Same assumptions for 2024 and 2025.

Where it becomes complicated. I want to be able to change when the employee starts. Not a fixed January and July, for each year. Maybe January and August, or February and September. Etc, Ect.

I have a table, of 5 rows representing the years and 12 columns representing the months. I complete the forecast by inserting a X, 0 or 1 in the relevant cell corresponding to his start date.

Then I want to be able to have a formula that will tell me. Employee head count, Sales total and CTC for any given month in the total of 60 months.

Eg. Cell E4 is April 2023. The the cumulative figures from the start January 2021 (B2) is Head count x, sales xy and CTC is xyz.

Then I can check June 2024 which is cell G5.

I need to be able to instantly see my Employee head count, sales and CTC for any given month throughout the 5 year forecast, as per my initial data input of when employees would be appointed.

Is the beyond excels capabilities or am I able to do this.

Please help.

Kind Regards
David
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Following your assumptions it could be something like (notice this is not "flexible")
Book1
BCDEFGHIJKLM
1Sales IncomeCost To Company
2k$ 50k$ 30Start YearSales IncomeMid Year Starter20212022202320242025
32021FTE 1k$ 50k$ 50k$ 50k$ 50k$ 50
4YearSales IncomeCost To Company2021FTE 2k$ 50k$ 50k$ 50k$ 50k$ 50
52021k$ 100k$ 602022FTE 3$ -k$ 50k$ 50k$ 50k$ 50
62022k$ 175k$ 1052022FTE 4Y$ -k$ 25k$ 50k$ 50k$ 50
72023k$ 275k$ 1652023FTE 5$ -$ -k$ 50k$ 50k$ 50
82024k$ 375k$ 2252023FTE 6Y$ -$ -k$ 25k$ 50k$ 50
92025k$ 475k$ 2852024FTE 7$ -$ -$ -k$ 50k$ 50
102024FTE 8Y$ -$ -$ -k$ 25k$ 50
112025FTE 9$ -$ -$ -$ -k$ 50
122025FTE 10Y$ -$ -$ -$ -k$ 25
13Totalk$ 100k$ 175k$ 275k$ 375k$ 475
Sheet1
Cell Formulas
RangeFormula
I3:M12I3=IF(N(H3)>0,$C$2,((I$2>=$F3)*MAX(1/2,$H3=""))*$C$2)
I13:M13I13=SUM(I3:I12)
C5:D9C5=C$2*(2*(ROW($B5)-ROW($B$4))-IF(N($B4)=0,0,1))+(C$2/2*IF(N($B4)=0,0,1))
 
Upvote 0
Model inputs are in Bold Red

MrE20210123-1.xlsx
ABCDEFGHIJKLMNOPQRSTU
2Sales/yr$ 50,000
3CtC/yr$ 30,000
4
5
61234567891011121st Hire 2nd HireTotal SalesCumulativeCtCCumulative
7Starting #JanFebMarAprMayJunJulAugSepOctNovDecMonthMonthfor yearSalesfor yearCtC
82021011111122222217$ 75,000$ 75,000$ 45,000$ 45,000
92022223333334444428$ 166,667$ 241,667$ 100,000$ 145,000
102023444555666666636$ 270,833$ 512,500$ 162,500$ 307,500
112024667777888888826$ 375,000$ 887,500$ 225,000$ 532,500
1220258999999991010101019$ 466,667$ 1,354,167$ 280,000$ 812,500
Sheet3
Cell Formulas
RangeFormula
C8:N12C8=B8+($P8=C$6)+($Q8=C$6)
R8:R12R8=SUMPRODUCT(C8:N8*$B$2/12)
S8:S12S8=SUM($R$8:R8)
T8:T12T8=SUMPRODUCT(C8:N8*$B$3/12)
U8:U12U8=SUM($T$8:T8)
B9:B12B9=N8
 
Upvote 0
Solution
H
Model inputs are in Bold Red

MrE20210123-1.xlsx
ABCDEFGHIJKLMNOPQRSTU
2Sales/yr$ 50,000
3CtC/yr$ 30,000
4
5
61234567891011121st Hire 2nd HireTotal SalesCumulativeCtCCumulative
7Starting #JanFebMarAprMayJunJulAugSepOctNovDecMonthMonthfor yearSalesfor yearCtC
82021011111122222217$ 75,000$ 75,000$ 45,000$ 45,000
92022223333334444428$ 166,667$ 241,667$ 100,000$ 145,000
102023444555666666636$ 270,833$ 512,500$ 162,500$ 307,500
112024667777888888826$ 375,000$ 887,500$ 225,000$ 532,500
1220258999999991010101019$ 466,667$ 1,354,167$ 280,000$ 812,500
Sheet3
Cell Formulas
RangeFormula
C8:N12C8=B8+($P8=C$6)+($Q8=C$6)
R8:R12R8=SUMPRODUCT(C8:N8*$B$2/12)
S8:S12S8=SUM($R$8:R8)
T8:T12T8=SUMPRODUCT(C8:N8*$B$3/12)
U8:U12U8=SUM($T$8:T8)
B9:B12B9=N8
Hi

Thank you for this, its perfect, just what I needed.
An addition in a separate spread sheet is to know which month the 1st and 2nd hire took place. As the assumption is when I hire another employee it is because I have acquired another client and this results in a once off admin fee $2000. So for that specific month I need to show the extra income/sales.


As per you example above. Info range P8:Q12. The value represent a month. (3=March, 5=May) The answer must then appear in the corresponding cell for that month in that year.
eg 2023. 1st hire March (3) = $2000 in column E row 13/14? 2nd hire June (6) = $2000 in column H row 13/14.

Jan Feb March April May June July August Sep Oct Nov Dec
2021 $2000 $2000
2022 $2000 $2000
2023 $2000 $2000

Vlookup wont work?
What function do you use for the answer to appear in a possibility of cells within a column range C - N. (Jan-Dec)

Thank you for your help.
 
Upvote 0
Upvote 0
Something like this:

Cell Formulas
RangeFormula
B5:M9B5=((B$3=Forecast!$P8)+(B$3=Forecast!$Q8))*$C$1
That was quick. I see my example shifted the info under each other, sorry but you understood my query. I will have a look at your solution thank you.
 
Upvote 0
That was quick. I see my example shifted the info under each other, sorry but you understood my query. I will have a look at your solution thank you.
Something like this:

Cell Formulas
RangeFormula
B5:M9B5=((B$3=Forecast!$P8)+(B$3=Forecast!$Q8))*$C$1

Hi

I could not get the forecast function to work.

I used IF/OR
=--(IF(OR(C$6=$P$8,C$6=$Q$8),$B$3))

Thanks for your help
 
Upvote 0
Forecast is the name of the worksheet I was referencing since you asked how to do the calc from a different worksheet. So Forecast!$P8 is referencing cell P8 on the sheet called Forecast. Change it to the name of the sheet where the original data table is located (whatever you named it, or if you didn't name it it might just be Sheet1). If you're doing the calc on the same sheet as the original data table then you don't need the worksheet identifier.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top