Appendix B - CWNS Excel Annotation and Needs Calculation Workbook_June 2021

Clean Watersheds Needs Survey (CWNS) (Reinstatement)

Appendix B - CWNS Excel Annotation and Needs Calculation Workbook_June 2021

OMB: 2040-0050

Document [pdf]
Download: pdf | pdf
Appendix 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 Typeapplication/pdf
File TitleCWNS Excel Annotation and Needs Calculation Workbook.xlsx
AuthorTStout
File Modified2021-05-27
File Created2021-05-27

© 2024 OMB.report | Privacy Policy