Formatting the Spending Plan Template
Contents
1. CONSUMER DIRECTION MODULE Formatting the Spending Plan Template Created January 2010 Last Updated February 2010 Author Dennis Horan National Resource Center for Participant Directed Services Formatting the Spending Plan Template Table of Contents Before you Statta aa aa a i a daha anna ra a Ea Ea a TAN aS 3 Pre ReguisiteSiureroananiii iiine e EN EE EE E EEEE anne EERE E TE 3 C utions When Formatting assisa init hecho aoc tthndstubes daca saoiecgnetelvatincaoks 4 Sumtnar of St ps orenean enean E EEE E E E 4 Format Sample Speriding Plai s isisisissirsiirirriiiesnisiairisinisieiniania nesei iainta adraia isiin 5 Optimize Column 1G BAS sststesissiso santas cecasrsnunssekatanisiacaesnsnuataam sa aai DAAA ENE ET RESA EAER ESRA 5 B Modify Page Setup to keep category columns and rows ON SAME page se sesisserssseesreesseerteereesrress 7 Be Mabie MATEINS aisanana a Aa ae aaa aaea Daae AESKA E Nea AA AASA EE aSa 7 be Change Page Orientation and Scaling suresinin a a 8 C Set Page Breaks to Keep Categories Togethet sse essserssersssressressresseessresstesrresnresnressrensrenereserenerenress 9 Modify Bl nkPage xls sninen a EEEE EEEE 9 Modification of Spending Plan in the CDM Admin Section s sesssssssesssssessressresseresererersrersrresrresrresreesereseee 10 Formatting the Spending Plan Template Before you Start When you first install the CDM or after installing enhancements or adjusting spending plan2. notes apply the same changes to the BlankPage xls file that you made in Step 2 Save the updated BlankPage xls file in the backend of the CDM Formatting the Spending Plan Template Format Sample Spending Plan The following section gives detailed instructions on how to make common formatting changes to your spending plan Keep track of the changes you make in Step 2 because you will need to make the same changes to the spending plan template file blankpage xls in Step 4 These instructions ate A Optimize Column Widths The first step in the process is to change the column header widths to optimize viewing The best actual width for a column is subjective however keep in mind these factors when looking at each column s width e See that the column width is suitable in the header section and all categories in your spending plan e Smaller column widths and smaller number of displayed columns increase the capability to view an entite spending plan within one screen e Smaller column widths and smaller number of displayed columns make it easier to print out a spending plan s contents one page wide P gp pag With these factors in mind let s proceed To change the column width for column A click on the line in the column headings separating A and B as shown below Formatting the Spending Plan Template al File Edit View Insert Format Tools Data Window Help Dae BeaigdkivGa s4aea JFi4 Bz ii
3. settings the spending plan Spreadsheet may not display or print as you would like see Figure 1 for an example These instructions tell you to modify column widths page breaks and print settings so that every time a spending plan is downloaded it is optimized for viewing printing and efficient completion Example A B c D E F G H I J K i M N a P G 1 DOH Region II ACME Services LLC Spending Plan H 2 4400 N Broad St Philadelphia PA 19104 i 3 K 111 222 4444 SS z n 5 Participant f Sherwin Aaron Budget Period Monthly 6 Participant 23 Main St Budget Amount 3000 i Philadelphia Massachuse 19108 Budget Effective Date 074012009 8 Budget End Date 06 30 2010 3 Medicaid M2315662 10 Participant F Active Spending Plan Start Date 074012009 n Spending Plan End Date 06 30 2010 12_ Support Bre Veronica Archer Days in Spending Plan 365 8 Spending Plan Version 2 Services Service TypetDe Total Services A a Es CIP aee E 30 up ii Additional Cost jp Saved for for pSommments a Puen H 1e 34 Total Emergency res Plan for Personal Care __ 0 00 Provider Name Comments Comments Gosti AN Danndinn Nan f Tham Ondas F Danica Ondas F Figure 1 unformatted spending plan a Units per Service Type Descripti This screenshot shows four formatting problems 1 Column A s labels are cut off because the column is not wide enough 2 Some column headings like Service Type Description and Emplo
4. this will keep the category together Modify BlankPage xls BlankPage xls is the template excel file from which the CDM builds your spending plan So if you apply all of your changes to the blankpage xls file then all future spending plans will have the optimized format you created for step 1 The steps are as follows 1 Retrieve the BlankPage xls file from its location in your website The default location is the files directory under your root If you are not the system administrator ask your system administrator to retrieve it for you 2 Open the BlankPage xls file It will look blank 3 Repeat all of the changes that you made to the downloaded spending plan 4 Save the file NOTE be sure to save the file in the xls format Do NOT save it as xlsx or any other spreadsheet format 5 Return the file to its location on the web site or ask your system administrator to do so 6 Log back into the web site and download a spending plan Verify that the spreadsheet formatting is the way you want it a Ifthe spreadsheet still does not look correct refine the spreadsheet by beginning the process again starting by downloading a new spending plan from the CDM front end 7 Ifyou use individual Excel expenditure reports in your state program download a sample expenditure report and verify that its formatting is also as you would like 5 jp Formatting the Spending Plan Template Modification of Spending Plan in the CDM Admin S
5. ection In general once you make the modifications in the previous sections you will no longer have to make changes to the spending plan blank page However please note that if you change settings in your admin settings such as hiding inserting a column changing the number of rows in a category activating de activating a category then you will likely need to re format your spending plan template Formatting the Spending Plan Template NRCPDS Boston College 314 Hammond Street Chestnut Hill MA 02467 617 552 6727 Fax 617 552 1975 www patticipantdirection org The National Resource Center for Participant Directed Services provides research based technical assistance on all aspects of designing implementing managing and evaluating participant directed programs te
6. g Examples Cautions When Formatting There ate two cautions when formatting your spending plans Never delete rows columns or cells Doing so will not help the format of your spreadsheet as the whole spreadsheet is created dynamically in the program In addition there are a couple of hidden fields such as FMS Tax rate If you happen to delete any of these hidden fields the calculations or the upload of data into the CDM may be effected The file BlankPage xls is the blank template page for both expenditure reports and spending plans If you use individual Excel expenditure reports in your state program be mindful of how the template modifications may affect them Summary of Steps In order to modify the spending plan template without causing other errors follow these general steps always keeping in mind the cautions written above 1 Download any spending plan from your CDM Format this spreadsheet as you would any other spreadsheet making sure to not delete any rows columns or cells Keep exact notes of all the changes you make In the backend of the CDM locate the spending plan template called BlankPage xls The file will appear blank because all spending plan fields are populated dynamically in the CDM when you download a participant s plan Make a copy of this file and archive it This way if your formatting changes create any problems you can easily roll back by placing this copy back in the directory Using your
7. ia T 2 ga Snagit Region E L3 Width 9 43 71 pixels B Cc G ACME Services LLC 4400 N Broad St Philadelphia PA 4 111 222 4444 H Participant f Sherwin Aaron Budget Period Monthly Participant 23 Main St Budget Amount 3000 i Philadelphia Massachuse 19108 Budget Effective Date O7 01 2009 i Budget End Date 06 30 2010 3 Medicaid NZ2315662 10 Participant Active Spending Plan Start Date O7 01 2009 11 i Spending Plan End Date 06 30 2010 12 Support Bre Veronica Archer Days in Spending Plan 365 13 i Spending Plan Version 2 14 i 15 Services Service Estim Type De Hours i seri tion atiaaaaaal aninainasal a Figure 2 Changing column width Notice that when you start to drag the column width a box displays the current column width 9 43 in the snapshot shown We will drag this column width out to 20 86 Make this change to column B C etc all the way out to the last column in your spreadsheet So in my sample spending plan I changed each of the column widths and recorded them A 27 14 B 11 14 C 11 14 N 15 O We will be using these settings to repeat these changes with the template file BlankPage xls Author Note While going through this example I found column widths that were not in good alignment It turns out that this was the first column in a couple of the categories spanned two columns in the spreadsheet the admin setting I went into the admin
8. ost programs If you are using Excel 2007 you can adjust the scaling using this same method First you have to go to Page Layout then click on the Expand icon in the lower right corner of the Page Setup box as shown in Figure 5 Formatting the Spending Plan Template Home Insert Page Layout Formulas Data Review View Acrobat Colors is e F h al E Width Automatic i La la a GH A Fonts E a 3 Height Automatic Themes Margins Orientation Size Print Breaks Background Print Effects 7 Area X Titles rh Scale 100 Themes Page Setup Scale to Fit Figure 5 Excel 2007 Page Layout and Expand Icon a Y el When you choose to scale the page like this you want to make sure that your printout does not get reduced to a point that your printout is not readable Print a sample page and see if the size is acceptable Generally speaking the actual page size should be close to one page wide before you make this choice Important as in previous sections make a note of the selections you make C Set Page Breaks to Keep Categories Together Looking at Figure 1 item 4 with the current print settings the Category Heading Participant Delegated Goods and Services would print on page 1 of the document and the contents of the category would print on the next page To make this change select row 34 of your spreadsheet Then from the menu on top select Insert gt Page break Doing
9. to change the column span so that the columns would match up easier Keep this option in mind when formatting your spending plan layout 6 jph Formatting the Spending Plan Template B Modify Page Setup to keep category columns and rows on same page You can modify the page setup in order to keep columns and rows on the same page and to optimize printing If you are using Excel 2003 or earlier all of these steps are conducted within the Page Setup menu in File gt Page Setup If using Excel 2007 the same steps can be done from the Page Layout tab in the ribbon that takes the place of traditional menus a Change Margins If using Excel 2003 or earlier from Page Setup go to the Margins tab If using Excel 2007 from Page Layout click on Margins and select Custom Margins Page Setup 2 x Page Margins Header Footer Sheet Top Header Print fi 0 5 Print Preview Options Left Right 0 75 44 0 75 Bottom Footer A Center on page Horizontally Vertically aan Figure 3 Excel 2003 Page Setup Margins Tab The margin settings dictate the distance that will be left between the spending plan text and the edge of the paper For example the top margin of 1 0 means that Excel will leave a full inch of space at the top of every page when printing To maximize your space on the page yet still leave the page readable these settings for margin are recommended Top 0 5 Bo
10. ttom 0 5 Left 0 25 Right 0 25 Select Print Preview to see the results of your setting changes PR Formatting the Spending Plan Template b Change Page Orientation and Scaling If using Excel 2003 or earlier from Page Setup go to the Page tab Page Setup 2 x Margins Header Footer Sheet Orientation Print e i j Portrait Landscape Print Preview Scaling Options Adjustto 100 normal size yal Fit to fi page s wide by fi tall Paper size Letter 8 1 2 x 11 in First page number Auto Ce Figure 4 Excel 2003 Page Setup Page tab There are two items that you could change on this tab to help your print setup First you could change your orientation to Landscape This means that the longer part of the page extends sideways If your program has a lot of columns in your spending plan this may be a good option To change the orientation in Excel 2007 go to the Page Layout tab click on Orientation and choose portrait or landscape The second item you can change is scaling You could select the option to adjust the size by a percent factor You can also click the option button that says Fit to This option would shrink the size of the printed text to fit on your page If you do select this option it is recommended that you change your selections to 1 page wide by two pages tall This is the selection that would fit the size of a spending plan for m
11. yee are not wide enough to display the heading or the content 3 The printout only goes to column L while the spending plan extends to column O 4 There is a page break in the middle of the Units per Spending Plan Category It should be noted that how you wish to display your spending plan is your own decision There are multiple acceptable ways to display a spending plan The goal of this document is not to attempt to dictate your layout but to make you aware of the tools at your disposal when laying out the plan Pre Requisites This document assumes some working knowledge of Microsoft Excel If you are not familiar with Excel please take some time to familiarize yourself before continuing with this document 8 Formatting the Spending Plan Template This document also assumes that you have already set up the spending plan categories and column headings in the administrative section of your CDM website The things you can set up in these pages are as follows Choose which Categories to display Choose the Display Names for each category Choose the Number of Lines to display in the spreadsheet for each category Choose the Columns that you wish to display or hide Designate Columns that need to span multiple columns Dictate formulas to be entered in particular cells For more information on how to accomplish the above log into the CDM select the Help link at the top of the page and look at the User Manual and Trainin
Download Pdf Manuals
Related Search