Audit Sampling Adjustments: Target vs. Actual Samples|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi All,

I'm looking for help with an audit sampling process that involves adjusting deficits across priorities (P1, P2, P3, P4) based on available extra cases in subsequent priorities. The goal is to ensure the sampling does not exceed the available actual cases while aligning as closely as possible to the target. Here's the logic:

1. If there's a deficit in P1 and extra cases in P2, the deficit should be covered from the extra cases in P2's actual.
2. If there's a deficit in P2 and extra cases in P3, the deficit should be covered from the extra cases in P3's actual.
3. If there's a deficit in P3 and extra cases in P4, the deficit should be covered from the extra cases in P4's actual.
4. If there's a deficit in P4, it should be covered from any extra cases in P1, P2, or P3 (in that order).

Important Notes:

• The sampling total across all priorities must not exceed the total of the target.
• The sampling total must not exceed the actual cases available in each priority.
• If the actual cases are less than or equal to the target, then the actual cases should be assigned to the sampling.
• The deficit should be adjusted top-down, meaning if there's extra in a lower priority, it should be used to cover a deficit in a higher priority.
• Sampling cannot be negative, so if the actual cases are zero for a priority, the sampling should also be zero.

I would appreciate any solutions, formulas, or VBA approaches to handle this efficiently. Thanks in advance!