Week 1 Review Data Packs against Change Log

[achafetz]

Ensure country teams are using Data Packs that reflect any necessary adjustments from the Change Log around formula fixes.

• [x] Botswana (received)
• [x] Cameroon (received)
• [x] DRC (received)
• [x] Lesotho (received)
• [x] Namibia (received)
• [x] Nigeria (received)
• [x] South Sudan (received)
• [x] Swaziland (received)
• [x] Uganda (received)
• [x] Zambia (received)
• [x] Zimbabwe (received)

repeated for week 2 in #64

[achafetz]

Best practice would be to copy formulas out of template. Best option would be to copy formulas out of template (find and replace before) (find and replace = with '= to make formula text) and paste into country's tool to compare.

Directions

• Copy Target Calculation tab formulas from row 7 into row 5 of the DP to check.
• Do a find and replace with the = to '= in row 7 of the DP to check
• Add a formula to row 8 (overwriting the data there) that compares row 5 (the formula from the template) to row 7 (the formula in the data) C5=C7. Note anywhere that this is not the case.
• In addition to this check, make sure you see if 20 and 22 from the Change Log are complete.

Submitted Data Packs located on PEPFAR SharePoint.

[shapaklyak]

@achafetz fyi, the version for Botswana that is up there does not include changes to OVC and PP_PREV at this time.

[kschlenker]

@achafetz the Uganda datapack is completed through #19. I had not seen #20 or #21 (I had already fixed the trends across time tab for 21 but not seen the SNU tab issue). What are next steps? Should I reach out to the SI Advisor (Nadia) to have her fix issues or is onsite helpdesk team working directly with people?

[achafetz]

Reviewed Nigeria. Only potential issue is with OVC_SERV not including Graduated in the FY19 OVC_SERV calculation. Looking into this as it may be an "issue" with other countries. Checking #14

[achafetz]

@kschlenker, just spoke Nadia a few minutes ago prior to seeing this update. She is planning on sending me their DP in a bit, so if you wouldn't mind flagging this with her, that would be great. Thanks!

@shapaklyak, thanks for checking Bots. Does JRoff/team know that this is still incomplete?

[achafetz]

Reviewed Nigeria. Only potential issue is with OVC_SERV not including Graduated in the FY19 OVC_SERV calculation. Looking into this as it may be an "issue" with other countries. Checking #14

Update --> #14 related to IM allocation, not actual calculation in Targeting tab.

@shapaklyak, any mention about taking Graduated out of the FY19 OVC_SERV Target?

[shapaklyak]

@achafetz JRoff knows that this is incomplete - i spoke w/him yesterday. No mention about graduated yet for FY19 targets. OVC discussion scheduled for this afternoon.

[shapaklyak]

@kschlenker take a look at this when you update your OUs datapack (from OVC dash).

[achafetz]

Reviewed Nigeria. Only potential issue is with OVC_SERV not including Graduated in the FY19 OVC_SERV calculation. Looking into this as it may be an "issue" with other countries. Checking #14

Update --> #14 related to IM allocation, not actual calculation in Targeting tab.

@shapaklyak, any mention about taking Graduated out of the FY19 OVC_SERV Target?

Spoke with K.Roha and it has to do with their programming requirements. Any OVC that graduates must be replaced 1 to 1. QED, the OVC active at the beginning of the year will equal the number at the end. This makes sense, but I'm wondering how this will work out in reporting. I'm not fully convinced this will work as a target option.

[achafetz]

Botswana is okay, but

• hard coded VMMC targets in manually. OK, but might be good to clarify method
• both retention rate assumptions are set to 95%.
• need to fix SNU Targets PMTCT_ART (column U), adding in look up. Change Log 20

[achafetz]

DRC - OK

• TX_NEW Target is hard coded in
• TX_NET_NEW <15 has an alternative formula that should be documented
  • =CX9-(CL90.05)-(CX90.1) --> [TX_NEW <15] - ([FY18 Expected TX_CURR] 5%) - ([TX_NEW <15] 10%)
  • TX_NEW <15 Target is hard coded in
  • VMMC Expected Coverage and Target Coverage formula not fix (Change Log 10) BUT no VMMC in DRC
  • need to fix SNU Targets PMTCT_ART (column U), adding in look up. Change Log 20

[hessionra]

Hi @achafetz assigned @anu87 and @aliyahwakil to help with this!

[achafetz]

@jb3436, I updated the directions in the second post. Could we get some HQ help to QC the outstanding DPs? Thanks so much!!!

[hessionra]

@achafetz hi! I added Anu/Aliyah to help with this. Do you think you'll need any additional people?

[aliyahwakil]

@anu87 and I have received and are reviewing

[aliyahwakil]

@achafetz @jb3436

For South Sudan change log adjustment 22, there are data in FY19 Target Allocation section (DP7:HX7). I lose these data when I attempt to copy DP8:HX8 formulas (assuming the formula is correct) into (DP7:HX7).

What should I do???

[aliyahwakil]

I reviewed Cameroon, Lesotho, Namibia, and South Sudan while Anu the last 4. The ou's I reviewed had instances where formulas in Target Calculation tab did not match that of the template. The attached photo shows the columns where formulas didn't match, I also posted the excel to sharepoint Support Requests and Tracker : Datapack. I reviewed Change log issues 20 and 22 for all and posted the updated datapacks to sharepoint Support Requests and Tracker : Datapack as well. I still have a question about SS DP which is posted above.

SharePoint link HERE

[jb3436]

@kschlenker @shapaklyak FYI Nigeria is updating their datapack to include OVC active and graduated breakdown. I shared this info graphic so they understand that they need to target for both active and graduated and programmatically account for new, but new is not targeted for. I am creating a separate ticket and will reference later.

[achafetz]

From my review of your review, we need to alter the following countries to the following things:

• South Sudan
  • change log adjustment 22
• Namibia
  • ask about FY18 VMMC Target (EQ) change to 15-29 target and Central Fund.
  • ask about 50% x VMMC Target used for HTS (FS)
• Lesotho
  • confirm they wan their FY19 VMMC_CIRC target to be VMMC_CIRC 15-29 (EX)
• Cameroon
  • Need to fix Change Log 10 - VMMC Coverage rates (EZ, FA)

@anu87, were there any major findings from yours? output like below was useful.

[anu87]

Here's my review:

[achafetz]

Great! Thanks, @anu87

[jb3436]

Closing this for week one now that there is a week 2 ticket.

[achafetz]

reopening since I still need to review the reviews of 3 more countries.

[achafetz]

Reviewed the remaining OUs and these are the only comments. Reached out to SI advisors.

• Swazi
  • hard coded FY18 TX_CURR targets (Z) - why?
  • TB_STAT FY19 target changed to be based off of FY18 instead of FY17 (DU)
  • FY19 VMMC_CIRC targets hard coded in (EX)
  • FY19 VMMC_CIRC targets for HTS hard coded in (FH) - misaligned to EX
• Uganda
  • Patients who should get a VL (Newly Eligible + FY18 TX_CURR) pulling from FY19 TX_CURR rather than FY19 TX_NEW + FY18 expected TX_CURR
  • TB_STAT yield (DG) is linked to another workbook. Risk: Moderate/High
  • TB_STAT Target calculation done in reverse order (DX, EA), but okay
  • OVC_SERV Tgarget (GX) hard coded in, but okay