User Manual - State of New Jersey
Contents
1. 23 N J Division of Mental Health Services Excel Budget Matrix Users Manual DEFINITIONS The following definitions may prove helpful for newer users as you seek to become proficient in the use of the DMHS Excel Budget Matrix Excel Budget R O E Matrix A spreadsheet application of the Microsoft Excel software program designed and supported by DMHS which allows users to store analyze and print budget and expenditure data in an automated format Cell The intersection of a row and a column the unit of a worksheet in which data is stored Each cell has a unique address indicated by a letter column and number row Column Any vertical block of cells in the worksheet similar to a database field In the Matrix application a column generally refers to a Program or Cost Center Command An instruction that you give to the computer program Within Lotus 1 2 3 the or forward slash activates the main menu of commands The Excel Matrix application s main menu is activated by clicking the mouse on the Matrix choice in the Excel Command Menu at the top of the screen Additional command choices are available in the drop down menu choices under the Matrix command Default Setting A setting or command choice that the program automatically assigns unless you specify another setting File A named collection of2. are to be inserted If you want to add more than one row click and hold the mouse button while painting several rows then releas th button with the rows selected Then invoke the Format Spreadsheet and Insert One or More Rows commands The Matrix program macro will add the selected rows to all four worksheets The Delete One or More Rows command works in the same way just consider placement of the cursor in the Budget sheet first before invoking the commands Also if you try to add or delete rows involving protected areas of the worksheet the command macro will indicate that you will need to make another choice Select unused areas stay away from pre set rows in each section of the worksheet 10 N J Division of Mental Health Services Excel Budget Matrix Users Manual Edit Program Element Info This command choice is used to give proper identification to the various program columns within your budget When presented with a blank Excel Budget Matrix file you will invoke this command to add identifying information to at least one DMHS and perhaps one non DMHS program column and this information will be recorded in rows 2 8 at the top of the column in each of the four worksheets Before clicking on this command choice position the cursor near the top of the spreadsheet in row 8 of the column that you are attempting to edit If the cursor is not properly located the macro will not operate until
3. e A third choice for allocation of General amp Administrative expenses G amp A has been added In addition to the Percent of Total Direct Expenses and Percent of Direct Salaries methods there is the Agency Plan choice in the menu system Unlike the first two choices which have pre set formulas and are protected cells the Agency Plan selection is unprotected and requires the user to enter data in each program column DMHS developed the Agency Plan choice to accommodate those users which have approved indirect cost plans or which allocate G amp A expenses according to a non standard methodology The user would provide a supplementary schedule for the approved allocation of G amp A expenses and then enter the program specific numbers or formulas from the schedule onto the Budget Matrix e Most importantly Dr Spiessl has created an upgrade utility that will allow DMHS staff to convert all existing version 1 3 Budget Matrix files to version 2 0 without any loss of data or additional data entry N J Division of Mental Health Services Excel Budget Matrix Users Manual MENU amp COMMAND STRUCTURE Below is a menu tree diagram listing the Excel Budget Matrix macro commands that have been created for your use All commands are invoked from the Matrix menu choice in the Excel Menu Bar at the top of the screen when the Budget Matrix file is open
4. 2 Save File using standard DMHS file naming scheme Be Print ROE must have correct sheet on screen 4 Calculate ROE change from budget De Print must Variance Report s have correct sheet on screen 21 COMMANDS keyboard commands Excel command Matrix Print macro Variance macro 3 Matrix Print macro N J Division of Mental Health Services Excel Budget Matrix Users Manual PROGRAM CODES amp NAMES With the implementation of the Budget Matrix DMHS introduced a standardized coding procedure for all budget programs or cost centers columns in your spreadsheet These codes are necessary for the organization and retrieval of all data submitted to DMHS the DMHS Contracts Database utilizes program codes to correctly identify data by program which in turn permits the issuance of management reports with program specific detail The program names for a Provider Agency s service programs will be entered automatically in row 5 of the Matrix worksheet in the applicable columns The names should correspond to the DMHS Program Elements listed below Local names which have meaning for Agency staff only can be added by the user and will appear in row 4 The length of the program names may need to be further adjusted depending on the size of the spreadsheet to be printed and user preferences Right justified program names will correspond more closely to th
5. e Matrix Clear Worksheet Copy Budget to Mod Worksheet Copy Mod to Budget Worksheet Add Alter Agency Contract Info e Agency Name e Contract Number e Contract Start Date e Contract End Date Edit Program Element Info e Select Program Code e Program Name e County of Service e Client Age Group Format Spreadsheet e Add DMHS Program e Add Non DMHS Program e Remove Program Column e Insert Rows e Delete Rows e Hide Columns e Unhide Columns e Color Worksheets and Tabs e White Worksheets and Tabs Variance Reports e Mod Change from Budget e Mod Change from Budget e Dollars Remaining per ROE e Interim ROE Percent Expended e Final ROE Change from Budget Print Spreadsheet e Select Worksheet to Print e Paper Type e Preview and customize or Cancel G amp A Allocation Method e Total Cost method e Salary method e Agency G amp A Plan N J Division of Mental Health Services Excel Budget Matrix Users Manual OVERVIEW OF MATRIX COMMANDS MACROS Add Alter Agency Contract Info When using the Budget Matrix for the first time this is the first command you should use after opening the Excel Budget Matrix file Users need to identify their agency specific information and record it on the Matrix file Invoking this command with your mouse will open a dialog box and you will be prompted to enter the Provider Agency Name DMHS Contract Number 5 digits and the Start Date and End Date for the Contract Period
6. the DMHS Contract Administration Unit are responsible for the development maintenance distribution and training associated with the Budget Matrix Copies of the Budget Matrix program will be made available at no cost to users within the mental health and larger human services community and other responsible persons 2002 2006 DMHS staff request that no modifications are to be made to the software if utilized for the N J DMHS contracting system and that proper identification and acknowledgements re DMHS are made if the software is shared with others The staff at DMHS welcome your comments and suggestions regarding the Budget Matrix program N J Division of Mental Health Services Excel Budget Matrix Users Manual WHAT S NEW IN THE EXCEL BUDGET MATRIX The Excel Budget Matrix Version 2 0 operates much the same as Version 1 3 introduced in 2003 The basic functionality is the same the menu system is the same and the look and feel between versions 1 3 and 2 0 is the same Some notable differences e The Excel Budget Matrix now has multiple pre set Subtotal columns to accommodate users with multiple human services funding sources and the need to group funded programs together and provide a subtotal for each As a result the Budget Matrix is more user friendly for the human services community with Subtotals already established for DYFS DCBHS DAS and DDD with two additional gr
7. Print Spreadsheet consider the size of the spreadsheet and the intended use of the printout Do you want to s th ntire worksheet or do you want to hide one or more columns If you need to hide columns place the cursor in the column to be hidden or paint several contiguous columns then invoke the Matrix Format Spreadsheet Hide Columns command Invoke the Matrix Print Spreadsheet command which leads to a dialog box with several choices The user must choose the appropriate Spreadsheet to Print Budget Mod ROE or Variance and Paper Type legal or letter Then click on the Print Preview box in order to review the potential print job more closely and perhaps tweak the print setting somewhat to obtain a better result This choice will provide a view of the print job and plac th user within the Excel page preview commands Clicking on other tabs such as Setup and then Page and Margins will nabl th user to size the spreadsheet more accurately to the paper prior to printing Use the various Excel menu choices to properly format your Budget or ROE to the size paper you are using and check the results with Print Preview before clicking on Print At this stage there is no return to the Matrix Print Spreadsheet dialog box the user must either proceed to print or cancel using the normal Excel commands that are presented Also one further caution if the user
8. When complete click on Enter and your choices will be recorded in rows 2 amp 3 at the top of your spreadsheet on the left and right sides and in all four Matrix worksheets Should any of this information change during the contract period the user will need to invoke this command again and re enter only the item s that are new If the contract term is completed and you are preparing a budget for the new period then you will need to invoke the command and change the Start Date and End Date No direct data entry in rows 2 amp 3 are permitted this area is protected so all contract identification information is to be accomplished through the use of this command N J Division of Mental Health Services Excel Budget Matrix Users Manual Format Spreadsheet All new users will need to modify the blank Budget Matrix file to the particular size and shape of their own agency s DMHS contract budget Before invoking this command consider how many DMHS program columns and how many non DMHS program columns appear on your contract budget Note Program budgets for other DHS Divisions should be included as well as any non DMHS program which affects the allocation of General amp Administrative costs Consult with your DMHS Contract Administrator if unclear on this point Also consider how many rows budget line items your contract wi
9. clicks on the Page Break Preview tab within this menu screen the only exit from this area is to click on the View menu choice top of the screen then click on Normal 16 N J Division of Mental Health Services Excel Budget Matrix Users Manual G amp A Allocation Method General amp Administrative G amp A expenses are those which are common or shared by all components of the organization and cannot be easily allocated to one or more programs as direct xpenses It is always preferable to directly identify expenses by program area to the extent possible rather than including them in the G amp A cost center There are two methods EOL the allocation of General amp Administrative expenses that are accepted as reasonable and appropriate for contracts with the N J Department of Human Services th percentag of direct total cost method and the percentage of direct salaries method refer to DHS Contract Reimbursement Manual sections 4 4 and 5 3 If your organization is allocating G amp A expenses on a basis different than these two methods please consult with your DMHS contract administrator Most DMHS contractors use the percentage of direct total cost method Therefore the Excel Budget Matrix has pre set formulas in the worksheets that will allocate G amp A expenses by percentage of direct total cost This is th
10. provided as distinct sheets in a separate Excel file for your use They are essentially templates for entering storing printing or submitting the required data that supports the Budget or ROE amounts There are no linked cells or formulas that relate the schedules data to the budget matrix data the user must enter the relevant total contract expense data from the schedules onto the Excel Matrix file manually DMHS is also making other fregquently used forms and documents available to provider agencies in Microsoft Excel or Microsoft Word files Examples include the Contract Modification signature page the Contract Modification dollar change summary pages 2 grid pages for before after analysis and the ROE Quarterly Summary amp Signature page Contact your assigned DMHS Contract Administrator for electronic copies of these files OTHER USER ISSUES POINTS TO REMEMBER Do not attempt to disable the file workbook protection that exists in certain areas this is necessary to ensure the integrity of the data presented Always seek assistance from an experienced user or DMHS Contract Administrator to overcom perceived problems Do not use the Excel cut and paste menu commands when working with an Excel Matrix file The results can wreak havoc with your file since cutting amp pasting will transfer cell references to the new location The data in comparative analyses a
11. to eliminate data for amounts llar amounts s applicable and line item descriptions enter new dollar amounts to Mod sheet Save File file naming scheme Print must ct ct Modification have correct sheet on screen using standard to Modification from Budget sheet DMHS Calculate Modification change Print must Variance Report s have correct sheet on screen When DMHS has approved the Modification EIs Copy Modification data to Budget sheet similar to the old Adjust macro Current approved budge In Budget t sheet t must always be Erase dat Save File ta in Modificat file naming scheme using standard tion sheet DMHS 20 Excel Budget Matrix Users Manual COMMANDS Add Alter Info macro Format Spreadsheet macro Edit Program Info macro Copy Budget to Mod macro that have not changed keyboard commands Format Spreadsheet macro keyboard commands Excel command Matrix Print macro Variance macro Matrix Print macro Copy Mod to Budget macro Clear Worksheet macro Excel command N J Division of Mental Health Services Excel Budget Matrix Users Manual Reports of Expenditure Income Quarterly amp Final ROEs PROCEDURE 1 Enter ROE data from agency records directly onto blank ROE worksheet note ROE data is cumulative for the contract period
12. to ROE data is always the approved budget and this data must always be in the Budget sheet Invoking this command will present the user with a reminder that all data currently in the Budget sheet will be overwritten and the user must click Yes to continue or No to exit After clicking Yes there will still be data in the Mod sheet so the user must also invoke the Clear Worksheet command from within the Mod sheet to erase the data and set up the file for future Modifications Don t forget to save the file again after making these changes 13 N J Division of Mental Health Services Excel Budget Matrix Users Manual Clear Worksheet This command is simple and straightforward it erases all numbers from the worksheet that is currently on the user s screen TG does not however eliminate any of the line item descriptions that appear in columns A B and C of all four worksheets To use this command click on the tab for the worksheet that contains the numbers to be erased The cursor can be positioned anywhere within the worksheet Invoking the command presents the user with a message You are about to clear the contents of the Budget Mod ROE Variance worksheet continue Click Yes to complete the command or No to exit The Clear Worksheet command is useful at times to erase numbers that are no longer relevant such as Modificatio
13. you get it right You will then be prompted to enter data in a dialog box Select Program Code Program Name Optional Select County of Service and Select Age Group for Clients The Program Code choices are set by DMHS and you can make your choice from the drop down menu that is available for DMHS programs Non DMHS program codes are determined by the placement of the cursor when the program is created 3 through 8 A further discussion on Program Codes is included in the Appendix section The Program Name selection is primarily for the user as a way of keeping local names attached to the budget Examples might include The Club Prospect House Family Resource Center Homestead etc The County of Service and Client Age Group choices must be made from drop down menus If the program operates in more than one county choose Regional but if it operates in more than one region choose Statewide When your selections are made click on OK and the identifying data will be transferred to all four worksheets Your choice of code number will appear in row 8 where the cursor was placed At the same time the corresponding program type pre set by DMHS that is associated with the code will appear in row 5 The local Program Nam selected by the user will appear in row 4 the County of Service will appear in row 3 and the Age Group will appear in row 2 If your age
14. 2002 2003 The specific hardware requirements will be the same as those for the version of Excel that you are using As with most large applications additional random access memory and processing speed will help the Budget Matrix operate more quickly The operating systems for which the Budget Matrix application have been tested include Windows 95 Windows 98 Windows NT and Windows XP both single user and network versions When opening an Excel Budget Matrix file you will be advised that the file contains macros and you are prompted to either Disable or Enable the macros You must choose Enable for the Budget Matrix file to operate properly However if you cannot open the file at this point after choosing Enable then you will need to re set the security settings for your version of Microsoft Excel If your Excel security level is set at high or very high the macros in the Excel Matrix will be automatically disabled From th Excel menu bar select Tools Options Security Macro Security then choos th medium security level Different versions of Excel may have a slightly different menu path Once you have finished your Excel Budget Matrix session you can re set your security level if necessary A WORD ABOUT FRINGE BENEFIT EXPENSES Employee fringe benefits are usually calculated on either a percentage of salary amp wages basis or th
15. Modification or Budget and ROE for further analysis Although this program is designed to be user friendly a working knowledge of the Microsoft Excel software program is necessary The Division of Mental Health Services is one of several operating divisions of the N J Department of Human Services The DMHS Contract Administration Unit is responsible for the negotiation processing analysis and monitoring of service contracts with a large network of community mental health agencies In 1984 Les Foster a DMHS Contract Administrator developed the Budget Matrix program in order simplify the considerable analytical work associated with contract budgets With assistance from Rex N J Division of Mental Health Services O Neal general use by Dan Knox The 1985 version 1 2 3 Version la and others of the Budget Matrix was an applica and it included two separate files the Budget Matrix was DMHS and its community servic Excel Budget Matrix Users Manual adapted for agencies tion of LOTUS MTRX2 2 and SCHD2 2 on a single 360k diskette The Budget Matrix program required some patience and dedication to learn but it soon proved to be a major advancement in both time and accuracy over the handwritten and typed budget forms The Budget Matrix was widely distributed to DMHS contract agencies computer equipment and training was made avai
16. THE EXCEL BUDGET MATRIX SOFTWARE APPLICATION Introduced December 2002 Version 1 2 Revised July 2003 Version 1 3 Revised August 2006 Version 2 0 N J Division of Mental Health Services Excel Budget Matrix Users Manual TABLE OF CONTENTS Section or Topic Page Introduction and Background 3 4 What s New in the Excel Budget Matrix 5 6 Menu and Command Structure Menu Tree 7 Overview of Matrix Commands Macros Add Alter Agency Contract Info 8 Format Spreadsheet 9 10 Edit Program Element Info 2 Copy Budget to Mod Worksheet Lo Copy Mod to Budget Worksheet 3 Clear Worksheet 4 Variance Reports 15 16 7 Print Spreadsheet G amp A Allocation Method 18 Sequence Guide for Users Original Contract Budget 19 Contract Budget Modification 20 Reports of Expenditure Income ROEs 21 Appendices Program Codes and Names 22 23 Definitions 24 25 Hardware amp Software Requirements 26 A Word About Fringe Benefit Expenses 26 Budget Schedules amp Other DMHS Forms Zi Other User Issues amp Points To Remember 27 Tips on Good Data Management 28 N J Division of Mental Health Services Excel Budget Matrix Users Manual INTRODUCTION amp BACKGROUND The Excel Budget Matrix is a spreadsheet application of the Microsoft Excel software program which is utilized by the N J Division of Mental Health Services and its funded contract Provider Agencies This is actually a secon
17. WO AANA I BWN EF BSW KB WWWWWWWWWWNnNNNHNHNHNNNNNHBPHEPHPEPRPERP EE ES WNHrROOWO WOAITAA TAB WBNHF CWO WOANIA UUIBWNEF OCW is listed below Total Cost unused DYFS contract program DDD contract program DAS contract program DCBHS contract program non DMHS program non DMHS program DMHS Subtotal nused nused formerly Consultation amp Education nused formerly Youth Case Management esignated Screening Center nused formerly E S Child mergency Services Adult amp Child n n u n Oo mi used formerly Adult Liaison Services used formerly Child Liaison Services tpatient Services Adult amp Child used formerly Outpatient Child Outpatient Services Other Partial Care Adult unused formerly Partial Care Child Partial Care Other Residential Services Adult DMHS Licensed unused formerly Residential Child Residential Services Licensed by DCA DHSS or others Residential Support amp Supportive Housing Systems Advocacy Other DMHS program Other DMHS program P A C T Team Services Integrated Case Management Services Adults P A T H Services for the Homeless Supported Employment Intensive Family Support Services Self Help Centers Welfare To Work for GA amp TANF clients Criminal Justice and Jail Diversion programs unused unused unused Unallowable General amp Administrative O
18. ady use Diskettes should be handled with care Writing on diskette labels should be done with felt tip pens don t bend diskettes or use paper clips on them and avoid placing 3 5 diskettes near magnets as they can render your data files useless Note telephone instruments contain magnets It is a good habit to always record the date as part of your work files If your computer has a functional clock that is set to the appropriate date your Matrix data files are dated each time you print them Select filenames for your data files that are meaningful or descriptive of your work so that future attempts to retrieve data files will be easier Also it is a good idea to always include the filename on the printed hardcopy or report again to avoid confusion when trying locate one file among many Develop an agency wide or office wide procedure to periodically weekly monthly backup your most important computer files on diskette compact disc or tape media and keep such backup copies off site at an alternate location This practice will ensure that your most critical work is secure in the event of destruction by fire or vandalism at your regular work site 28
19. d generation product with the original Budget R O E Matrix having been developed in 1985 as a Lotus 1 2 3 application The purpose of the DMHS Budget Matrix software both the original Lotus 1 2 3 product and now the revised Excel product is the same as follows a to provide a standard software format to DMHS staff and all contract provider agency staff for saving storing easily retrieving and printing important budget and expenditure data and b to automate some of the routine but fairly extensive calculations that are performed by DMHS staff and provider agency staff to enable full budget line by line variance change reports to be created which greatly facilitates the planning and monitoring aspects of our contract budget responsibilities The normal budget format employed by the N J Department of Human Services Contract Reimbursement Manual has been adapted for use in the Excel Budget Matrix by the Division of Mental Health Services DMHS with customized budget templates and schedules and a menu system which allows for easy movement data entry and user commands The Excel Budget Matrix program actually operates in four 4 separate worksheets in the same fil workbook The user will develop an initial Budget update the budget with e d Modifications issue quarterly Reports of Expenditure Incom R O E Ss and then compare the variances between Budget an
20. d yields the same variance comparison from Budget to Modification presented as a percentage change When performing more than one variance comparison in succession there is no need to use the Clear Worksheet command first since the data in the Variance sheet will be overwritten by the new commands The Dollars Remaining per ROE command will reveal how many dollars are still available in all parts of the contract budget after subtracting the funds expended and revenues applied from the cumulative quarterly or final data in the ROE sheet The Interim ROE Percent Expended command will present the data in the ROE sheet as a percent of the Budget sheet ROE divided by Budget for each cell on a relational basis The Final ROE Change from Budget 3 command yields the amount of change from Budget to ROE for all cells in the spreadsheet presented as a percentage of the approved budget These ar xtremely useful analytical tools for staff of both provider agencies and DMHS 15 N J Division of Mental Health Services Excel Budget Matrix Users Manual Print Spreadsheet At various times the user may need to print out one or more of the worksheets either to review and consider a work in progress more closely or to issue reports of completed tasks The Print Spreadsheet command gives the user a quick efficient way to do this Prior to choosing Matrix
21. data stored on a disk for future retrieval For Microsoft Excel software a file is also known as a workbook Format 1 A DOS command which initializes or prepares a disk for writing files to the sectors of the disk 2 An Excel command choice which allows the user to specify different ways to display or present the same numbers and or text data in the spreadsheet 3 A Budget Matrix command choice which allows the user to specify the number of program columns to be displayed in the Budget Modification and ROE G amp A Abbreviation for General amp Administrative a budget term which refers to common or shared expense items which cannot be assigned as direct program expenses 24 N J Division of Mental Health Services Excel Budget Matrix Users Manual Label Any cell entry that starts with a letter or a label prefix or ats Macro A set of consecutive instructions which is interpreted by the software program and which speeds up repetitive routine or complex operations Macros are used by programmers to assist less experienced users in performing tasks Menu A series of command choices that appear usually in the top control panel or within a drop down menu tree when invoked by menu command The user can invoke the menu by clicking on the desired choice with the mouse or by typing the proper keys usually alt or ctrl plus a letter then usin
22. e default setting and should work fine for the overwhelming majority of users As you add or delete columns by Matrix commands the G amp A formulas are adjusted accordingly Some DMHS contractors use the percentage of salary cost method This method is preferabl where there is a significant amount of pass through or subcontract expense in the non salary portion of the budget which would unfairly allocate G amp A expenses which are primarily Personnel under the direct total cost method If your organization utilizes an Indirect Cost Plan which is typically more complex than a presentation of mental health G amp A expenses the Excel Budget Matrix can now more easily accommodate this choice In this case you would submit a detailed Indirect Cost Plan or supplementary schedule to your assigned DMHS Contract Administrator for review and approval The Indirect Cost Plan should have sufficient detail to show what the G amp A expenses are for the entire company and how they are allocated or stepped down to the DMHS contract Organizations with an approved Indirect Cost Rate would need to first provide the appropriate documentation Similar to an Indirect Cost Plan to support the Rate to their assigned DMHS Contract Administrator 17 N J Division of Mental Health Services Excel Budget Matrix Users Manual Once you have determined the proper
23. e direct identification basis benefit calculations for each individual employee as aggregated The direct identification of fringe benefits is the preferred method particularly for Final ROE data since it represents precise cost data The Excel Budget Matrix is pre set for users to add fringe benefit expense data in each relevant cell per the direct identification method However we understand that FOr budgeting purposes and interim ROE submissions allocating fringe benefit expenses on a percentage basis iS an acceptable practice Should you wish to use the percentage basis for allocation you will need to write the appropriate formulas in the fringe benefit area of the Budget sheet and then make sure that the formulas are copied to the Modification and ROE sheets when they are used Please seek additional help from an experienced Excel user or your DMHS contract administrator 26 N J Division of Mental Health Services Excel Budget Matrix Users Manual BUDGET SCHEDULES amp OTHER DMHS FORMS There are several schedules or attachments that may be necessary to more fully explain or record a subset of data that supports the amounts in the Budget or ROE file Examples include schedules for Depreciation expenses Interest xpenses Equipment purchases client Housing Space amp Equipment Rentals Related Organizations and Allocation Bases Thes schedules are
24. e right justified numbers and improve the appearance of your spreadsheet The program codes are an alternate method of identifying each Program Element They ar ntered in row 8 of the Matrix below the corresponding program names Sinc th codes are numbers they are automatically right justified in the column by Excel When adding program columns to your file the Excel Matrix macros automatically place the new columns in ascending sequential order from left to right in row 8 It is permissible to have several like programs with the same program code Some columns have a pre set fixed program code examples are Total Cost 1 DMHS Subtotal 9 Unallowable 42 and General amp Administrative 43 The full set of DMHS program codes with corresponding program names is listed on the following page As a Matrix user it is important for you to list out the program names and codes that you will be using prior to running the Budget Matrix application In this way you will be prepared to set up the Matrix for the specific programs sponsored by your agency The Matrix commands will enable you to establish the correct layout of DMHS and non DMHS program columns and to apply the correct program names and codes 22 N J Division of Mental Health Services Excel Budget Matrix Users Manual The full set of DMHS program codes with corresponding program names AMAIA UOABWNFO
25. g arrow keys and pressing enter to make the selection Program Within the Budget Matrix application a program refers to a mental health DMHS or non mental health cost center Each program is displayed ina separate column Program Codes Numbers which have been assigned by DMHS to identify certain programs These Codes appear on row 8 of the Budget Matrix and are also used on the DMHS Annex A amp QOCMR forms Adherence to the DMHS program coding scheme is necessary to ensure that data submitted to DMHS can be properly entered into the Contracts Database Row A horizontal series of cells in the worksheet which correspond to budget line items In a database program a row is known as a record Worksheet A grid made up of rows and columns which is used to enter and manipulate spreadsheet data The term worksheet commonly refers to the data file or application which is currently being viewed on your computer screen The Microsoft Excel software is designed to accommodate many worksheets within the same fil workbook and the Excel Budget Matrix features four separate worksheets in the application that users will see and use 25 N J Division of Mental Health Services Excel Budget Matrix Users Manual HARDWARE amp SOFTWARE REQUIREMENTS The Excel Budget Matrix is written to operate properly when using Microsoft s Excel 97 Excel 2000 or Excel XP
26. ion 3 Apply program names and codes Edit Program Info macro 4 Save File using standard DMHS Excel command file naming scheme 5 Enter budget amp revenue line descriptions keyboard commands 6 Enter dollar amounts to budget keyboard commands and units and clients served in the section below Budget Summary as applicable 7 Save File using standard DMHS Excel command file naming scheme 8 Print must Budget Matrix Print macro have correct sheet on screen NOTE When saving your work for the first time always save the file to your computer s hard drive or to a network hard drive not the floppy disk drive The Excel program automatically proceeds to save a duplicate file and there isn t enough space for two Budget Matrix files on a 3 5 diskette so the attempted save to the a drive will not work Once the file is saved to the hard drive you can later save to a diskette in the a drive if the file size is less than 1 4 MB 19 N J Division of Mental Health Services Contract Budget Modification 10 OCEDURE PR term of agency If there is a change in contract change contract End Date If there is the addition or deletion DMHS amp non DMHS programs Apply program names and codes for any new columns Copy existing budget data into Modification sheet re entry Enter do sheet a Add rows
27. lable and eventually DMHS required that all contrac budgets and R O E s must be submitted on diskette the Budget Matrix program per After several Matrix program leading to new possibilities once the standard for the industry Excel and DOS operating systems had been upgraded to Windows systems And Budget Matrix users offered some constructive criticism of the original product which aided in the design of the revised Excel Budget Matrix in 2002 2003 Ron Spiessl and Dave Salewski developed the Excel Budget Matrix which incorporated the primary functions from the original product with years it becam necessary to revise the Budget Hardware and software advancements had been made Lotus 1 2 3 spreadsheet software had been eclipsed by Microsoft the enhanced features of the Microsoft Excel Windows based software Version 2 0 introduced in 2006 and also developed by Ron Spiessl contains new features which build upon the original product It is important to note that some of the principles employed by the Budget Matrix can be adapted for other types of spreadsheet analyses While DMHS staff generally use the plain vanilla form of the Matrix staff of some mental health agencies have converted the basic Matrix to perform other agency specific analyses The Budget R O E Matrix is a copyrighted 1985 program of the N J Division of Mental Health Services Staff of
28. ll require in the different sections particularly the Personnel section for staff positions The pre set Budget Matrix file Budget Matrix v2_0 xls contains enough rows for about thirty five staff positions and has one non DMHS program column coded as 8 and one DMHS program column coded 15 Start by editing the pre set DMHS column to your needs From within the Budget sheet most commands must be issued from within this sheet place the cursor on row 8 in the DMHS program column Invoke the Edit Program Element Info command and rename this program to your specifications see next section for discussion of this command Next if you have an additional DMHS contract program invoke the Format Spreadsheet command choose Add DMHS Program Column and you will be presented with the same dialog box choices as the Edit Program Element Info command Select the particular program name code county and client age group click on OK when completed and the Matrix macro will create the DMHS Program with appropriate name code and identification in all four worksheets If you have additional DMHS Programs follow the same procedure you will need to add new programs to your spreadsheet one at a time via this command macro Relax it won t take long The program specific information is important to the DMHS Contracts Database and this is how it is generated Fo
29. method for G amp A expenses you must invoke the Matrix menu choice G amp A Allocation Method You have three choices Total Cost Salary and Agency G amp A Plan The default choice is Total Cost if you choose Salary or Agency G amp A Plan you are then prompted to click OK if you want to change your allocation method or Cancel to keep the current method Once changed the current method is indicated in Column A on the line row in which the allocation is performed directly above the Revenu section of the budget For either the Total Cost or Salary methods the chosen G amp A allocation method is calculated and applied automatically to all four sheets and can easily be switched again by use of the Matrix menu command For the Agency G amp A Plan method the calculations are generated by the user and are not replicated to the other sheets or saved for future use unless further steps are taken s below If you select Agency G amp A Plan the allocation line is unprotected and the user must enter either whole numbers per the Plan or formulas that include percentages per the Rate in each DMHS and non DMHS column Subtotals are protected they contain calculations Note that the user driven allocation here must match the approved Plan or Rate and the Total Cost column budget code 1 must show zero on the G amp A allocation line If you are using an a
30. n data that has been approved and moved to the Budget sheet or Variance data following the user s analysis A word of caution the user must invoke this command from within the appropriate worksheet since there are no second chances the Excel undo command is not available within the Matrix program 14 N J Division of Mental Health Services Excel Budget Matrix Users Manual Variance Reports The real utility of the Budget Matrix program is evident in this set of command macros Invoking the command will lead to five choices two of which involve comparisons between data from the Mod sheet and Budget sheet and three of which involve comparisons between data from the ROE sheet and Budget sheet The Mod Change from Budget choice will subtract Budget sheet data from Mod sheet data for all cells in the worksheet on a relational basis yielding the dollar difference for the Modification on a line by line cell by cell basis and present the answer in the Variance sheet The result is a full change from budget report which shows precisely how the official budget will change if the Modification is submitted and approved This type of variance analysis is useful for provider agency staff when considering the impact of changes and various what if scenarios and it is important for DMHS staff when evaluating submitted Modifications for the impact of changes The Mod Change from Budget comman
31. ncy operates a DMHS licensed adult residential program and you have one or more budget columns identified by code 24 you will be prompted to complete an additional drop down menu choice for the specific level of care such as A GH B Apt etc that is associated with the program The residential code 24 level of care information is displayed in row 6 following the user s data entry choice This data is helpful to an analyst who may not be fully familiar with the agency or its budget and it will be extremely important to DMHS as the data is later merged into a database and used for planning purposes At this point don t worry if the identifying information is not entirely visible in the column or does not 11 N J Division of Mental Health Services Excel Budget Matrix Users Manual look finished there will be other opportunities to format the spreadsheet prior to printing the document Use this command primarily to identify your first DMHS and non DMHS program column and to change information to a pre existing column When formatting your budget spreadsheet for use and you are establishing multiple columns you will be invoking the Format Spreadsheet command and this command will lead into the Edit Program Element Info command choices 12 N J Division of Mental Health Services Excel Budget Matrix Users Manual C
32. opy Budget to Mod Worksheet This command macro will be utilized when you are working with a fully loaded and approved contract budget located in the Budget sheet and you are attempting to modify the budget for certain changes that are planned The primary purpose of this macro is to save valuable time and data entry for the user Since it is likely that some or even most of the budget expense and revenue line item amounts will not change in the updated version of the budget Modification it is a time saver to copy the entire Budget sheet data into the Modification sheet for use as the starting point for preparing the Modification That s all this macro command does Invoking this command will open a message box advising the user that all Budget sheet data will be copied into the Mod sheet that existing Mod sheet data will be overwritten and prompting the user to either continue or exit by clicking Yes or No After clicking Yes the user now has all Budget sheet data also in the Mod sheet and can now review and edit data in the Mod sheet to record how the budget will be changed Copy Mod to Budget Worksheet Once the modified budget has been prepared submitted to DMHS and approved by DMHS it is now time to reorganize the Budget Matrix file to ensure that the officially recognized current budget data is in the Budget sheet Remember the starting point for future Modifications and for comparisons
33. oupings possible e Program specific clients served and units of service data has been added below the Budget Summary section along with built in calculations for Per Client Cost and Cost Per Unit of Service This is a useful introduction of key program statistics to the Matrix file which will allow users to evaluate their programs according to these benchmarks and will enhance the DMHS Contracts Database with data that had previously been collected separately e A total count of FTE full time equivalent staff positions is established below the Hours per Week column in the Personnel section By entering the Hrs Wk for each staff position then entering the standard number of hours in the workweek for the organization the calculation will yield the total number of FTEs for the organization e The column immediately to the right of the last column in the spreadsheet General amp Admin has been unprotected to allow users a field to enter user notes comments etc This user memo field will not be part of the printed hardcopies e A reminder message has been added to the Matrix Add Alter Agency Contract Info menu choice indicating that DMHS requires a five digit assigned contract number not the DMHS filename which is entered here and displayed at the upper left corner of the spreadsheet N J Division of Mental Health Se
34. pproved Rate you would need to write an Excel formula to generate the correct percentage then copy it to each program column The DMHS Contract Administrator or an experienced Excel user can assist a new user with this task In order to protect the data in the Agency G amp A Plan allocation from being erased by one of the copy or clear commands invoke lt shift gt lt ctrl gt p to save the data in the current sheet must be done in Budget Mod and ROE sheets separately or lt shift gt lt ctrl gt a to copy the data to the Modification and ROE sheets In this way if you change methods then decide to change back you haven t lost your original entries 18 N J Division of Mental Health Services Excel Budget Matrix Users Manual SEQUENCE GUIDE FOR USERS As a guide for users DMHS lists a number of steps or user commands that should be followed in sequential order in order to achieve the most satisfactory results with the Budget Matrix program Please adhere to the following program steps in the order given Original Contract Budget PROCEDURE COMMANDS 1 Record Agency Name Contract Number Add Alter Info macro Contract Start amp End Dates 2 Set up spreadsheet to correspond to Format Spreadsheet agency DMHS amp non DMHS programs macro add or delete rows to accommodate individual agency needs especially for Personnel sect
35. r non DMHS programs you can follow the same procedures outlined previously The first non DMHS column can be edited to your specifications by placing the cursor on row 8 of the column Other B and then invoking the Edit Program Element Info command and completing the prompts for data Additional non DMHS programs can be added and edited by placing the cursor in row 8 of any selected non DMHS Subtotal and invoking the Format Spreadsheet and Add Non DMHS Program Column commands and completing the data requests N J Division of Mental Health Services Excel Budget Matrix Users Manual As your budget grows and additional programs are added to your DMHS contract you now know how to add new program columns Conversely if it becomes necessary to eliminate a DMHS or non DMHS program you can accomplish this by invoking the Format Spreadsheet and Remove Program Column commands This will remove the selected program column from all four worksheets In order to use this command the cursor must be placed in any cell in the relevant column to be deleted from within the Budget sheet So consider placement of the cursor before making this change You will be prompted that the relevant column will be deleted if this is your intent click OK to execute To add Rows extra budget lines to your spreadsheet move the cursor to an unprotected area of the Budget sheet where the rows
36. rvices Excel Budget Matrix Users Manual e Under the Matrix Format Spreadsheet menu choice the user now has the added choices of Hide and or Unhide Columns Many budget presentations are expansive and while it is desirable to have all the information in the file sometimes it is useful to customize the view for the work session or the finished printout copy to meet the user s needs e Also under the Matrix Format Spreadsheet menu choice the user can now choose the Color Worksheets command which will yield different colors for each set of worksheets and g S d tabs Some users may prefer the colors for their workin sessions as a visual reminder of which worksheet i currently active When done the White Worksheets comman restores the worksheets and tabs to their normal default setting Also the Print Spreadsheet command will automatically change colored worksheets back to a white background prior to printing e When adding numbers to cells in the Mod or ROE worksheets for line items that were blank in the Budget worksheet the actual change amount had previously shown as a blank in the Variance worksheet Subtotal and Total variances were shown correctly The Dollar Change in Version 2 0 now correctly reflects the change amount in the program column when performing the Mod Change from Budget S or Dollars Remaining per ROE choices for the Variance Reports
37. s in Variance Reports will be scrambled rendering them useless You can successfully use the Excel copy and paste commands without jeopardizing your work however DMHS discourages this practice except for building the original budget file When using the Excel Matrix there should be no need to utilize Excel Menu choices other than the Matrix command and the various submenu commands Under Print Spreadsheet the user will preview and finalize the print options from the standard Excel Menu choices but this is the only deviation from the Matrix command set 27 N J Division of Mental Health Services Excel Budget Matrix Users Manual TIPS 1 2 3 4 5 6 7 ON GOOD DATA MANAGEMENT Set up a new folder or directory on your computer hard drive or on a network drive for all your Excel Matrix work files Save your files to this hard drive folder first Make it a habit to save your current work file periodically every 10 15 minutes for example This will prevent the loss of your important data in the event of a power failure or inadvertent commands that cause your current file to disappear For important work files such as your Excel Budget Matrix it is good practice to make a backup copy on a removable 3 5 diskette thumb drive or Compact Disc CD in addition to your main hard drive copy Always maintain several new formatted diskettes or CDs for re
Download Pdf Manuals
Related Search