08/0909/1010/1111/1212/13 electronics (digitisers) current£91k£23k£69k requested£91k£91k...
TRANSCRIPT
![Page 1: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/1.jpg)
08/09 09/10 10/11 11/12 12/13
Electronics (Digitisers)Current £91k £23k £69kRequested £91k £91k
MechanicsCurrent £50k £45kRequested £50k £10k £35k
![Page 2: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/2.jpg)
Spreadsheet complexity
Spreadsheet ErrorsLack of experience with this spreadsheetMisunderstanding – use of column 4Misunderstanding – How to account for committed funds
Checks IntroducedGive confidence in figures
![Page 3: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/3.jpg)
Finance Spreadsheet Template
Approved Transfers
(excluding contingency)
Actual spend to Sept 09
Projected spend this
year
Actual spend (2+3)
Projected spend
(2+4+5)
Actual (6-1-1a)
Projected (7-1-1a)
(1) (1a) separate columns
for each FY (3) (4) 2010/11 2011/12 2012/13 (6) (7)
University Staff Effort Costs*University 1 EffortUniversity 2 EffortUniversity 3 EffortUniversity 4 EffortUniversity 5 EffortUniversity 6 EffortUniversity 7 EffortUniversity 8 EffortUniversity 9 Effort
University 10 EffortUniversity 11 EffortUniversity 12 Effort
University Sub-Total1
STFC Lab Costs
RAL
Daresbury
STFC Lab Sub-Total
Equipment1
Travel
Other Directly Allocated costs (eg consumables)
University Estates CostsUniversity Indirect costs
Total (Excluding VAT and WA)
Working allowanceVAT
Total (including VAT & WA)
Contingency (Held by STFC)
1 Excluding Working Allowance and VAT
* The University staff effort recorded in this table should be the 80% amount STFC pays, including academic time
Use of columns:(1) = The amount approved by STFC (1a) = This column should be used to show any virements between headings, for example when Working Allowance is used, the amount should appear as a debit in the WA row and then credited to the relevant row(2) = The actual spend in previous financial years, by year (3) = The actual spend in the current financial year up to the most recent quarter(4) = The total projected spend for the current financial year, including any expenditure so far (ie actual spend this year plus predictions of remaining spend this year)(5) = Projected spend for the remaining years(6) = The actual spend so far(7) = Projected spend over the whole duration of the project (ie actual spend so far plus predictions of remaining spend to project completion)The variance columns show the difference between the actual and projected amounts and the approved amount.
Latest estimate of future requirement (5)
Example: Finance Summary (all figures in £k)
Total VarianceCurrent year 2009/10Actual spend in previous years (2)
One is required per workpackage
![Page 4: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/4.jpg)
Difficult to work out allocations per workpackage.
Even with STFC data(STFC data generated by inputting figures into a database that generates output figures - difficult to replicate in spreadsheet)
![Page 5: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/5.jpg)
Grant award given in years relative to grant start date
Staff years Translated into FY’s
Staff yearsTranslated into FY’s + index linked to salary
Estates and direct costs into FY’s + index linked to salary
Travel costs translated into FY’s and index linked
Yellow – students (no estate or direct costs)
Purple – funded through rolling grant
Blue squares used as link to master spreadsheet
Linked back to Salary/estates/Indirect costs spreadsheet
Complex spreadsheets required to simulate Database behaviour
![Page 6: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/6.jpg)
Approved Transfers
(excluding contingency)
Actual spend to Sept 09
Projected spend this
year
Actual spend (2+3)
Projected spend
(2+4+5)
Actual (6-1-1a)
Projected (7-1-1a)
(1) (1a) separate columns
for each FY (3) (4) 2010/11 2011/12 2012/13 (6) (7)
University Staff Effort Costs*University 1 EffortUniversity 2 EffortUniversity 3 EffortUniversity 4 EffortUniversity 5 EffortUniversity 6 EffortUniversity 7 EffortUniversity 8 EffortUniversity 9 Effort
University 10 EffortUniversity 11 EffortUniversity 12 Effort
University Sub-Total1
STFC Lab Costs
RAL
Daresbury
STFC Lab Sub-Total
Equipment1
Travel
Other Directly Allocated costs (eg consumables)
University Estates CostsUniversity Indirect costs
Total (Excluding VAT and WA)
Working allowanceVAT
Total (including VAT & WA)
Contingency (Held by STFC)
1 Excluding Working Allowance and VAT
* The University staff effort recorded in this table should be the 80% amount STFC pays, including academic time
Use of columns:(1) = The amount approved by STFC (1a) = This column should be used to show any virements between headings, for example when Working Allowance is used, the amount should appear as a debit in the WA row and then credited to the relevant row(2) = The actual spend in previous financial years, by year (3) = The actual spend in the current financial year up to the most recent quarter(4) = The total projected spend for the current financial year, including any expenditure so far (ie actual spend this year plus predictions of remaining spend this year)(5) = Projected spend for the remaining years(6) = The actual spend so far(7) = Projected spend over the whole duration of the project (ie actual spend so far plus predictions of remaining spend to project completion)The variance columns show the difference between the actual and projected amounts and the approved amount.
Latest estimate of future requirement (5)
Example: Finance Summary (all figures in £k)
Total VarianceCurrent year 2009/10Actual spend in previous years (2)
All for 1st column.Similar spreadsheet generated for columns 4 and 5For 8 work-packagesCheck introduced to allow comparison with actual grant figures
![Page 7: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/7.jpg)
Yellow – students (no estate or direct costs)
Purple – funded through rolling grant
Blue squares used as link to master spreadsheet
Green – input values that may be changed with plan
Linked back to different Salary/estates/Indirect costs spreadsheet to allow for changes to these costs from prediction.
![Page 8: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/8.jpg)
Column 4 originally generated from previous spreadsheet to compare estimated expenditure with actual - misunderstanding.
![Page 9: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/9.jpg)
Committed costs – Money accounted for but not yet spent –misunderstanding of use of column 4 allowed error to be compounded.
Insufficient cross-checking meant error undetected.
![Page 10: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/10.jpg)
cross-checking added to Workpackage spreadsheets
Spreadsheet sums vertically
Crosscheck sums horizontally
![Page 11: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/11.jpg)
cross-checking added to Workpackage spreadsheets
Level of checking within the spreadsheets been improved
![Page 12: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/12.jpg)
![Page 13: 08/0909/1010/1111/1212/13 Electronics (Digitisers) Current£91k£23k£69k Requested£91k£91k Mechanics Current£50k£45k Requested£50k£10k£35k](https://reader036.vdocuments.us/reader036/viewer/2022070409/56649ea25503460f94ba5942/html5/thumbnails/13.jpg)
Approved Transfers
(excluding contingency)
Actual spend to Sept 09
Projected spend this
year
Actual spend (2+3)
Projected spend
(2+4+5)
Actual (6-1-1a)
Projected (7-1-1a)
(1) (1a) separate columns
for each FY (3) (4) 2010/11 2011/12 2012/13 (6) (7)
University Staff Effort Costs*University 1 EffortUniversity 2 EffortUniversity 3 EffortUniversity 4 EffortUniversity 5 EffortUniversity 6 EffortUniversity 7 EffortUniversity 8 EffortUniversity 9 Effort
University 10 EffortUniversity 11 EffortUniversity 12 Effort
University Sub-Total1
STFC Lab Costs
RAL
Daresbury
STFC Lab Sub-Total
Equipment1
Travel
Other Directly Allocated costs (eg consumables)
University Estates CostsUniversity Indirect costs
Total (Excluding VAT and WA)
Working allowanceVAT
Total (including VAT & WA)
Contingency (Held by STFC)
1 Excluding Working Allowance and VAT
* The University staff effort recorded in this table should be the 80% amount STFC pays, including academic time
Use of columns:(1) = The amount approved by STFC (1a) = This column should be used to show any virements between headings, for example when Working Allowance is used, the amount should appear as a debit in the WA row and then credited to the relevant row(2) = The actual spend in previous financial years, by year (3) = The actual spend in the current financial year up to the most recent quarter(4) = The total projected spend for the current financial year, including any expenditure so far (ie actual spend this year plus predictions of remaining spend this year)(5) = Projected spend for the remaining years(6) = The actual spend so far(7) = Projected spend over the whole duration of the project (ie actual spend so far plus predictions of remaining spend to project completion)The variance columns show the difference between the actual and projected amounts and the approved amount.
Latest estimate of future requirement (5)
Example: Finance Summary (all figures in £k)
Total VarianceCurrent year 2009/10Actual spend in previous years (2)
VAT why required? – Not claimed back
15% - 17.5%To what categories should VAT be applied?Equipment/Directly Allocated costs/Travel