Line Utilization Log User Manual
Contents
1. the people involved a 2 2 Line Utilization Log User Manual Line Utilization Log User Manual Introduction The Line Utilization Log xls is a macro driven spreadsheet that will calculate the statistics of line utilization in any given month This User Manual gives instructions on how to use the spreadsheet to obtain the required reporting functionality Getting Started The spreadsheet is macro driven However Excel sees running macros as a potential threat to the integrity of the system and would rather macros were disabled i e not allowed to run You have two choices 1 Permanently enable macros To do this in Excel go to the Tools menu select Macros and from the sub menu select Security On the Security Level tab select the Low option and press OK Security Trusted Publishers Very High Only macros installed in trusted locations will be allowed to run All other signed and unsigned macros are disabled High Only signed macros from trusted sources will be allowed to run Unsigned macros are automatically disabled Medium You can choose whether or not to run potentially unsafe macros LS Low not recommended You are not protected from potentially unsafe macros Use this setting only if you have virus scanning software installed or you have checked the safety of all documents you open Virus scanner s installed 2 Enable macros on a session by2. Data window Help A POF Type a question For help F X L LA LC gt 01234 567290 01234 567391 bh J CU 01234 567092 01234 567593 f Ei a an N 4 K KNX Template May 2010 Ready Page 8 of 14 Line Utilization Log User Manual Calculate Report Button Clicking the Calculate Report button protects the work sheet Then a small program will run and it will total the number of minutes available for that month and then ask you for information about whether the lines were unavailable for any period of time during the month Lost Minutes How many minutes were lost in this month due to line Ok Faults equipment Failure maintenance etc Lok Cancel E If there were no periods of line unavailability leave as zero otherwise enter the number of minutes the lines were unavailable You will not be asked for this information a second time Don t forget to calculate the number on line minutes that the lines were unavailable For example if bank 1 has 6 lines and bank 1 is faulty for 60 minutes then the lines minutes of unavailability is 6 x 60 360 line minutes so enter 360 and click on OK Lost Minutes How many minutes were lost in this month due to line Faults equipment Failure maintenance etc Cancel 360 Similarly if you have 16 lines and the whole system is down for 60 minutes then line minutes not available that month is 16 x 60 960 line minutes so enter 360 a
3. new report Clicking on OK cancels the Add New Report operation and returns you to the Template tab You will receive confirmation that the operation was cancelled Clicking on the Cancel button cancels the cancelling operation and leaves you with the Select New Report Name box open With the Select New Report Name box open insert a Month and Year into the two boxes and click on OK This will create a new report and place it directly behind the Template sheet Over time this has the effect of pushing older reports further to the right You have now created a new Line Utilization Log Report to track all faults notified in the specified month Using the New Report The new report looks like Page 6 of 14 Line Utilization Log User Manual Ej Microsoft Excel Line Utilization Log Blank xls Seles Adobe PDF Type a question for help f X el 4 HH serean pf T E E 10 4 4 K FH Template May 2010 Ready You can now populate the Minutes Usage column with data in preparation for calculating the report analysis This information will be available as a report from your calls handling software Simply enter the number of minutes each line was used If a line was not used leave that entry blank or enter 0 zero Page 7 of 14 Line Utilization Log User Manual Ed Microsoft Excel Line Utilization Log Blank xls Sele e L EE E l ew Insert Formal Toos
4. session basis To do this leave the security setting at Medium and you will be presented with a warning screen when you open the Line Utilization Log file Then just select Enable Macros in the warning message security Warning U Template Manuals Telecare PAPER SYSTEM Draft Forms Fault Log xls contains macros Macros may contain viruses Itis usually safe to disable macros but if the Macros are legitimate you might lose some functionality Page 2 of 14 Line Utilization Log User Manual Note Do not select the High or Very High security settings for the macros In your version of Excel as that would disable the macros and the functionality that you require in the Line Utilization Log would not be available When you first open the spreadsheet you will be asked if you want to show the spreadsheet in full screen mode If your screen resolution is reasonably high it will look better in reduced screen mode so click No Otherwise click Yes Full Screen Mode Do you want to open this spreadsheet in Full screen mode When the spreadsheet opens it will look like this Ej Microsoft Excel Line Utilization Log Blank xls icles File Edit View Insert Format Tools Data Window Help Adobe PDF Type a question For help a n A ENV ECO Cetails e No To Dial Eime R Ka Minutes Usage Usage within Bank Dn Usage Overall a 01234 567890 1B 2 01234 567891 01234 567892 0
5. 1234 567093 Page 3 of 14 Line Utilization Log User Manual You cannot enter any information in the various cells as they are protected All you can do Is click on the button marked Add New Report or Edit Details Editing Details Clicking the Edit Details button takes the protection off the first three columns on the spreadsheet so that you can change the number of lines in a bank the number to call or the bank number You will be advised of this with an information box Edit Details Edit the first three columns on the spreadsheet as you will but try to keep the same format as the image below The only thing to remember to get right is that there must be two blank lines and only two between the banks and no blank lines between the lines within a bank If this is not correct the spreadsheet will not calculate the line utilization statistics correctly Bank Ho Ho To Dial p B 1 anaa sman Line No within Bank 01234 567890 et 2 R B ee a O a o ooo o S s 191 OO ee SS 01234 567691 ss as LO o 4 01234 567592 BI PJ Lo r tu 01234 567693 bi bi PI bo LO 00 o 101 When the details are correct click on the Lock Details button and the spreadsheet is Page 4 of 14 Line Utilization Log User Manual locked again You will be informed of this by an information box Edit Details i The details are now locked This information will be used to create all new r
6. Galloway of External Quality Management Ltd Southampton S019 4D England T 023 8044 0866 Please feel free to share this soreadsheet with friends and colleagues However please also ensure that a copy of this User Manual always accompanies the associated spreadsheet Copyright External Quality Management Ltd Page 14 of 14
7. ed to calculate Page 13 of 14 Line Utilization Log User Manual minutes of line availability for that month As the Lost Minutes changes from month to month and different months have different numbers of days in them this figure changes from month to month However once established it is not required to be calculated again for that month The coding then counts the number on Banks by examining the Bank No column for populated cells and the numbers of lines within each bank by counting rows until it finds a blank cell It then located the next bank two lines down from the end of the previous bank This is why it is important to have no blank lines within a bank and two and only two blank lines between each bank The coding the calculates the Bank Totals and enters a legend of Bank X Totals and then the total minutes for that bank They are entered in the report and formatted to stand out better These cells are then locked This information is cleared and the cells reformatted and left unlocked prior to the calculation being carried out subsequently The coding then runs down each row in turn calculating the two Usage figures and entering them into the correct columns If any of the figures are equal or greater than 50 the Conditional Formatting changes the font colour and pattern colour to highlight this to the user Copyright Notice This User Manual and the spreadsheet to which it refers is the work of Andy
8. ensuring the traffic is shared more evenly between the existing lines and banks Normally this sort of statistic would show up if there are low numbers of lines handling very high volumes of calls In this case additional lines would alleviate the problem Be aware that this may also have an effect on staffing levels as more lines without more staff to handle the calls will result in call response times dropping off The report should be printed off preferably in colour Any items showing up in solid red are to be investigated and the reasons for the shortfall should be explained in writing to the Satisfaction of the Control Room Manager You may have a separate form for this purpose called an Exceptions Report The explanation should also indicate the actions needed to prevent a recurrence of this shortfall in the future These recommendations may be by action plan corrective actions list of things to do written into the explanation document Page 11 of 14 Line Utilization Log User Manual exception report or appended to the Line Utilization Log by hand The key thing is that correct control is exercised to ensure that the rectification actions are carried out and verified later as having been effective in closing down the original problem This explanation should be attached to the printed copy of the report and filed with it The explanation should be signed and dated by the Control Room Manager to show that it has been reviewed sa
9. eports from now on Reports created with the old information will remain Creating a New Report Clicking on the button Add New Report will allow you to create a similar sheet where you can enter all of the Line Utilization Log information Clicking this button also protects the worksheet and the workbook You will be asked whether you actually want to create a report Create New Report P You are about to create a new report ready for inputting data Is this what you want to do Clicking No cancels the operation in case the button was pressed in error Clicking Yes takes you to a screen where you can denote the name of the tab for this report It Is envisaged that a new report will be started each month so the tabs will be named by month and year The system will always select the month and year of the month following the month in which you are carrying out this task but this can be changed from the drop down boxes It is important to get this right for all the obvious reasons but also because the information input here will be used to calculate the Line Utilization Statistics select New Report Name Eg Select the month and year for the new report from the drop down lists Page 5 of 14 Line Utilization Log User Manual Clicking on Cancel will cause a prompt to ask whether you really meant to cancel the operation Are you sure 2 Are you sure you want to cancel this operation without creating a
10. heet will take longer and longer to open At that point simply rename the existing live file to include Archive 01 in the file name or dates such as 2010 to 2012 Then create a another copy of the Line Utilization Log Blank xls and rename it to Line Utilization Log Live xls Then follow the routines above on the new live version of the spreadsheet PROTECT ME This spreadsheet needs to be protected in order for the automated functionality to work correctly However as it is your spreadsheet you may have a need to change the spreadsheet from time to time To access this spreadsheet s protected areas unprotect the worksheets using the password 12345qwertasdfgzxcvb When finished with your changes go to the Template page and press the Add New Report button This applies all of the protection to the worksheet and the workbook When asked if you want to create a new report click on the No button and then save the worksheet to save your changes HOW IT S DONE There are no formula in the spreadsheet at all all calculations are carried out by the coding However the rose colour and the red colour in the Usage columns is created with conditional formatting When the Calculate Report button is clicked the cell containing or that will contain the line availability minutes is examined If it is blank or 0 zero the user is asked for Lost Minutes This figure along with the month and year of the tab name Is us
11. nd click on OK Lost Minutes How many minutes were lost in this month due to line Ok Faults equipment Failure maintenance etc _ ok Cancel ES The Report is calculated and would look something like Page 9 of 14 Line Utilization Log User Manual Ej Microsoft Excel Line Utilization Log Blank xls lt Eg 4 ST File Edit view Insert Format Tools Data Window Help Adobe PDF Type a question for help x E T T H Ss a HT a a E Number of minutes of line availability for this month is 713 260 Not including 960 minutes of lost line availability during the month Calculate Tass Report Ho To Dial nne Se Minutes Usage Usage within Bank bn Usage Overall Bank Ho ps 01234 567890 1 234 20 26 rt ae 17 30 51 0 33 5 32 00s ogr 0 06 12 56 0 11 14 30 LO 0 00 0 00 24 07 0 01 2 22 0 03 aA Bank 2 Total o e y aooo TT z a maass a o wa ooo ajo o es 30 Bank S Total 23A O ao EE e S o eee a nasa tm 31 08 0 09 47 5 0 14 21 57 0 06 Et O O y Bank 4 Total 2 114 T EN L 7 4 RN Template ag 2010 8 Ready Notice that the minutes of lost line availability are shown If there were 0 zero lost minutes that month this notification would be missing If Usage within Bank figures are 50 or greater will show up as a light red background This is not noncompliant it is merely highlighted for you to look at weaknesses within the traffic dist
12. ribution system that may cause more problems than is necessary under certain fault conditions For banks with low numbers of lines See Bank 3 in the example above high percentage figures are almost certain If the Usage Overall figures are 50 or greater this is noncompliant with the TSA Code of Practice and will show up as a solid red background such as in this example for Bank 1 Line 2 Page 10 of 14 Line Utilization Log User Manual Ej Microsoft Excel Line Utilization Log Blank xls ea File Edit View Insert Format Tools Data Window Help Adobe PDF Type a question For help Y fe BRUUUDU a Number of minutes of line availability for this month is 713 260 Not including 960 minutes of lost line availability during the month aeuasrenat Ho To Dial nne e Minutes Usage Usage within Bank bn Usage Overall paseo O o ee 7 a 99 26 70 10 a 0 06 0 05 O 0 08 oo 0 41 0 12 ee eT massere Bank 1 Total R 745 St 1 70 0 00 24 07 2 22 01234 567391 S PE Bank2Total 324 T an 100 00 0 00 0 00 31 08 1 001 4 7 30 21 57 bh Lu A 01234 567593 H el KA Fa ie e Kgl a gt 24 ES 26 E El E 30 a a Bank 4 Total 2114 ka ea 4 K FH Template May 2010 i Ready This indicates a severe potential problem caused by a lack of resources or incorrect management of the existing resources In the above example it would simply be a case of
13. tisfactorily Then it should be filed in a folder or drawer that is specifically for these reports This makes the records easily retrievable for audit and review purposes LINE UTILIZATION LOG a ae S60 minutes of lost lire av a dun a ans _ a mnr Dz 0 Oo o 2 SHD 25 28 70 10 Ce Bank 1 Total 503 745 5 amai R AR RH HR Fom 35 Page 1 of 1 Issue 1 Page 12 of 14 Line Utilization Log User Manual IMPORTANT NOTE The formulae in this spreadsheet are dependent upon the Analysis Toolpaks being enabled in Microsoft Excel 2003 Go to the Tools menu select Add Ins select the Analysis Toolpak and the Analysis Toolpak for VBA then press OK Do not save the work sheet until after this has been done Other versions of Microsoft Excel may already have this feature enabled but probably not OTHER NOTES WHY THE NEED FOR THIS REPORT The original reports created from the calls handling software are either to complex not clear enough or just plain wrong There was a need to have this information presented clearly and simply and to just tell the user whether the statistics are compliant or not Hence this report was produced A GOOD ROUTINE It is recommended that you create a copy of the Line Utilization Log Blank xls and rename it to Line Utilization Log Live xls Then follow the routines below on the live version of the spreadsheet After a period of time when there are thousands of entries in it the spreads
Download Pdf Manuals
Related Search