Please enable JavaScript.

Coggle requires JavaScript to display documents.

Workstation User Rates: Email/Active Directory $6,190,236 / 13,804 Users =…

- - - - M11 = $640,469 = "SharePoint Online" = Sum(H11:K11)
        
        I11 = blank
        
        J11 = blank
        
        H11 = $7,000 = "Training/Conferences" = plug #
        
        K11 = $633,469 = "Labor Cost" = same calculation as K7
        
        Note: the labor costs for this branch are equivalent to the labor costs shown immediately above. That is, 80% of the same FTEs are allocated on two different branches, but the calculation is multiplied by .5, so this actually allocated .4 of the 4 FTE's to each branch..
      - M12 = blank
      - M16 = $780,000 = "Microsoft Premier Support Software" = Sum(Software!G23:H23) = Plug #'s for "EA Renewal" and "MS Premier Support"
      - M10 = blank
      - M9 = blank
      - M8 = blank
      - M7 = $660,164 = "Email Exchange Online" = Sum (H7:K7)
        
        H7 = blank
        
        I7 = 496 = "Software" = Software!G15:H15)
        
        Sum(G15:H15) = 243 + 253 = plug# inflated by 4%/yr
        
        J7 = $26,200 = "IT to IT" = IT to IT!E8
        
        IT to IT E8 = Sum(B8:D8) = B8 = Compute!G15
        
        K7 = $633,469 = "Labor Cost" = Sum(Labor Cost!M12:M16) X .5
        
        Sum(Labor Cost M12:M16) = salary and benefits for 4 FTE's sourced from FTE by Group file, plus $28,270 of additional costs as discussed above multiplied by Service and Customers B8, which is 80%. Note, Cell M14 is blank.
      - M13 = blank
      - M6 = $133,157 = "System Management Tools for Workstations" = Sum(H6:K6)
        
        I6 = blank
        
        K6 = $33,323 = "IT to IT" = IT to IT!E5 X Service & Customers!B5 (Question: Why is this multiplied by the Service and Customers % when the calculation in J5 is not?)
        
        IT to IT E5 = $133,292 = "Automation" = Sum(B5:D5)
        
        H6 = blank
        
        K6 = $99,834 = Labor Cost!M6
        
        Labor Cost!M6 = J6 X Service and Customers!B5
        
        J6 = $399,337 = Salary and benefits for 1 FTE + $28,270 of additional costs loaded to FTEs as discussed above
        
        Service and Customers!B5 = 25%
      - M14 = blank
      - M5 = $3,313,341 = "Identity & Access Management" = Sum(H5:K5)
        
        J5 = $554,499 = "IT to IT" = IT to IT!E6
        
        IT to IT!E6 = $554,499 = Sum(B6:D6)
        
        C6 = 0.00
        
        B6 = $527,064 = "IAM Server Cost" = Compute!G13
        
        Compute!G13 = $527,064 = a calculation of 60 servers X $6,550 per server (plug #s)
        
        D6 = $27,435 = "IAM Storage Cost" = Compute!I13 = $27,435 (plug #)
        
        K5 = $1,626,871 = "Labor Cost" = Sum(Labor Cost!M7:M10) = $406,718 X 4 FTEs = Salary and benefit costs tracking to FTE by Group File + $28,270 additional costs per FTE calculated as discussed above X Service and Customers!B6 = 100% (meaning these 4 FTE's are charged entirely to this function)
        
        H5 = blank
        
        I5 = $1,131,970 = "Software" = Sum(Software!G11:H11) + Sofware!H43
        
        Sum(Software!G11) = $25,609 + $26,361 = the sum of a series of plug#s for individual software inflated by 4% each year
        
        Software!H43 = $1,080,000 = projected 2020 cost of "Identity as a Service" software
      - M15 = blank
      - M4 = $206,863 = .5 of one person's time: Salary and benefits eventually tracks to FTE by Group File. $28,270 additional costs for this FTE, including the $21,000/yr "IT Support" calculation + $2,000/yr for "Software/Tools" and $5,270/yr for PM and Admin/Mgmt (Note: we don't have the files supporting any of the calculations for additional costs loaded to staff
      - M17 = $201,987 = "Security" = Sum(H17:K17) (Note: this cost is subtracted out on the right-hand branch above.
        
        I17 = blank
        
        J17 = $39,300 = "IT to IT" = IT to IT!E10
        
        IT to IT!E10 = Compute!D17 = 6 servers X $6,550 per server (plug #s)
        
        H17 = blank
        
        K17 = $162,687 = "Labor Cost" = Sum(Labor Cost!M17:M18)
        
        Sum(Labor Cost!M17:M18) = Salary and benefit costs for two FTEs + $28,270 of additional costs/fte/yr multiplied by Service and Customers!B9 = 20%
      - M18 = $36,527 = "Automation" = (Sum(Software!G18:H18) X Service and Customers!B5) + Software!G46
        
        Sum (Software!G18:H18) = $1,033 + $1,074 = "Flexera InstaShield" = $993 inflated by 4% each year
        
        Service and Customers! B5 = 25%
        
        Software!G46 = $36,000 = "DevOps Configuration Management Tool" (Plug #) (Note: the software branch immediately above is multiplied by the 25% from the Services and Customer sheet but this number is not.
    - - The security costs are subtracted out for this rate. These costs are included and traced in the left-hand branch so I'm not retracing them for this branch.