Contents Show
The FNS-798 Statement Spreadsheet (Output) is a WIC Financial Management and Participation report that contains the counts of participants that received WIC benefits for the reported months. The counts are grouped by category (pregnant, breastfeeding, non-breastfeeding, child, or infant). The participant is counted in each month that they were issued a food instrument, regardless of the status of the food instrument (paid, voided or outstanding).
The FNS-798 Statement Spreadsheet (Output) consists of multiple worksheets. All worksheets within the spreadsheet are generated. Click the links below for additional information:
The report is generated when the OK button is clicked on the Generate FNS-798 Statement Spreadsheet screen.
Selected cells are populated for the start of the spreadsheet. The user may update cells as needed. The spreadsheet is as of the day the spreadsheet is requested. The spreadsheet template will be protected and is not is not intended to be modified by the user. Changes to the template may require programming changes.
The fiscal year to generate the spreadsheet for is selected by the user on the "Generate screen ". The data to be included is based the month selected by the user on the "Generate screen ". This month is also printed in the heading of the spreadsheet.
Most columns on this report are reported for each of the twelve months of the selected fiscal year.
Origin of Data Element
This value is calculated one of three ways. Each month is calculated.
Actual Redemption
For closeout months, this value is the total of the PAIDAMOUNT column of the FOODINSTRUMENT table plus the total of the PAIDAMOUNT column of the FOODINSTRUMENTREDEEMED table where one of the following selection criteria is met.
The PARTICIPATIONDATE is equal to the reported month and the PAIDDATE is greater than zero.
The PARTICIPATIONDATE is equal zero and the PAIDDATE is within the reported month.
Adjusted Redemption/Obligation
For 30-day and current months, this value is the sum of the paid amounts and the obligation amounts on outstanding food instruments. The sum of the paid amounts is the total of the PaidAmount column of the FoodInstrument table plus the total of the PaidAmount column of the FoodInstrumentItemRedeemed table where one of the following selection criteria is met.
The PARTICIPATIONDATE is equal to the reported month and the PAIDDATE is greater than zero.
The PARTICIPATIONDATE is equal zero and the PAIDDATE is within the reported month.
The sum of the obligation amounts is the total of the OBLIGATIONAMOUNT column of the FOODINSTRUMENT table where all of the following selection criteria are met.
The PARTICIPATIONDATE is equal to the reported month.
The PAIDDATE is equal to zero.
The EXPIRATIONDATE is greater than or equal to the current date.
The VOIDDATE is zero.
Estimated Obligation
For future months, this value is calculated based on the value of the last closeout month for the line and the variant values on the FUNDOBLIGATIONVARIANT table. The value of the last closeout month (for this line) is multiplied times the REDEMPTIONVARIANTVALUE column. That product is multiplied times the CASELOADVARIANTVALUE column. That product is multiplied times the INFLATIONVARIANTVALUE column.
Add Actual Breast Pump Expense:
The breast pump expense is added after the food obligation value is calculated and before the variants are applied for estimated obligations. This value is calculated for orders received in the selected fiscal year that were flagged with "use food funds". Multiply the ESTIMATEDCOSTEACH column of the ORDERITEM table by the QUANTITY column of the associated ORDERITEMRECEIVED table and accumulate by the RECEIVEDDATE month and year of the ORDERITEMRECEIVED table. Months up to and including the requested months are calculated.
Format
This is a nine-digit number.
Origin of Data Element
This value is calculated one of two ways. Each month is calculated.
Actual
For closeout months, this value is the total of the billed rebated amount for all rebate products.
For non-EBT clinics (Electronic Benefit Transfer), the Units and REBATEPERUNIT columns of the REBATEINVOICEITEM table are multiplied for all rows where the ISSUEYEAR and ISSUEMONTH columns are for a closeout month. The products of the rows are totaled.
For EBT clinics, the values from the REDEEMEDQUANTITY column of the FOODINSTRUMENTITEMREDEEMED table are totaled for all rows where the PURCHASEDATE is in a closeout month.
The products of both calculations are summarized.
Estimated
For 30-day, current and future months, this value is calculated based on the value of last the closeout month for the line and two of the variant values on the FUNDOBLIGATIONVARIANT table. The value of the last closeout month (for this line) is multiplied times the REDEMPTIONVARIANTVALUE column. That product is multiplied times the CASELOADVARIANTVALUE column.
Format
This is a nine-digit number.
Origin of Data Element
This value is calculated by subtracting line 2 (Estimated Rebates) from line 1 (Adjusted Gross Obligations). Each month is calculated.
Format
This is a nine-digit number.
Origin of Data Element
This value is the total of the PAIDAMOUNT column of the FOODINSTRUMENT table plus the total of the PAIDAMOUNT column of the FOODINSTRUMENTITEMREDEEMED table for rows where one of the following selection criteria is met. Months up to and including the requested months are calculated.
The PARTICIPATIONDATE is equal to the reported month and the PAIDDATE is greater than zero.
The PARTICIPATIONDATE is equal zero and the PAIDDATE is within the reported month.
Add Actual Breast Pump Expense:
Same as Line 1 above.
The breast pump expense is added after the food instrument redemption value is calculated. This value is calculated for orders received in the selected fiscal year that were flagged with "use food funds". Multiply the ESTIMATEDCOSTEACH column of the ORDERITEM table by the QUANTITY column of the associated ORDERITEMRECEIVED table and accumulate by the RECEIVEDDATE month and year of the ORDERITEMRECEIVED table. Months up to and including the requested months are calculated.
Format
This is a nine-digit number.
Origin of Data Element
This value is calculated for the reporting month of September only and is the value of the food instruments issued for the month of September that have not been paid.
This is the sum of the Obligation-Amount attribute of food instruments with the following selection criteria.
The PARTICIPATIONDATE column of the FOODINSTRUMENTSET table is September of the reported year.
The PAIDDATE column of the FOODINSTRUMENT table = zero.
The VOIDDATE column of the FOODINSTRUMENT table = zero.
The EXPIRATIONDATE column of the FOODINSTRUMENT table is >= the current system date.
Format
This is a nine-digit number.
Origin of Data Element
This value is calculated by adding line 4 (Gross Outlays) and line 5 (Un-liquidated Obligations). Each month is calculated.
Format
This is a nine-digit number.
Origin of Data Element
For closeout months, this value is the total of the billed rebated amount for rebate products excluding milk and cheese. These products have a base food category value of "A" (formula), "I" (Infant cereal), "M" (Infant Fruits and Vegetables), and "O" (Infant Meat).
For non-EBT clinics (Electronic Benefit Transfer), the REBATEPERUNIT and UNITS columns from the REBATEINVOICEITEM table are multiplied for all rows where the ISSUEYEAR and ISSUEMONTH columns are for a reported month. The products of the rows are totaled. Each month up to and including the requested month is calculated.
For EBT clinics, the value from the REDEEMEDQUANTITY column of the FOODINSTRUMENTITEMREDEEMED table is totaled where the PURCHASEDATE column is for a reported month. Each month up to and including the requested month is calculated.
The products of both calculations are summarized.
Format
This is a nine-digit number.
Origin of Data Element
The user manually enters this value.
Format
This is a nine-digit number.
Origin of Data Element
The user manually enters this value.
Format
This is a nine-digit number.
Origin of Data Element
The user manually enters this value.
Format
This is a nine-digit number.
Origin of Data Element
For closeout months, this value is the total of the billed rebated amount for dairy rebate products. These products have a base food category value of "B" (milk) or "E" (cheese).
For non-EBT clinics (Electronic Benefit Transfer), the REBATEPERUNIT and UNITS columns from the REBATEINVOICEITEM table are multiplied for all rows where the ISSUEYEAR and ISSUEMONTH columns are for a reported month. The products of the rows are totaled. Each month up to and including the requested month is calculated.
For EBT clinics, the values from the REDEEMEDQUANTITY column of the FOODINSTRUMENTITEMREDEEMED table are totaled where the PURCHASEDATE column is for a reported month. Each month up to and including the requested month is calculated.
The products of both calculations are summarized.
Format
This is a nine-digit number.
Origin of Data Element
This value is calculated by adding the following lines.
Line 7 (Rebates Billed)
Line 8 (Program Income)
Line 9 (Post Payment Vendor Collections)
Line 10 (Participant Collections)
Line 11 (Other Credits)
This total is then subtracted from line 6 (Gross Outlays and Un-liquidated Obligations). Each month is calculated.
Format
This is a nine-digit number.
Origin of Data Element
For reported months that are less than or equal to the current closeout month, this value is a "Y". For reported months that are greater than the current closeout month, this value is an "N". Each month is calculated.
Format
This is one-character value.
Origin of Data Element
If the month is closed out, this value is the same as the value in line 12 (Net Federal Outlays & Un-Liquidated). If the month is not closed out, this value is the same as the value in line 3 (Net Federal Obligations). Each month is valued.
Format
This is a nine-digit number.
Origin of Data Element
This value is the count of WIC participants with a category of pregnant ("P"). Participants are counted in each month that they were issued a food instrument. They are counted in one category total per month, based on their category on the last food instrument issued to them for the reported month. Only closeout months are reported.
Format
This is a seven-digit number.
Origin of Data Element
This value is the count of WIC participants with a category of breastfeeding ("B"). Participants are counted in each month that they were issued a food instrument. They are counted in one category total per month, based on their category on the last food instrument issued to them for the reported month. Only closeout months are reported.
Format
This is a seven-digit number.
Origin of Data Element
This value is the count of WIC participants with a category of postpartum non-breastfeeding ("N"). Participants are counted in each month that they were issued a food instrument. They are counted in one category total per month, based on their category on the last food instrument issued to them for the reported month. Only closeout months are reported.
Format
This is a seven-digit number.
Origin of Data Element
This value is calculated by adding Federal Participation line 15a (Women Pregnant), line 15b (Women Breastfeeding) and line 15c (Women Postpartum). Only closeout months are reported.
Format
This is a seven-digit number.
Origin of Data Element
This value is the count of WIC participants with a category of infant ("I" or "F"). Participants are counted in each month that they were issued a food instrument. They are counted in one category total per month, based on their category on the last food instrument issued to them for the reported month. Only closeout months are reported.
Format
This is a seven-digit number.
Origin of Data Element
This value is the count of WIC participants with a category of child ("C"). Participants are counted in each month that they were issued a food instrument. They are counted in one category total per month, based on their category on the last food instrument issued to them for the reported month. Only closeout months are reported.
Format
This is a seven-digit number.
Origin of Data Element
This value is calculated by adding Federal Participation line 15d (Women Total), line 16 (Infants) and line 17 (Children). Only closeout months are reported.
Format
This is a seven-digit number.
Origin of Data Element
The user manually enters this value.
Format
This is a nine-digit number.
Origin of Data Element
The user manually enters this value.
Format
This is a nine-digit number.
Origin of Data Element
This value is calculated by adding Year-to-Date NSA costs line 19 (Gross Outlays) and line 20 (Un-liquidated Obligations).
Format
This is a nine-digit number.
Origin of Data Element
The user manually enters this value.
Format
This is a nine-digit number.
Origin of Data Element
The user manually enters this value.
Format
This is a nine-digit number.
Origin of Data Element
The user manually enters this value.
Format
This is a nine-digit number.
Origin of Data Element
The user manually enters this value.
Format
This is a nine-digit number.
Origin of Data Element
This value is calculated by adding the following Year-to-Date NAS Cost lines.
line 22 (Program Income)
line 23 (Post Payment Vendor Collections)
line 24 (Participant Collections)
line 25 (Other Credits)
This total is then subtracted from line 21 (Gross Outlays & Un-liquidated Obligations).
Format
This is a nine-digit number.
Origin of Data Element
The user manually enters this value.
Format
This is a nine-digit number.
Origin of Data Element
This value is calculated by adding Year-to-Date NSA Cost line 26 (Net Federal Outlays & Un-liquidated) and line 27 (Est. Future Month(s) Obligations).
Format
This is a nine-digit number.
Each cell in the spreadsheet has its own definition of how to collect the data.
|
· |
October |
November |
December |
January |
February |
March |
April |
|
· |
Close | 30 | Issue |
+1 |
+2 |
+3 |
+4 |
|
Food Obligation Estimates |
· |
· |
· |
· |
· |
· |
· |
|
Line 1. Adjusted Gross Obligation |
Actual Gross Redeemed + Breast Pump Total |
Actual Redeemed - Original Estimate + Net remaining unredeemed + Breast Pump |
Actual Redeemed - Original Estimate + Net remaining unredeemed + Breast Pump |
Net Estimated |
Net Estimated |
Net Estimated |
Net Estimated |
|
Line 2. Estimated Rebate |
Rebate Billed by Issue Mo (Total Actual Billed) |
Net Estimated |
Net Estimated |
Net Estimated |
Net Estimate |
Net Estimated |
Net Estimated |
|
Line 3. Net Federal Obligations |
Line 1 - Line 2 |
Line 1 - Line 2 |
Line 1 - Line 2 |
Line 1 - Line 2 |
Line 1 - Line 2 |
Line 1 - Line 2 |
Line 1 - Line 2 |
|
Actual Food Outlays |
· |
· |
· |
· |
· |
· |
· |
|
Line 4. Gross Outlays |
Actual Redeemed + Breast Pump + Warehouse Costs |
Actual Redeemed + Breast Pump |
Actual Redeemed + Breast Pump |
Breast Pump |
Breast Pump |
Breast Pump |
Breast Pump |
|
Line 5. Unliquidated Obligations |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
Line 6. Gross Outlays and Unliquidated Obligations |
Line 4 + Line 5 |
Line 4 + Line 5 |
Line 4 + Line 5 |
Line 4 + Line 5 |
Line 4 + Line 5 |
Line 4 + Line 5 |
Line 4 + Line 5 |
|
Line 7. Rebates Billed |
Rebate Billed by Issue Mo (Formula and Cereal) |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
Line 8. Program Income |
Rebate Billed by Issue Mo (Juice) |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
Line 9. Post-Payment Vendor Collections |
Manual Entry |
Manual Entry |
Manual Entry |
Manual Entry |
Manual Entry |
Manual Entry |
Manual Entry |
|
Line 10. Participant Collections |
Manual Entry |
Manual Entry |
Manual Entry |
Manual Entry |
Manual Entry |
Manual Entry |
Manual Entry |
|
Line 11. Other Credits |
Rebate Billed by Issue Mo (Dairy) |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
Line 12. Net Federal Outlays and Unliquidated Obligations |
Line 6 - Line 7 - Line 8 - Line 9 - Line 10 - Line 11 |
Line 6 - Line 7 - Line 8 - Line 9 - Line 10 - Line 11 |
Line 6 - Line 7 - Line 8 - Line 9 - Line 10 - Line 11 |
Line 6 - Line 7 - Line 8 - Line 9 - Line 10 - Line 11 |
Line 6 - Line 7 - Line 8 - Line 9 - Line 10 - Line 11 |
Line 6 - Line 7 - Line 8 - Line 9 - Line 10 - Line 11 |
Line 6 - Line 7 - Line 8 - Line 9 - Line 10 - Line 11 |
|
Line 13. Month Closed Out (Y/N) |
Y |
N |
N |
N |
N |
N |
N |
|
Line 14. Annual Net Federal Cost |
If closed, Line 12, else Line 3 |
If closed, Line 12, else Line 3 |
If closed, Line 12, else Line 3 |
If closed, Line 12, else Line 3 |
If closed, Line 12, else Line 3 |
If closed, Line 12, else Line 3 |
If closed, Line 12, else Line 3 |
|
Federal Participation: |
· |
· |
· |
· |
· |
· |
· |
|
15a. Women Pregnant |
# with issued FI |
# with issued FI |
# with issued FI |
N/A |
N/A |
N/A |
N/A |
|
15b. Women Breastfeeding |
# with issued FI |
# with issued FI |
# with issued FI |
N/A |
N/A |
N/A |
N/A |
|
15c. Women Postpartum |
# with issued FI |
# with issued FI |
# with issued FI |
N/A |
N/A |
N/A |
N/A |
|
15d. Total Women |
Lines 15a through 15c |
Lines 15a through 15c |
Lines 15a through 15c |
N/A |
N/A |
N/A |
N/A |
|
16. Infants |
# with issued FI |
# with issued FI |
# with issued FI |
N/A |
N/A |
N/A |
N/A |
|
17. Children |
# with issued FI |
# with issued FI |
# with issued FI |
N/A |
N/A |
N/A |
N/A |
|
18. Total |
Lines 15d through 17 |
Lines 15d through 17 |
Lines 15d through 17 |
N/A |
N/A |
N/A |
N/A |
·
|
Year-to-Date NSA Costs |
YTD Total |
|
19. Gross Outlays |
Manual Entry (Total Admin Costs - Program Income) |
|
20. Unliquidated Obligations |
Manual Entry from Financials |
|
21. Gross Outlays & Unliquidated Obligations |
Line 19 + Line 20 |
|
22. Program Income |
Manual Entry from Financials |
|
23. Post-payment Vendor collections |
Manual Entry (Fees & Penalties Collected from Vendor) |
|
24. Participant Collections |
Manual Entry (Fraudulent Recoveries) |
|
25. Other Credits |
Manual Entry (Sale of Assets, etc.) |
|
26. Net Federal Outlays & Unliquidated |
Line 21 - Line 22 - Line 23 - Line 24 - Line 25 |
|
27. Estimate of Future Month(s) Obligation |
Manual Entry (Total Grant - Line 26) |
|
28. Annual Net Federal Costs |
Line 26 + Line 27 |
|
Software Version: 2.40.00
