Please enable JavaScript.

Coggle requires JavaScript to display documents.

Network for WS $7,028,136 / 13,804 = $510 per User ($7,028,136 = Cost Rec…

- - - - H34 = $646,676 = "Labor" = G34 (Why no increase in labor costs from 2019 to 2020?
      - H31 = $2,692,856 = "Nonlabor" = Sum(H6:H29)
        
        Nonlabor for 2020 is calculated similarly to Nonlabor for 2019. Most of the elements are a series of plug#s which are the same as those for 2019 (not inflated). However, the same 4 lines (12, 14, 25, 28) that had formulas in 2019 also have formulas in 2020
        
        H14 = $464,939 = Same numbers as 2019 except inflated by 15% and 5% respectively
        
        H25 = $980,034 = 2019 number inflated by 7%.
        
        H12 = $798,851Same formula as G12 with 2019 #s inflated by 1%
        
        H28 = $237,123 = Same calculation as for 2019 with a result of allocating a higher proportion of TT ITS to workstations but total cost of TT ITS remains $750,000
    - - G31 = $2,565,401 = "Nonlabor for 2019" = Sum(G6:G29)
        
        Sum(G6:G29) are a series of expenditure objects that look like they were cut and pasted from an expenditure report. Most of this series reference back to 2018 numbers which are all plug #s. However, the 2018 numbers are divided by 3 to arrive at 2019 #s. Have no idea why this is the case. There are 4 numbers in the series that are derived from formulas, and those are traced in the next set of branches.
        
        G14 = $421,103 = Workstation!F3 + Workstation!F4 Note: These are based on plug#s for 2017 inflated by 10% and 20% respectively for 2018, and then inflated by another 20% and 10% respectively for 2019.
        
        G25 = 915,920 = "INET Transport Services" = Workstation!F9 = 2017 plug# inflated by 7% for 2018 and 7% for 2019.
        
        G28 = $225,498 = "TT ITS" = (G14/(G14 + I14 + K14)) X $750,000. This formula is allocating a portion of TT ITS costs of $750,000 (plug#) to Workstations based on the proportion of Service Repair Maintenance Equip (line 14) allocated to Workstations.
        
        G12 = $943,186 = "Services Communications Telephone = Workstation!F7 + Workstation!F8 + Workstation!F10 These are are numbers that are based on 2017 numbers (plug#s) inflated by 1%.
      - G34 = $646,676 = "Labor" = Staff!G50
        
        Staff!G50 = E50 X G43 E50 is a plug number assigning 3 FTEs at a cost of $646,676 to "Workstations." G43 calculates the cost of an average staff per year = $215,559 = Sum(G39:G42). So rather than attributing specific people to this rate, it is calculating the average cost of an FTE in this unit and multiplying by 3.
        
        G41 = $23,382 = "Mgmt" = K32 = a calculation of the salaries and benefits of 3 FTEs (presumably managers), sourced from the FTE by Group file, adds the $21,000 IT Support cost to the salaries and benefits and then divides by 28, presumably to calculate the cost of management per FTE in the group. However, 28 is a plug number, and the total of all staff on this page is 24.5, not 28, so not sure why the number is 28.
        
        G40 = $21,000 = the standard IT Support calculation
        
        G42 = $7,362 = "Operation Support" = [Operations Management Cost]Supp for EBS and Ops Eng!M22 + [Operations Management Cost]Production Operations!I7 (We don't have this file)
        
        G39 = $163,953 = "Average Sal and Ben" = J26 which calculates the average salary and benefits for 20.5 FTEs in the Network CC. This information is sourced from the FTE by Group file.