New Search
ME DLIN Johnny R <br />From: <br />BURNETT Jim T <br />To: <br />MEDLIN Johnny R <br />Subject: <br />Full Cost Recovery <br />Date: <br />Wednesday, April 01, 1998 4:53PM <br />Greetings, <br />My apologies for taking so long to get back to you on this. I've converted the Lotus file to Excel. <br />Finn and I took a look at your Billable Hourly Rate Calculation. It seems well thought out and logical, so it's <br />definitely in the right direction (of course, you knew that since you did it!). We really had only had a couple of <br />comments on that part. The CSA actually includes departmental overhead (Admin) in it's calculation. So, I think <br />when you have a separate column for PW Admin, you're hitting yourself twice for that. You should probably talk to <br />Finn for more info on the CSA as he prepares it. <br />Also, I wasn't quite clear on how the personnel related M &S costs relate to individuals. <br />As far as getting the data into the spreadsheets, there are a couple of things that can be done and some things <br />that will most likely remain the same. <br />1- The position and employee info for the rate calculation live on the city's Position Management database. I left <br />most of your schedule intact except for the Salary and Fringe columns, which draw on a refreshable query table on <br />the worksheet "positions ". Any of the columns from POS# to Fringe can be gotten from that table, but I only chose <br />the dollar totals as I thought you'd probably like your labels better. The medical and fringe are combined. If you <br />need them split, it can be done with more detail in the query table and some more criteria (which is next to the <br />table). Also, the rate calculation could be put into a pivot table except for the last column, which would have to be <br />added next to the pivot table. I started one on "positions" at column M. The good thing about it is that it'll <br />automatically update added positions whereas on the worksheet with formulas we have to remember to add the <br />new position, which of course is not a big deal. If nothing else, that particular pivot table will give you check <br />figures. If the formulas have a potential to change often, the worksheet with formulas may be easiest. <br />2 - Billable Time Calculation - this information lives in the payroll system, which isn't accessible via an excel query. <br />It might be possible to do a Dataquery on the mainframe and download it, but my experience with historical <br />information in the payroll system is that it's rarely stored in a format useful to a an inquiry this detailed. I think that <br />using whatever reports come out with this data on it is probably the easiest (probably only) way to get it. When we <br />get a client server payroll system, this type of inquiry should become possible. <br />3 - Equipment hourly rates - don't know where you got these. If they live on an SQL database in client server, it <br />could be queried. But there aren't many and I'm guessing the rates don't change much, so that may not matter. <br />4 - DVSW Personnel - I put an adopted budget look from Performance Budgeting in a pivot table on the worksheet <br />"Budget ". Our numbers were a bit different so we'd need to compare our sources to find out why. Also, I put a <br />"sort of appropriation report on the worksheet YTD, just to give you a look at something off the general ledger. All <br />the pivot tables are refreshable and can be cleaned a bit for titles and such. The budget pivot table won't change <br />because, hey, the adopted budget doesn't change! The YTD report does show how the budget changes through <br />the year. <br />Well, I'm a bad editor and got wordy here. This isn't the answer to your prayers but there are a couple of potential <br />helps. Have a look and give me a shout. <br />Jim <br /><<File Attachment: TRE- MDL2.XLS>> <br />Page 1 <br />