Roster Coster in Excel

This is a staged project – we will add functionality to the worksheets after each part starts working correctly. Below are the requirements for Stage 1 PDF of the excel sheets is attached. The old spreadsheets do not have any cleaver functionality. We need to remove errors, improve the accuracy of the calculations and make the spreadsheets easy to use. The Roster/ Coster is prepared for each fortnight usually on the Friday/Saturday before the start of the period. Business Process: • After the first fortnights spreadsheet has been set up it will be used as the base for the next period • As the staff stay constant the user will copy the previous periods Roster / Coster and use that as the base for the next period. • The Start Date of the fortnight is updated • Any gazetted public holiday dates are added – where ever these dates are in the roster and any other reference, the cell must be highlighted yellow – to show that this date is a public holiday • If there are new starters or workers new to the site the manager will add them to the Data Setup worksheet – the cells on the roster for days, will be blank • Terminated workers will be deleted from the list any reference to them from existing sheets will be removed. • If any information about a worker is updated on the Data Setup table, all references throughout the workbook are automatically updated • Projected Occupancy for the period is updated first – this information guides rostering requirements • When a name is added to the roster the following information is populated o Name o Department o Position o Type o Category o Roster • Rows can be inserted into the roster or can be deleted • Salary workers information is added next – this is fixed cost • Then the waged workers • Need to be able to add RDO / AL/ PL / LS in the start date field along with a time • Worked Hours and Costs are calculated based on rules & information about the individual like their salary category, roster, hours worked minus breaks etc • Each days salary impact on roster is automatically calculated on the Occupancy Projection Calculator – and the remaining amount of budget for wages staff will be calculated • The days header will be highlight red if the allocated roster exceeds budgeted hours and costs • All of the calculated fields on the Roster MUST NOT be visible when it is printed (once it is working we will then hide them but keep them visible until the calculations are right) Rules for hours and costs Calculating hours If shift is less than 5 hours apply no break If shift is 5 hours < 8.5 hours, remove 30 mins for break If shift is 8.5 hours < 13 hours, remove 1 hour for breaks If shift is greater than 13 hours remove 1.5 hours for breaks Cost of hours Wages Employee • Question – do we have to cover costs for RDO for wages staff? If the answer is yes, what rate? Is the date a public holidays, if NO then If total hours < 10 hours then hours x rates Else total hours > 10 hrs then overtime (total hours – 10 hours) if overtime < 2 hours (10 hours x rates + (overtime hours x rates x 150%)) if overtime hours >2 hours (10 hours x rates + 2 hours x 150% + (overtime hours – 2) x 200%) If Yes then, If total hours < 10 hours then hours x (rates x 250%) Else total hours > 10 hrs then overtime (total hours – 10 hours) if overtime < 2 hours (10 hours x rates + (overtime hours x rates x 150%)) if overtime hours >2 hours (10 hours x rates + 2 hours x 150% + (overtime hours – 2) x 200%) If Salary Employee If they are on AL / PL / LS then cost is $0 If they are on an RDO or shift then day rate (for type of roster works on i.e. 5/2 or 14/7)