Excel variable phasing methods and payrises for salary budget

Question

I have been asked to phase the budget salaries by month and to include the timing of up to 2 payrises and 1 promotion for each employee in the phasing. The information is below and includes the calculation for 1/12th per month, in a July to June year. The column letter is in the first row. The payrise is effective from the first day of the month. The data line is an example only. All of the data changes depending on the employee.

N	Q	T	V	AT	AU	AW	AX	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN
Base Hourly Rate	New Hourly Rate	New Hrly rate Date Change	Contract Hours Per F/N	Expected Payrise %	Effective Date Expected Payrise	Second Expected Payrise %	Second Effective Date Expected Payris	Fortnights / Year	Period 1 Total	Period 2 Total	Period 3 Total	Period 4 Total	Period 5 Total	Period 6 Total	Period 7 Total	Period 8 Total	Period 9 Total	Period 10 Total	Period 11 Total	Period 12 Total	Total
20.00	22.00	April	76.00	3%	October	3%	1/01/2014	26	3,744	3,744	3,744	3,856	3,856	3,856	3,933	3,933	3,933	4,327	4,327	4,327	47,583

The phasing methods are to now also include working days, calendar days and calendar days + public holidays and there will be other phasing methods to be added.

	Period 1 Total	Period 2 Total	Period 3 Total	Period 4 Total	Period 5 Total	Period 6 Total	Period 7 Total	Period 8 Total	Period 9 Total	Period 10 Total	Period 11 Total	Period 12 Total	Total
1/12th	1	1	1	1	1	1	1	1	1	1	1	1	12
Working Days	23	22	21	23	21	22	23	20	21	22	22	20	260
Calendar days	31	31	30	31	30	31	31	28	31	30	31	30	365
Calendar+PH	31	31	30	32	30	33	32	28	33	31	31	31	373

I need to use the total produced for the 1/12th calculation but phase it differently if required. and it needs to be done by formulae not macro.

This is too hard for me to include the timing of the payrises and promotion. Is it possible to do this or is it beyond the scope of Excel?

Any help very much appreciated.

Ando

Answer 1

I don’t think this can be possible using formulae. A little complex.

You will have to use macros.