Download:
pdf |
pdfAppendix B:
CWNS Excel Annotation and Needs
Calculation Workbook
CWNS Excel Annotation and Needs Calculation Workbook
This workbook allows state coordinators to annotate documents, as well as calculate needs by
document and category. State coordinators should enter document information first, then enter
need information by document. The "Outputs" sheet will automatically calculate values that will
need to be entered into the CWNS Data Entry Portal.
Table of Contents
Worksheet Name
Step 1
Documents
Step 2
Needs By Document
Step 3
Outputs
Description
This worksheet is where a state coordinator will enter information for
all the documents and costs being annotated. The state coordinator
must enter document information for each cost included in the "Cost
by Document" section.
Here a state coordinator can enter project information, page
numbers, and totals for each cost in the document. The next section
"outputs" will automatically sum the informaiton by cost and
category for entry into the DEP.
Total costs from each document are summed by category and
document. State coordinators will need to input these ouput values
into the DEP and upload this spreadsheet as Document Type 96.
All inputs are indicated in light green
Spreadsheet calculations and formulas indicated in orange. Do not edit.
* indicates required for the form to work.
(+) incates required to use this form for annotation
Instructions: Enter document attribute information for each document you are
using for this CWNS ID.
* indicates required for the form to work.
(+) incates required to use this form for annotation
Input * (+)
CWNS ID
12345678
* please enter CWNS ID
Do not edit.
Input * (+)
Document
Number
Document Title
Input (+)
Title Pg. #
Input (+)
Author
1
Maryville CIP
1, 2nd line City of Maryville
2
Ohio IUP
1, title page Ohio SRF Agency
3
4
5
6
7
8
9
10
Input (+) Input (+)
Author
Pg. #
Published Date
1, 1st line 1/1/2020
1, title
1/1/2020
page
Input (+)
Input (+)
Published Base Month/Year
Date Pg #. of Cost Information
1, bottom May 2019
1, title
May 2019
page
Input (+)
Base Month/Year
of Cost
Information Pg. #
2, footnote
15, last page of
notes
Input
Notes
Instructions: Provide the details necessary for reviewers to locate information in submitted documentation.
* indicates required for the form to work.
(+) incates required to use this form for annotation
Input *
Document
Number
1
Prepopulated from Step 1
Document Title
Input * (+)
Input (+)
Category
Input (+)
Project Description (Name
Project
of Identifier)
Description Page #
2
Input * (+)
Input (+) Input (+)
Cost
Cost Page
#
$ 10,000,000
2
Comments on project or location of relevant information within document
Maryville CIP
II ‐ Advanced Wastewater Treatment New plant upgrade…
Table 1 includes project description and costs
1
Maryville CIP
I ‐ Secondary Wastewater Treatment New plant upgrade…
3
$ 33,600
3
Table 2 provides project description and costs
2
Ohio IUP
II ‐ Advanced Wastewater Treatment 10,000 feet of pipe
13
$ 4,259,000
42
Description in narrative in first paragraph; The tables on 42 provides costs.
Instructions
Needs Category for CWNS ID 12345678
Category
I ‐ Secondary Wastewater Treatment
II ‐ Advanced Wastewater Treatment
III‐A ‐ Infiltration/Inflow (I/I) Correction
III‐B ‐ Sewer Replacement/ Rehabilitation
IV‐A ‐ New Collector Sewers and Appurtenances
IV‐B ‐ New Interceptor Sewers and Appurtenances
V ‐ Combined Sewer Overflow (CSO) Correction
VI‐A ‐ Gray Infrastructure
VI‐B ‐ Green Infrastructure
VI‐C ‐ General Stormwater Management
VII‐A ‐ NPS Control: Agriculture (Cropland)
VII‐B ‐ NPS Control: Agriculture (Animals)
VII‐C ‐ NPS Control: Silviculture
VII‐E ‐ NPS Control: Ground Water Protection
(Unknown Source)
VII‐F ‐ NPS Control: Marinas
VII‐G ‐ NPS Control: Resource Extraction
VII‐H ‐ NPS Control: Brownfields/Superfund
VII‐I ‐ NPS Control: Storage Tanks
VII‐J ‐ NPS Control: Sanitary Landfills
VII‐K ‐ NPS Control: Hydromodification
VII‐M ‐ NPS Control: Other Estuary Management
Activities
X ‐ Water Reuse
XII ‐ Decentralized Wastewater Treatment Systems
XIV ‐ Desalination
Grand Total
Check TRUE
Total Needs
$ 33,600
$ 14,259,000
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
1
Maryville CIP
$ 33,600
$ 10,000,000
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
2
Ohio IUP
$ ‐
$ 4,259,000
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$ ‐
$14,292,600
3
4
5
6
7
8
9
10
| File Type | application/pdf |
| File Title | CWNS Excel Annotation and Needs Calculation Workbook.xlsx |
| Author | TStout |
| File Modified | 2021-05-27 |
| File Created | 2021-05-27 |