I am a Sessional Teacher at a Institute which offers a range of Financial Services qualifications. During my time with the Institute, I have created and have been "tweaking" a Financial Modelling spreadsheet for use in one of our key subjects: Principles of Statement of Advice Construction. (Might be useful to mention at this point that I am originally self-taught in Excel; the historical lesson here being don't put your hand-up when someone asks for a volunteer!)
One of the teachers has expressed concern that some students have been copying the workings from other students and submitting this as "their own work". The suggestion put to me was that I add the student's "Date of Birth" in the spreadsheet and use this date as a unique identifier to a specific cell(s) in the spreadsheet.
I hope the following is an adequate description of what I am seeking to do:
Based on a students' birth date, subtract this from the future "Year" in the spreadsheet. The starting date for the financial modelling begins each current "Calendar Year", although this appears in the spreadsheet as [Year] 2013/14, [Year] 2014/15, etc. Notwithstanding the current year issue, the resulting days(?)/weekdays(?) would then be used to calculate the final months of "Salary" in the Case Study to add to "Superannuation Fund Balance after Tax" in the final year of the strategy.
My problem is that our students range from "fresh out of Uni/College" to "established Professionals". Thus, ages [and birth dates] can be from 18 to 65!
So...
I'm trying to work out how I can incorporate a "bucket-load" of variable "dates/months/years" that tie-in with the appropriate Calendar Year and also identify the number of days(?)/weekdays(?) to then use for the unique identifier calculation the remaining Financial Year. (Even "My" head hurts trying to make sense of that sentence!)
The attached spreadsheet in question may to assist with the context of what I am attempting to explain. (In the spreadsheet, the final calculation is shaded "Green", i.e. age 60.) Cells B91:F94 are just "bits" I started to play with before I realized that I may have swum too far out of my depth. Also, the "*5" in Cell C84 is just there "to test" an outcome. These are the two cells that apply to the required calculation.
Perhaps I'm creating an unnecessary "monster" when there may be a more elegant method or change to the spreadsheet to achieve the desired outcome.
Hoping for a solution,
Preceptor
One of the teachers has expressed concern that some students have been copying the workings from other students and submitting this as "their own work". The suggestion put to me was that I add the student's "Date of Birth" in the spreadsheet and use this date as a unique identifier to a specific cell(s) in the spreadsheet.
I hope the following is an adequate description of what I am seeking to do:
Based on a students' birth date, subtract this from the future "Year" in the spreadsheet. The starting date for the financial modelling begins each current "Calendar Year", although this appears in the spreadsheet as [Year] 2013/14, [Year] 2014/15, etc. Notwithstanding the current year issue, the resulting days(?)/weekdays(?) would then be used to calculate the final months of "Salary" in the Case Study to add to "Superannuation Fund Balance after Tax" in the final year of the strategy.
My problem is that our students range from "fresh out of Uni/College" to "established Professionals". Thus, ages [and birth dates] can be from 18 to 65!
So...
I'm trying to work out how I can incorporate a "bucket-load" of variable "dates/months/years" that tie-in with the appropriate Calendar Year and also identify the number of days(?)/weekdays(?) to then use for the unique identifier calculation the remaining Financial Year. (Even "My" head hurts trying to make sense of that sentence!)
The attached spreadsheet in question may to assist with the context of what I am attempting to explain. (In the spreadsheet, the final calculation is shaded "Green", i.e. age 60.) Cells B91:F94 are just "bits" I started to play with before I realized that I may have swum too far out of my depth. Also, the "*5" in Cell C84 is just there "to test" an outcome. These are the two cells that apply to the required calculation.
Perhaps I'm creating an unnecessary "monster" when there may be a more elegant method or change to the spreadsheet to achieve the desired outcome.
Hoping for a solution,
Preceptor