Методика учета прогресса проектной разработки в таблице оценки: агрегация % готовности и часов по уровням исполнитель -> роль -> подзадача -> задача -> контракт, метрики освоенного объема (EVM: EV, PV, CV, SV, CPI, SPI, EAC, VAC, TCPI), обработка незапланированных доработок (overhead) и недельные срезы с плановой кривой, прогнозом даты завершения и графиками. Расчеты - на голых формулах Google Sheets, без скриптов.
EVM and weighted-progress tracking methodology for software delivery, implemented in plain Google Sheets formulas.
Живой пример с формулами и тестовыми данными: Google-таблица (листы Прогресс, Этапы, История).
В fix-price разработке нужно в любой момент честно ответить заказчику: сколько scope закрыто, идем ли с опережением или перерасходом, сколько overhead съедает бюджет, когда закончим. Методика дает это на одной таблице - взвешенный по трудоемкости прогресс, отделение overhead от планового scope, стандартный EVM поверх тех же данных и историю срезов, из которой строятся графики.
PM / delivery / руководителям проектов, которым нужен прозрачный, защитимый перед заказчиком учет прогресса без отдельного софта. Экономистом быть не нужно: в разделе 5 есть шпаргалка по терминам EVM.
Оценка в проекте планируется на роль (PM, AR, SA, BE, FE, QA, DO) - одна колонка на роль, без привязки к конкретному исполнителю. Факт и Прогресс могут вестись на исполнителя: на одной роли может быть один или несколько человек, у каждого своя подколонка часов и %. Формулы должны работать одинаково при любом числе исполнителей на роли и не переписываться при появлении нового исполнителя.
Решение - двухуровневая шапка плюс агрегатные блоки по ролям:
- Row 2 = роль (PM, AR, SA, ...) - ключ для агрегации.
- Row 3 = исполнитель (PM, AR, SA2, SA3, ...) - идентификатор человека или группы.
- В блоках "Оценка" и в агрегатных блоках "Факт по ролям" / "Прогресс по ролям" Row 2 несет название роли, Row 3 пустая (одна колонка = одна роль).
- В блоках детализации "Факт" и "Прогресс" Row 2 несет роль (повторяется для всех исполнителей этой роли), Row 3 - конкретного исполнителя.
- Все формулы итогов работают только с агрегатными блоками, а не с детализацией напрямую. Детализация - источник данных, агрегаты - буфер для свертки.
Раскладка (на примере таблицы-примера, лист Прогресс)
| Колонки | Блок | Содержимое |
|---|---|---|
| A, B | - | №, Задача |
| C | разделитель | |
| D | Σ оценка | =SUM(E<r>:K<r>) |
| E:K | Оценка | PM, AR, SA, BE, FE, QA, DO (1 колонка на роль) |
| L | разделитель | |
| M | Σ факт | =SUM(N<r>:V<r>) |
| N:V | Факт (детализация) | PM, AR, SA2, SA3, BE, FE, QA2, QA3, DO2 (по подколонке на человека) |
| W | разделитель | |
| X | anchor "Прогресс по исп" | заголовок группы (видим при сворачивании Y:AG) |
| Y:AG | Прогресс (детализация) | %PM, %AR, %SA2, %SA3, %BE, %FE, %QA2, %QA3, %DO2 |
| AH | Проверка | флаг рассогласований Факт↔Прогресс по исполнителям (часы без %, % без часов) |
| AI | разделитель | |
| AJ | Σ Факт по ролям | =SUM(AK<r>:AQ<r>) |
| AK:AQ | Факт по ролям (агрегат) | PM, AR, SA, BE, FE, QA, DO |
| AR | разделитель | |
| AS | Σ Остаток по ролям | =SUM(AT<r>:AZ<r>) |
| AT:AZ | Остаток по ролям (агрегат) | PM, AR, SA, BE, FE, QA, DO (оценка - факт) |
| BA | разделитель | |
| BB | anchor "Прогресс по ролям" | |
| BC:BI | Прогресс по ролям (агрегат) | %PM, %AR, %SA, %BE, %FE, %QA, %DO |
| BJ | Σ% по строке | взвешенное по оценке роли |
| BK | Δ | опережение/отставание в п.п. |
| BL, BM | разделитель | (отделение блока EVM) |
| BN:BS | EVM | EV, CV, CPI, EAC, VAC, TCPI - метод освоенного объема (см. раздел 5) |
Кроме основного листа методика использует два вспомогательных листа (см. раздел 6):
| Лист | Назначение |
|---|---|
Этапы |
базовый план: этапы контракта с датами и объемом часов - источник плановой кривой PV |
История |
недельные срезы: замороженные значения BAC / AC / Σ% плюс производные метрики и графики |
Замечания по раскладке:
- Σ-колонки вынесены в начало блока (D, M, AJ, AS), а не в конец. Это упрощает сворачивание группы детализации (E:K, N:V) - Σ и заголовок блока остаются видимы.
- Anchor-колонки (X, BB) - пустые колонки слева от блоков без Σ. На них висит подпись блока в строке 1; при сворачивании детализации anchor остается виден.
- Σ% (BJ) и Δ (BK) примыкают вплотную к Прогрессу по ролям, без spacer'а - они продолжают логику этого блока (обобщающие метрики строки).
- EVM (BN:BS) отделен двумя пустыми колонками (BL, BM) - визуальная сепарация другой "упаковки" тех же данных.
- Проверка (AH) - служебная колонка-флаг между детализацией Прогресса и агрегатами: на строках-подзадачах сверяет Факт (N:V) и Прогресс (Y:AG) по каждому исполнителю и помечает рассогласование ("часы без %" / "% без часов").
Иерархия строк (на примере таблицы-примера, лист Прогресс):
- Строка 5 - проект/контракт (родитель верхнего уровня) - агрегирует все задачи + Незапланированные.
- Строка 7 - Незапланированные доработки (см. раздел 4).
- Строки 9, 17 - задачи (родители подзадач).
- Строки 10..13 (для задачи 1) и 18..21 (для задачи 2) - подзадачи, базовый уровень: в них вводятся исходные часы Факта и % Прогресса по исполнителям.
В детализации Факта и Прогресса у родителей (задача, проект) ячейки тоже заполняются: Факт по исполнителю - простая сумма по подзадачам, Прогресс по исполнителю - взвешенный по часам факта подзадач. Это нужно, чтобы на уровне родителя было видно вклад каждого человека (а не только агрегат по роли). Агрегатные блоки Факта/Прогресса по ролям на родителях считаются не из детализации, а из подзадач напрямую - см. разделы 2 и 3. Эти две ветки агрегации согласованы по сумме, но Σ% роли на родителе считается через оценку (не через факт), что сохраняет смысл "выполнение относительно плана".
Условные обозначения ниже:
<r>- номер текущей строки.<col>- буквенный адрес текущей колонки в формуле.- В % ячейках формат Процентный (Sheets хранит как 0..1, пользователь вводит "50%").
Это базовый слой, на котором держится вся методика. Для каждой строки подзадачи в агрегатных блоках сворачиваются все подколонки исполнителей данной роли в одну ячейку.
Σ часов факта по роли (агрегат Факт, колонки AK:AQ) - простая сумма часов всех исполнителей роли, с скрытием нулей через LET:
=LET(v; SUMIFS($N<r>:$V<r>; $N$2:$V$2; <col>$2); IF(v = 0; ""; v))
Где $N<r>:$V<r> - детализация факта в текущей строке (с закрепленными колонками N и V, чтобы при копировании формулы вдоль агрегата AK..AQ диапазон не съезжал), $N$2:$V$2 - роли в шапке детализации, <col>$2 - роль текущей агрегатной колонки (для Факта <col> пробегает AK..AQ). LET(v; ...; IF(v=0;"";v)) вычисляет SUMIFS один раз и подставляет в проверку - роли с нулевым часом факта возвращают пустую строку, а не "0", сильно уменьшая визуальный шум.
Остаток по роли (колонки AT:AZ) - оценка минус факт, тот же шаблон с LET:
<col_остаток><r> = =LET(v; <col_оценка><r> - N(<col_факт><r>); IF(v = 0; ""; v))
То есть AT<r> = =LET(v; E<r>-N(AK<r>); IF(v=0;"";v)) (PM), AU<r> = =LET(v; F<r>-N(AL<r>); IF(v=0;"";v)) (AR), и т.д. Функция N(...) нужна, потому что AK<r> может вернуть пустую строку (через LET/IF в Факте) - арифметика с текстом дала бы #VALUE!, а N("") = 0. Знак: положительный остаток - еще есть запас по плану, отрицательный - перерасход на этой роли.
% по роли (агрегат Прогресс, колонки BC:BI) - взвешенное по часам факта среднее % всех исполнителей роли, с теми же фиксацией диапазонов и скрытием нулей:
=LET(v;
IFERROR(
SUMPRODUCT(
($Y$2:$AG$2 = <col>$2) * $Y<r>:$AG<r> * $N<r>:$V<r>
) / SUMIFS($N<r>:$V<r>; $N$2:$V$2; <col>$2);
0
);
IF(v = 0; ""; v)
)
Где <col> пробегает BC..BI. Конструкция LET(v; ...; IF(v=0; ""; v)) вычисляет результат один раз и подставляет в проверку - чище и не повторяет тяжелый SUMPRODUCT дважды. Колонки $Y<r>:$AG<r> и $N<r>:$V<r> закреплены, чтобы формулу можно было копировать вдоль агрегата BC..BI без съезда диапазонов.
Смысл: каждый % исполнителя умножается на часы этого же исполнителя (его "выполненные часы"), сумма делится на общие часы роли. Если у роли часов нет (никто еще не списывал), агрегат = 0 (через IFERROR).
Альтернатива - усреднение AVERAGEIF(...роль...; %...) - дала бы равный вес людям с разной нагрузкой и искажала бы картину. Текущий выбор: пока часов нет - прогресс не идет.
Формулы агрегатов копируются по строкам подзадач. Над уровнем подзадач (на родителях задач и проекта) агрегаты считаются иначе - см. разделы 2 и 3.
Σ оценка:
=SUM(E<r>:K<r>)
Σ факт:
=SUM(N<r>:V<r>)
Σ% по подзадаче (колонка BJ) - взвешенное среднее % по ролям, веса = оценочные часы роли:
BJ<r> = =IFERROR( SUMPRODUCT(BC<r>:BI<r>; E<r>:K<r>) / SUM(E<r>:K<r>); 0 )
Логика: для каждой роли "оценка × %роли" = выполненные часы; сумма по ролям / сумма оценок = доля выполненного. Пустые %роли считаются как 0 (роль еще не начала) - корректно. Роль с оценкой 0 автоматически выпадает из взвешивания.
Δ по подзадаче (колонка BK) - опережение/отставание в процентных пунктах:
BK<r> = =IFERROR( BJ<r> - M<r> / D<r>; 0 )
Где BJ<r> = Σ%, M<r> = Σ факт, D<r> = Σ оценка. Формат - проценты или число с подписью "п.п.".
Родитель задачи (например, строка 9 для задачи 1 с подзадачами 10..13). Заполняется и детализация (N:V, Y:AG), и агрегатные блоки (AK:AQ, AT:AZ, BC:BI, BJ, BK).
Оценка по роли (E:K) - сумма оценок этой роли по подзадачам:
E<R> = =SUM(E<r1>:E<rN>)
И аналогично F<R>..K<R>.
Σ оценка:
D<R> = =SUM(D<r1>:D<rN>)
(Эквивалентно =SUM(E<R>:K<R>).)
Факт по исполнителю (детализация N:V) - простая сумма часов этого исполнителя по подзадачам:
N<R> = =SUM(N<r1>:N<rN>)
...
V<R> = =SUM(V<r1>:V<rN>)
Σ факт:
M<R> = =SUM(M<r1>:M<rN>)
Прогресс по исполнителю (детализация Y:AG) - взвешенный по часам факта подзадач % этого исполнителя:
Y<R> = =IFERROR( SUMPRODUCT(Y<r1>:Y<rN>; N<r1>:N<rN>) / SUM(N<r1>:N<rN>); 0 )
Z<R> = =IFERROR( SUMPRODUCT(Z<r1>:Z<rN>; O<r1>:O<rN>) / SUM(O<r1>:O<rN>); 0 )
...
AG<R> = =IFERROR( SUMPRODUCT(AG<r1>:AG<rN>; V<r1>:V<rN>) / SUM(V<r1>:V<rN>); 0 )
Каждая формула привязана к своей колонке часов исполнителя (Y ↔ N, Z ↔ O, ..., AG ↔ V). Это нужно, чтобы на уровне задачи было видно вклад каждого человека отдельно, а не только агрегат по роли.
Факт по роли (агрегат AK:AQ) - сумма часов этой роли по подзадачам:
AK<R> = =SUM(AK<r1>:AK<rN>)
И аналогично AL<R>..AQ<R>.
Остаток по роли (AT:AZ) - формулы те же, что у подзадачи (LET со скрытием нулей и N(...) оберткой): AT<R> = =LET(v; E<R>-N(AK<R>); IF(v=0;"";v)), и т.д.
% по роли (агрегат BC:BI) - взвешенное по оценке этой роли в подзадачах. Поскольку подзадачи могут возвращать пустую строку (для нулевых %), оборачиваем массив в N(...), чтобы пустые строки превратились в 0 (без N SUMPRODUCT упадет с #VALUE!):
BC<R> = =IFERROR( SUMPRODUCT(N(BC<r1>:BC<rN>); E<r1>:E<rN>) / SUM(E<r1>:E<rN>); 0 ) // PM
BD<R> = =IFERROR( SUMPRODUCT(N(BD<r1>:BD<rN>); F<r1>:F<rN>) / SUM(F<r1>:F<rN>); 0 ) // AR
BE<R> = =IFERROR( SUMPRODUCT(N(BE<r1>:BE<rN>); G<r1>:G<rN>) / SUM(G<r1>:G<rN>); 0 ) // SA
BF<R> = =IFERROR( SUMPRODUCT(N(BF<r1>:BF<rN>); H<r1>:H<rN>) / SUM(H<r1>:H<rN>); 0 ) // BE
BG<R> = =IFERROR( SUMPRODUCT(N(BG<r1>:BG<rN>); I<r1>:I<rN>) / SUM(I<r1>:I<rN>); 0 ) // FE
BH<R> = =IFERROR( SUMPRODUCT(N(BH<r1>:BH<rN>); J<r1>:J<rN>) / SUM(J<r1>:J<rN>); 0 ) // QA
BI<R> = =IFERROR( SUMPRODUCT(N(BI<r1>:BI<rN>); K<r1>:K<rN>) / SUM(K<r1>:K<rN>); 0 ) // DO
Каждая формула привязана к своей колонке оценки роли (PM ↔ E, AR ↔ F, SA ↔ G, BE ↔ H, FE ↔ I, QA ↔ J, DO ↔ K). Веса - именно оценка, а не факт: тогда роль с большим планом, но без часов еще, не "размывается" малой долей факта. На уровне родителя нули не скрываются - они остаются числами, чтобы вышестоящий проект мог считать через них.
Σ% по задаче:
BJ<R> = =IFERROR( SUMPRODUCT(N(BC<R>:BI<R>); E<R>:K<R>) / SUM(E<R>:K<R>); 0 )
Δ по задаче:
BK<R> = =IFERROR( BJ<R> - M<R> / D<R>; 0 )
Формулы Σ% и Δ - те же, что у подзадачи. Уровень определяется тем, на какие данные ссылается строка (детализация vs. суммы подзадач).
Самый верхний родитель (например, строка Проект) агрегирует строки родителей задач. Логика та же, что у задачи, но веса берутся с уровня задач.
Пусть задачи живут в строках <T1>, <T2>, ..., <Tk>.
Оценка по роли:
E<P> = =E<T1> + E<T2> + ... + E<Tk>
(Если задачи идут подряд без пропусков, проще =SUM(E<T1>:E<Tk>). При пропусках - явное перечисление, чтобы не задвоить подзадачи.)
Факт по исполнителю (детализация N:V):
N<P> = =N<T1> + N<T2> + ... + N<Tk>
...
V<P> = =V<T1> + ... + V<Tk>
Прогресс по исполнителю (детализация Y:AG) - взвешенный по часам факта на задачах:
Y<P> = =IFERROR( (Y<T1>*N<T1> + Y<T2>*N<T2> + ... + Y<Tk>*N<Tk>) / (N<T1> + ... + N<Tk>); 0 )
...
И аналогично для Z..AG (привязка к своей колонке часов исполнителя).
Факт по роли (агрегат AK:AQ):
AK<P> = =AK<T1> + AK<T2> + ... + AK<Tk>
Остаток по роли (AT:AZ): AT<P> = =LET(v; E<P>-N(AK<P>); IF(v=0;""; v)), и т.д. (тот же LET/N-шаблон, что у подзадачи).
% по роли (агрегат BC:BI) - взвешенное по оценке роли в задачах. Каждое значение из задач оборачивается в N(...), на случай, если у задачи Прогресс по роли = 0 (там тоже могут быть пустые строки):
BC<P> = =IFERROR( (N(BC<T1>)*E<T1> + N(BC<T2>)*E<T2> + ... + N(BC<Tk>)*E<Tk>) / (E<T1> + E<T2> + ... + E<Tk>); 0 )
И аналогично для BD..BI (привязка к своей колонке оценки роли).
Σ оценка / Σ факт / Σ% / Δ - так же, как у задачи:
D<P> = =D<T1> + ... + D<Tk>
M<P> = =M<T1> + ... + M<Tk>
BJ<P> = =IFERROR( SUMPRODUCT(N(BC<P>:BI<P>); E<P>:K<P>) / SUM(E<P>:K<P>); 0 )
BK<P> = =IFERROR( BJ<P> - M<P> / D<P>; 0 )
Эквивалентность: благодаря вложенному взвешиванию по оценке роли на каждом уровне, Σ% по проекту совпадает с суммой выполненных часов по подзадачам / суммой оценочных по подзадачам - то есть с "честным" агрегатом по всему контракту. Это удобно: можно проверять результат либо через цепочку (подзадача → задача → проект), либо одной "плоской" формулой по всем подзадачам контракта.
Σ% - доля выполненной работы относительно того, что было запланировано. Всегда трактуется в часах оценки: "сколько из плановых часов уже сделано". Не равно "сколько часов списано".
Δ = Σ% - Факт/Оценка. Разница между долей выполненного и долей сожженных часов. Положительно - идем с опережением (сделали больше, чем потратили часов от плана). Отрицательно - отстаем (потратили часов больше, чем сделали работы). Единица измерения - процентные пункты, не проценты: "-15 п.п." значит "сожжено на 15 п.п. больше плана при текущем уровне готовности".
Дальше - что означают эти показатели на каждом уровне иерархии.
- Σ% - сколько процентов от scope этой подзадачи закрыто. Формула - взвешенное по оценке роли среднее % всех ролей. Если, например, у подзадачи оценка PM=2 ч и BE=8 ч, и BE сделал 50%, а PM еще не начал, Σ% = (0×2 + 50%×8)/10 = 40%. PM в простое не "ломает" картину - его доля просто 0.
- Δ - локальное опережение/отставание на самой подзадаче. Если Σ%=40%, а часов сожжено уже 7 из 10 плановых (Факт/Оценка = 70%), Δ = 40% - 70% = -30 п.п.: подзадача отстает, тратится больше, чем делается.
- Используется для тактического контроля: какая конкретно подзадача "горит".
- Σ% задачи - агрегат Σ% подзадач, взвешенный по их оценочной трудоемкости. Маленькая закрытая подзадача (1 ч, 100%) и большая открытая (50 ч, 0%) дадут Σ% задачи ≈ 2%, а не "50%" как при простом среднем. Это правильно - метрика показывает реальную долю закрытой работы по задаче в целом.
- Δ задачи - опережение/отставание по задаче в среднем. На этом уровне начинают усредняться разнонаправленные подзадачи: одна с +20 п.п., другая с -30 п.п. могут дать Δ ≈ -5 п.п. Если по Δ задачи все спокойно, но в подзадачах есть сильные отклонения - смотреть детализацию.
- Важно: строки "Незапланированные доработки" (раздел 4) не дают вклада в Σ% (у них оценка = 0), но гасят Δ вниз, потому что увеличивают Σ факт без увеличения Σ% и Σ оценки. Сильный минус по Δ задачи - чаще всего сигнал не "отстаем", а "много overhead".
- Σ% проекта - доля выполнения контракта в целом. Эквивалентно "сумма выполненных часов по подзадачам / сумма оценочных часов по подзадачам". Не зависит от того, как порезаны подзадачи и задачи: благодаря вложенному взвешиванию результат одинаковый. Это основная цифра для отчета заказчику.
- Δ проекта - системное опережение/отставание контракта. Это уже "сводная температура": в нем смешаны и реальные отставания, и непредвиденный overhead, и неравномерность списания часов. Сильный минус (например, ниже -10 п.п.) - повод детально разобрать, где именно "сжигается" сверх плана: смотреть Δ по задачам, потом по подзадачам и ролям. Положительный Δ контракта не всегда хорошо: может означать, что не списывают часы (нужно сверять с фактической активностью команды).
- Полезно сопоставлять Δ контракта с метрикой "доля незапланированных" (раздел 4.4): если основной "минус" в Δ объясняется overhead в риск-буфере, эскалация не нужна; если за пределами буфера - повод предупреждать заказчика.
- Σ% не падает с появлением новых подзадач без факта. Появилась новая подзадача с оценкой 20 ч и %=0 - Σ% задачи/проекта снизится: знаменатель (сумма оценок) вырос, числитель не изменился. Это корректное поведение: scope увеличился, выполнение - нет. Если ожидается рост scope, заранее закладывать его в оценку.
- Δ чувствителен к раннему списанию без процента. Если QA списал часы, но % выполнения еще не выставил, Σ% не вырастет, а Факт/Оценка - да. Δ просядет ложно. Правило команды - списывать часы вместе с обновлением %, не "догонять" % раз в неделю.
- Не сравнивать Σ% разных контрактов напрямую. В разных контрактах разная структура оценок, разный риск-буфер, разная доля overhead-строк. Сравнение имеет смысл внутри одного контракта, по неделям.
Остаток = Оценка - Факт. Колонки AT:AZ, одна на роль (PM, AR, SA, BE, FE, QA, DO).
- Положительный остаток - запас по плану роли, еще можно списывать.
- Ноль - часы по этой роли исчерпаны точно (на момент текущего среза).
- Отрицательный остаток - перерасход: уже сожжено больше, чем заложено в оценке роли. Это твердое превышение, не отражение скорости (как Δ). Если такая ячейка появляется - повод разобраться, прежде чем продолжать списывать.
В отличие от Δ, Остаток - чисто часовая метрика, не зависит ни от % выполнения, ни от взвешивания. Полезен в паре с Δ: Δ говорит "по скорости", Остаток - "по бюджету".
На уровне родителей (задача, проект) Остаток считается как Оценка<R> - Факт<R> - просто разность агрегатов. На проекте это сумма остатков по задачам.
На диапазон всех ячеек Δ (колонка BK + клетки Δ в итоговой строке):
- Зеленая заливка: значение ≥
5% - Красная заливка: значение ≤
-5% - Желтая заливка: между ними
Пороги ±5 п.п. - можно подстроить, если по факту будут ложные срабатывания.
Для Остатка по ролям (AT:AZ) - имеет смысл подсветить только отрицательные значения красным: положительный остаток - норма (есть запас), а перерасход всегда требует внимания.
| Ситуация | Что покажет |
|---|---|
| Новая подзадача, ничего не списано / не % - чистый 0 | Σ% = 0, Δ = 0 |
| Роль не на задаче (оценка = 0) | % этой роли не влияет на Σ% задачи |
| У роли часы факта = 0 при ненулевом % | Агрегат % роли = 0 (взвешивание по факту); семантика: пока часов нет, прогресс не идет |
| Подзадача без оценок (странный кейс) | Σ% = 0 (IFERROR ловит 0/0) |
| Списано 100% оценки, факт = 0 | Δ = +Σ% (явный сигнал, что не списывают часы) |
| Списано часов больше оценки | Δ уходит в минус - сигнал отставания / overhead |
| Один исполнитель ушел в перегруз, другой нет | Σ% роли взвешено по часам - перекошен в сторону того, кто работает |
В контракте может появляться отдельная строка "Незапланированные доработки" - часы overhead на уже согласованном scope (скрытая сложность, мелкие правки в процессе, доработки по итогам ревью и т.п.). У нее нет планового объема и нет определенного "% выполнения".
Главное правило: не загонять под оценку. Иначе формулы дают ложный сигнал "в графике / отстаем".
| Блок | Что в ячейках |
|---|---|
| Оценка | пусто (или 0) во всех ячейках по ролям; Σ оценка = 0 |
| Факт (детализация и агрегат) | списывается как обычно по ролям; Σ факт = реальные часы |
| Прогресс | % по ролям пусто; Σ% и Δ строки не показывать (или прочерк через формулу) |
Визуально - подсветить такие строки нейтральным фоном (например, серым), чтобы они отличались от строк scope.
Формулы трогать не надо - они уже корректно отрабатывают такие строки за счет IFERROR и взвешивания по оценке:
- Σ% задачи / проекта - строка не влияет: числитель
SUMPRODUCT(%×оценка)= 0, знаменательSUM(оценка)= 0; не добавляется ни в числитель, ни в знаменатель агрегата. - Σ факт роли / контракта - увеличивается на часы из незапланированных.
- Δ роли / контракта = Σ% - Факт/Оценка → уйдет в минус. Это и есть сигнал: роль (или контракт) системно тратит сверх плана.
Рекомендованный вариант - отдельная строка на уровне проекта/контракта, рядом с родителем проекта (например, строка 7 в листе Прогресс, между Проектом и первой задачей). Тогда overhead виден отдельно, не "разъедает" Δ конкретной задачи и не маскирует ее настоящее отставание.
Формулы родителей задач (D9:BK9, D17:BK17 в примере) не трогаем - они работают только со своими подзадачами и не знают о существовании строки незапланированных. Это держит каждую задачу честной.
В формулы проекта (<P> = строка 5) добавляем строку незапланированных (<rND> = строка 7) только в часовые суммы и в Факт по роли, через N(...):
| Блок | Формула проекта (k задач) | Включает ? |
|---|---|---|
| Оценка по роли (E:K) | =E<T1> + ... + E<Tk> |
нет |
| Σ оценка (D) | =D<T1> + ... + D<Tk> |
нет |
| Факт по исполнителю (N:V) | =N<T1> + ... + N<Tk> + N(N<rND>) |
да |
| Σ факт (M) | =M<T1> + ... + M<Tk> + N(M<rND>) |
да |
| Прогресс по исполнителю (Y:AG) | (Y<T1>*N<T1> + ... + Y<Tk>*N<Tk>) / (N<T1> + ... + N<Tk>) |
нет |
| Факт по роли (AK:AQ) | =AK<T1> + ... + AK<Tk> + N(AK<rND>) |
да |
| Остаток по роли (AT:AZ) | =LET(v; E<P>-N(AK<P>); IF(v=0;""; v)) |
косвенно да |
| % по роли (BC:BI) | (N(BC<T1>)*E<T1> + ... + N(BC<Tk>)*E<Tk>) / (E<T1> + ... + E<Tk>) |
нет |
| Σ% (BJ) | SUMPRODUCT(N(BC<P>:BI<P>); E<P>:K<P>) / SUM(E<P>:K<P>) |
нет |
| Δ (BK) | =BJ<P> - M<P>/D<P> |
косвенно да |
Логика:
- Σ% и Прогресс по роли не должны падать из-за overhead - выполнение плановых задач от него не зависит.
- Σ факт, Факт по роли и Остаток должны включать overhead - часы реально потрачены и съедают бюджет проекта.
- Δ проекта уйдет в минус автоматически: Σ% не растет, а
M/Dрастет на часы незапланированных. Это и есть нужный сигнал по контракту в целом. - Остаток по роли на проекте уйдет в отрицательный, если overhead в этой роли превысил резерв по задачам (см. пример: строка 7 в листе
Прогресс, PM5 часов).
Если по конкретной задаче нужно отдельно учесть overhead (поручили overhead конкретной задаче), можно ввести вторую "вложенную" строку незапланированных внутри задачи и расширить только ее агрегаты на эту строку через +N(N<rND>) и т.п. Но это редкий случай; по умолчанию overhead - на уровне проекта.
Полезно вывести в шапке контракта отдельной ячейкой:
= SUM(факт незапланированных по всем задачам) / SUM(оценка контракта)
Смысл: какой процент от объема контракта уходит на overhead. Сравнивается с риск-буфером проекта (раздел 2 регламента, ориентир ~10%). Превышение - повод к эскалации (раздел 6.1) и к закладыванию большего риск-буфера в следующих контрактах.
"Незапланированные доработки" заводится фичей в той же версии/категории, что и крупная задача. Внутри - задачи исполнителей по ролям с оценкой 0. Часы списываются по факту. При еженедельной выгрузке (раздел 8.1 регламента оценки) эти часы попадают в Σ факт по контракту и в роль-колонки автоматически.
Флоу задачи в трекер задач (статусы, переходы, карточка бага) - Регламент работы с задачами в трекер задач.
Колонки BN:BS справа от Δ - стандартные метрики EVM (Earned Value Management), посчитанные на тех же входах (Σ оценка, Σ факт, Σ%). Дублирования с разделами 1-3 нет - это другая упаковка тех же данных, привычная заказчику и PMO.
EVM выглядит как набор случайных аббревиатур, но за ними одна простая механика. Экономистом быть не нужно: все меряется в часах и все выводится из трех чисел.
| Что это | На какой вопрос отвечает | |
|---|---|---|
| PV | сколько часов план обещал освоить к этой дате | "сколько должны были сделать?" |
| EV | сколько работы реально сделано, в плановых часах | "сколько сделали?" |
| AC | сколько часов реально потрачено | "сколько потратили?" |
Главное здесь - EV. Он меряется не потраченными часами, а плановыми: сделали половину работы, которая по плану стоила 100 часов, - EV = 50 часов, независимо от того, ушло на это 30 часов или 80. Именно поэтому EV можно сравнивать и с планом, и с фактом.
Дальше все остальное - это две операции над этими тремя числами:
- вычесть - получится разница в часах, имя кончается на V (Variance). Хорошо, когда больше нуля.
- разделить - получится безразмерный индекс, имя кончается на PI (Performance Index). Хорошо, когда больше единицы.
А первая буква говорит, с чем сравниваем EV:
- C (Cost) - с
AC, то есть про бюджет и часы. - S (Schedule) - с
PV, то есть про сроки.
Отсюда вся четверка собирается сама, запоминать нечего:
| Вычитание -> часы | Деление -> индекс | |
|---|---|---|
| против AC (бюджет) | CV = EV - AC |
CPI = EV / AC |
| против PV (сроки) | SV = EV - PV |
SPI = EV / PV |
EV всегда стоит первым - это единственное, что надо держать в голове.
Отдельное семейство - "at Completion", то есть "на момент финиша":
| Термин | Расшифровка | По-русски |
|---|---|---|
| BAC | Budget at Completion | бюджет на весь scope - то, о чем договорились |
| EAC | Estimate at Completion | прогноз, во что реально выльется |
| VAC | Variance at Completion | BAC - EAC: на сколько промахнемся |
| ETC | Estimate to Complete | сколько еще осталось потратить (EAC - AC) |
И одна метрика, которая смотрит вперед, а не назад: TCPI (To-Complete Performance Index) - с каким CPI надо пройти остаток, чтобы уложиться в BAC.
CPI отвечает "как мы шли до сих пор", TCPI - "как надо идти дальше". Если TCPI заметно выше уже достигнутого CPI, план арифметически нереалистичен.
Наши две метрики из разделов 1-3, которых в каноническом EVM нет, но которые ему эквивалентны:
| Наше | Чему равно в EVM |
|---|---|
Σ% - доля выполнения, взвешенная по оценке |
EV / BAC |
Δ - опережение/отставание в п.п. |
CV / BAC (то же, что CV, но в долях бюджета, а не в часах) |
Пример на круглых числах. Контракт на 1000 ч (BAC). К текущей дате план обещал освоить 500 ч (PV). Сделали работы на 400 плановых часов (EV), потратили при этом 550 ч (AC).
| Считаем | Получается | Читается |
|---|---|---|
CV = 400 - 550 |
-150 ч | перерасход: сожгли на 150 часов больше, чем сделали |
CPI = 400 / 550 |
0,73 | каждый заработанный час обходится в 1,37 потраченного |
SV = 400 - 500 |
-100 ч | отстаем от графика на 100 часов работы |
SPI = 400 / 500 |
0,80 | идем на 80% плановой скорости |
EAC = 1000 / 0,73 |
1375 ч | при таком темпе выйдет 1375 вместо 1000 |
VAC = 1000 - 1375 |
-375 ч | промах по бюджету на 375 часов |
TCPI = (1000-400) / (1000-550) |
1,33 | остаток надо пройти вдвое эффективнее, чем шли (1,33 против 0,73) - то есть план уже недостижим |
Обрати внимание: CV и SV разошлись (-150 против -100). Это нормально и полезно - проект отстает и по деньгам, и по срокам, но по деньгам сильнее. Если бы CPI был 1,0 при SPI 0,8 - значит работаем эффективно, просто людей на проекте меньше, чем планировалось.
| Наше | EVM | Что значит |
|---|---|---|
D = Σ оценка |
BAC (Budget at Completion) | Бюджет на весь scope, в часах |
M = Σ факт |
AC (Actual Cost) | Фактически потраченные часы |
BJ × D = Σ% × Σ оц. |
EV (Earned Value) | Освоенный объем: сколько плановых часов закрыто текущим выполнением |
BJ = Σ% |
EV / BAC | Доля освоенного от бюджета |
BK = Δ |
CV / BAC | Нормированное отклонение по стоимости (в долях бюджета, в п.п.) |
BN (EV) = =LET(v; BJ<r>*D<r>; IF(OR(D<r>=0; v=0); ""; v))
BO (CV) = =LET(v; N(BN<r>)-M<r>; IF(OR(D<r>=0; N(BN<r>)=0); ""; v))
BP (CPI) = =LET(ev; N(BN<r>); IF(OR(ev=0; M<r>=0); ""; ev/M<r>))
BQ (EAC) = =LET(cpi; N(BP<r>); IF(cpi=0; ""; D<r>/cpi))
BR (VAC) = =LET(eac; N(BQ<r>); IF(eac=0; ""; D<r>-eac))
BS (TCPI) = =LET(rem; D<r>-N(M<r>); IF(OR(D<r>=0; rem<=0); ""; (D<r>-N(BN<r>))/rem))
Что значат метрики:
- EV (Earned Value) - "выполненная часть бюджета в часах".
EV = Σ% × BAC. Например, проект на 360 ч с готовностью 19% → EV = 70 ч. - CV (Cost Variance) = EV - AC. Положительно - сэкономили часы (выполнено больше, чем сожгли); отрицательно - перерасход. В отличие от Δ это абсолютная величина в часах, а не доля.
- CPI (Cost Performance Index) = EV / AC. Эффективность расходования часов:
1.00- идем ровно по плану,>1- быстрее плана,<1- дороже плана.0.8-1.0- умеренное отставание, можно нагнать.0.6-0.8- тревога, нужен план восстановления.<0.6- серьезное отставание, разговор с заказчиком (scope или сроки).
- EAC (Estimate at Completion) = BAC / CPI. Прогноз итоговой стоимости в часах, если сохранится текущая эффективность. Формула предполагает что оставшаяся работа пойдет с тем же CPI - наиболее распространенное допущение в EVM.
- VAC (Variance at Completion) = BAC - EAC. Прогноз итогового отклонения. Положительно - закончим с экономией; отрицательно - закончим с перерасходом на
|VAC|часов. - TCPI (To-Complete Performance Index) = (BAC - EV) / (BAC - AC). С какой эффективностью надо пройти остаток, чтобы все-таки уложиться в бюджет. В отличие от CPI (который смотрит назад) TCPI смотрит вперед и отвечает на вопрос "еще вытягиваем или уже нет".
≤ 1.0- при текущем темпе укладываемся, рывок не нужен.1.0-1.1- нужен умеренный рывок, обычно реалистично.> 1.1при заметно меньшем CPI - план недостижим арифметически: если полконтракта шли на CPI 0.8, внезапно выдать 1.15 на остатке не выйдет. Это сигнал к разговору о scope, сроках или бюджете, а не к обещанию "наверстаем".- Пусто, когда
AC ≥ BAC- бюджет уже исчерпан, наверстывать нечего (перерасход виден по VAC).
Формулы работают на любой строке иерархии, но заполнять их везде не надо - ниже уровня этапа они дают не измерение, а шум.
| Уровень | EVM | Почему |
|---|---|---|
| Проект / контракт | да | основная цифра для заказчика и руководства |
| Уровень агрегации под ним - этап или крупная задача-родитель | да | видно, что именно жжет; разрыв между суммой по этим строкам и контрактом равен вкладу overhead |
| Листовая подзадача | нет | индексы шумят до бессмыслицы, см. ниже |
| Незапланированные | бессмысленно | у строки оценка = 0 по построению (раздел 4), поэтому EV = 0 и весь блок пуст всегда. Формулы там можно держать, но они ничего не выведут; overhead виден в AC контракта и в метрике "доля незапланированных" |
Критерий - не название уровня, а объем строки. Строка на 10-50 часов даст скачущие индексы независимо от того, зовется она задачей или подзадачей; строка на несколько сотен часов - устойчивые. Поэтому в таблице с иерархией "контракт -> этап -> подзадача" EVM живет на контракте и этапах, а в таблице "проект -> задача -> подзадача" - на проекте и задачах.
На подзадаче любая неточность процента или неравномерность списания дает кратные искажения. Живой пример: подзадача с оценкой 698 ч, фактом 207 ч и готовностью 98% показывает CPI 3,3 и VAC +487 - формально "сэкономим 487 часов", фактически же это просто расхождение оценки с фактом на одной строке. Соседняя подзадача того же этапа при этом дает CPI 0,39. Разброс индексов 0,4-3,3 внутри одного этапа - не сигнал, а артефакт гранулярности.
Тактический контроль подзадач при этом ничего не теряет: Δ несет ту же информацию, что CV (только в долях бюджета, а не в часах), а Остаток по ролям показывает перерасход прямо в часах. Обе метрики есть на каждой строке и в EVM не нуждаются.
Оговорка про ранние этапы. Пока этап пройден на 10-15%, его EAC и VAC так же ненадежны, как у подзадачи: делим на CPI, посчитанный по горстке часов. Ориентироваться на них имеет смысл начиная примерно с 30% готовности этапа, а до того смотреть только на EV и CV.
У EAC два канонических варианта, и они дают заметно разные числа. Выбор между ними - не вкусовщина, а ответ на вопрос "отклонение системное или разовое?".
| Природа отклонения | Формула | Когда применять |
|---|---|---|
| Системное (typical) | EAC = BAC / CPI |
Причина перерасхода никуда не делась и доработает до конца проекта: оценки занижены по всему scope, не хватает экспертизы, архитектура сопротивляется. Дефолт, пока не доказано обратное. |
| Разовое (atypical) | EAC = AC + (BAC - EV) |
Перерасход уже случился и не повторится: единичная переделка, один провальный модуль, разовый простой. Остаток идет по плановой эффективности. |
Различить их по одному срезу невозможно - нужна история (раздел 6). Диагностика простая: смотреть на CV во времени.
CVрастет по модулю от среза к срезу - отклонение системное, братьBAC / CPI.CVдержится на одном уровне, а приростEVдогнал приростAC- перерасход разовый и уже понесенный, реальность ближе кAC + (BAC - EV).
Разница не косметическая. На тех же входах (BAC 5000, AC 2900, EV 2450) первый вариант дает 6659 ч (перерасход 993), второй - 6151 ч (перерасход 485). Вдвое.
Поэтому на отчете полезно показывать вилку из двух оценок, а не одно число: пока история короткая, честно сказать "перерасход 500-1000 часов в зависимости от того, разовый провал или системный", а не выдавать любую из границ за факт. Когда срезов накопится и станет видно поведение CV, вилка схлопнется сама.
- ETC (Estimate to Complete) = EAC - AC - можно посчитать на лету (
BQ<r> - M<r>), не вынес в отдельную колонку чтобы не зашумлять. - PV (Planned Value) и SV/SPI в самом листе не считаются: они требуют не строки, а даты - плановой кривой выполнения во времени. Они вынесены в лист
Историяповерх недельных срезов и базового плана по этапам, см. раздел 6.
Так как часы строки Незапланированные доработки попадают в M проекта (через +N(M<rND>)), но не в EV (там оценка = 0), CPI проекта естественно проседает от overhead. Поэтому EVM на проекте показывает совокупную картину "scope + overhead", а EVM на задачах - только свой scope, без overhead. Сравнивая CPI задач и CPI проекта, можно отдельно увидеть масштаб overhead в общей картине.
Разделы 0-5 описывают моментальный снимок: где проект стоит сейчас. Все формулы живые и пересчитываются, поэтому вчерашнего состояния таблица не помнит. Между тем главные управленческие вопросы - динамические: ускоряемся или замедляемся, растет ли overhead, сходится прогноз или уползает, когда закончим.
Ответ дает регулярный срез: раз в неделю значения проекта фиксируются строкой в отдельном листе История. Поверх накопленных срезов появляются плановая кривая PV, метрики сроков (SV, SPI), темп и прогноз даты завершения, а также графики.
Срез хранит значения, не формулы. Если в лист истории поставить формулы, ссылающиеся на живой блок EVM, они будут пересчитываться вместе с ним, и все "исторические" строки всегда покажут сегодняшний день. Истории не возникнет вообще.
Поэтому перенос значений в История делается только вставкой значений (Ctrl+Shift+V -> "Вставить только значения"), а не обычной вставкой и не ссылкой на ячейку.
Обратное тоже верно: внутри листа История формулы безопасны и желательны, потому что ссылаются на уже замороженные ячейки того же листа.
Наивный подход - копировать весь блок EVM. Так делать не надо: EV, CV, CPI, EAC, VAC, TCPI и Δ выводятся из трех чисел - BAC, AC и Σ% (см. соответствие в разделе 5). Достаточно заморозить входы, остальное посчитается формулами внутри История.
Что это дает:
- Вручную переносится 5 значений в неделю вместо 9-12 - меньше ручной работы и меньше шансов ошибиться.
- История непротиворечива по построению: невозможна строка, где CPI не соответствует своим EV и AC.
- Если формула метрики позже уточнится, вся история пересчитается задним числом - переснимать ничего не нужно.
Замораживаем ровно это (строка проекта <P>, плюс строка незапланированных <rND>):
| Что | Откуда |
|---|---|
| Дата среза | вводится вручную |
| BAC, ч | D<P> - Σ оценка проекта |
| AC, ч | M<P> - Σ факт проекта |
| Σ% | BJ<P> - готовность проекта |
| Незапланированные, ч | M<rND> - Σ факт строки незапланированных |
| Флагов "Проверка" | =COUNTIF(<диапазон Проверки>; "?*") - см. ловушку ниже |
| Комментарий | вручную: что за неделю значимого произошло |
Последние два поля - не метрики, а служебные. "Флагов Проверка" фиксирует качество данных на момент среза (см. 6.9), комментарий объясняет изломы графиков через полгода, когда контекст забудется.
Ловушка счетчика флагов. Считать надо именно через
COUNTIF(...; "?*")- "ячейки, где есть хотя бы один символ текста". ОчевидныеCOUNTA(...)иCOUNTIF(...; "<>")здесь врут: формула Проверки на строках без рассогласований возвращает пустую строку"", а это для Sheets не пустая ячейка. Оба варианта посчитают все строки, где формула просто стоит, и счетчик будет каждую неделю показывать одно и то же число (скажем, 21 при реальных нулях). Ошибка тихая: цифра выглядит правдоподобно и не вызывает подозрений.
Слева замороженный блок (вводится), справа производный (формулы, протягиваются вниз). Одна строка = один недельный срез, строки идут по возрастанию даты.
| Кол | Поле | Как получается |
|---|---|---|
| A | Дата среза | вручную |
| B | BAC, ч | снимок D<P> |
| C | AC, ч | снимок M<P> |
| D | Σ% | снимок BJ<P> |
| E | Незапланированные, ч | снимок M<rND> |
| F | Флагов "Проверка" | снимок счетчика |
| G | Комментарий | вручную |
| H | EV, ч | =IFERROR(D<r>*B<r>; "") |
| I | PV, ч | по этапам, см. 6.5 |
| J | CV, ч | =IFERROR(H<r>-C<r>; "") |
| K | SV, ч | =IFERROR(H<r>-I<r>; "") |
| L | CPI | =IFERROR(H<r>/C<r>; "") |
| M | SPI | =IFERROR(H<r>/I<r>; "") |
| N | EAC, ч | =IFERROR(B<r>/L<r>; "") |
| O | VAC, ч | =IFERROR(B<r>-N<r>; "") |
| P | TCPI | =IF(C<r>>=B<r>; ""; IFERROR((B<r>-H<r>)/(B<r>-C<r>); "")) |
| Q | Δ, п.п. | =IFERROR(D<r>-C<r>/B<r>; "") |
| R | Доля незапл. | =IF(E<r>=""; ""; IFERROR(E<r>/B<r>; "")) |
| S | Темп Σ%/нед | =IF(ROW()<6; ""; IFERROR((D<r>-INDEX($D:$D;ROW()-4))/((A<r>-INDEX($A:$A;ROW()-4))/7); "")) |
| T | Прогноз 100% | =IF(N(S<r>)<=0; "нет темпа"; A<r> + (1-D<r>)/S<r>*7) |
Границу между блоками полезно обозначить заливкой шапки: замороженное - одним цветом, производное - другим.
Лист стоит защитить (Данные -> Защищенные листы и диапазоны). Данные в нем невосстановимы, а одна затертая ячейка портит все графики, которые через нее проходят. Два варианта:
- Весь лист - если срезы пишутся автоматически (через API или скрипт). Максимальная защита, руками добавить строку уже нельзя.
- Только производные колонки
H:T, а замороженный блокA:G- с предупреждением. Тогда человек может дописать срез руками, но формулы не сломает. Предпочтительно, если ритуал ручной.
Защита действует на пользователей, а не на способ доступа: запись через API от аккаунта-владельца проходит нормально, потому что владелец в списке разрешенных редакторов. То есть защита спасает от чужой руки и от собственного случайного клика, но не от ошибки в самом срезе - от нее спасают только проверки из 6.4.
Держать локальную копию. Раз в неделю выгружать лист в CSV рядом с проектными файлами. История версий Google ненадежна как архив (ревизии прореживаются, а восстановление значений из них - отдельная возня), а Σ% на прошлую дату не хранится больше нигде: факт добывается из трекера, план - из базовых документов, а процент готовности живет только в этой таблице. Выгружать вместе с листом Этапы - без него нельзя пересчитать PV.
Ловушка
INDEX(...; 0)в формуле темпа. Явная проверкаIF(ROW()<6; "")в колонке S нужна, и убирать ее нельзя. Без нее на строке 4 выражениеROW()-4дает ноль, аINDEX(диапазон; 0)в Google Sheets - не ошибка, а спецслучай "вернуть весь столбец".IFERRORего не перехватывает, и вместо пустой ячейки получается 0, то есть фальшивый нулевой темп в самом начале истории. На соседних строках все корректно само (там индекс либо отрицательный, либо попадает в текстовый заголовок - и то и другое дает ошибку, которуюIFERRORгасит), поэтому дефект проявляется ровно в одной строке и легко проходит мимо глаз - пока не увидишь провал в ноль на графике темпа.
Срез привязывается к уже существующему недельному ритму, а не заводится отдельной новой обязанностью.
- Дождаться, пока факт за неделю полон: часы из трекера подтянуты, проценты готовности обновлены исполнителями. Срез по недосписанным часам врет (см. эффект в разделе "Как читать Σ% и Δ": Δ ложно проседает).
- Проверить колонку Проверка: если рассогласований много - сначала разобрать их, потом снимать.
- Скопировать значения
D<P>,M<P>,BJ<P>,M<rND>и счетчик флагов, вставить только значениями новой строкой вИстория, проставить дату. - Дописать комментарий, если неделя была нестандартной (закрыт этап, влетел крупный незапланированный объем, менялась оценка).
- Досвести предыдущую строку по устоявшейся выгрузке часов и отметить это в ее комментарии (см. 6.10).
Снимать всегда в один и тот же день недели. Формула темпа (S) и прогноз (T) делят на число недель между срезами и молча предполагают равные интервалы; срез "когда вспомнили" ломает шкалу и делает кривые несопоставимыми.
Привязывать срез надо к еженедельному обновлению таблицы фактом, а не к дате синка с заказчиком. Причина простая: синк может сдвинуться или отмениться, а шаг истории должен оставаться ровным; к тому же до обновления таблицы часы за неделю еще не разнесены, и срез поймает ложный провал Δ. В этом проекте таблица обновляется в пятницу - значит и срез снимается в пятницу, сразу после обновления.
Пропущенная неделя лечится не задним числом, а пропуском: строку за пропущенную неделю не выдумывать (значений того дня уже не восстановить - таблица живая). Отметить пропуск в комментарии следующего среза.
PV (Planned Value) - сколько часов бюджета должно быть освоено к данной дате по базовому плану. Это единственная величина, которая не выводится из срезов: срезы дают факт (EV, AC), а PV идет от плана. Без него нет ни классической S-кривой, ни метрик сроков.
Источник плана - этапы контракта с их датами сдачи и объемами. Это грубее, чем календарь по задачам, зато совпадает с тем, как заказчик реально принимает работу, и не требует поддерживать плановые даты на каждой подзадаче.
Лист Этапы:
| Кол | Поле | Пример |
|---|---|---|
| A | Этап | Этап 1 |
| B | Начало | 01.06.2026 |
| C | Сдача | 15.10.2026 |
| D | BAC этапа, ч | 1800 |
Требования к таблице: Начало < Сдача, этапы идут подряд без пустых строк, сумма D равна BAC контракта на момент подписания.
Формула PV для среза на дату A<r> (диапазоны - точно по числу этапов, здесь 3):
I<r> = =SUMPRODUCT((Этапы!$C$2:$C$4 <= A<r>) * Этапы!$D$2:$D$4)
+ SUMPRODUCT(
(Этапы!$B$2:$B$4 <= A<r>) * (Этапы!$C$2:$C$4 > A<r>)
* Этапы!$D$2:$D$4
* (A<r> - Этапы!$B$2:$B$4) / (Этапы!$C$2:$C$4 - Этапы!$B$2:$B$4)
)
Первое слагаемое - этапы, которые к этой дате должны быть сданы целиком, берутся полным объемом. Второе - текущий этап, взятый пропорционально прошедшей его части. Этапы, которые еще не начались, обнуляются маской и не участвуют.
Диапазон задается точно по числу этапов намеренно: пустая строка ниже даст Начало = Сдача = 0, деление на ноль и ошибку во всей сумме.
Базовый план замораживается при подписании и живет своей жизнью. Если scope формально изменился (доп. соглашение), базовый план перевыставляется: правятся объемы в Этапы, дата ре-базлайна пишется комментарием в срезе. Уже снятые строки истории не пересчитываются задним числом - иначе пропадет ровно то, ради чего история ведется.
Отсюда полезная перекрестная проверка: если снимок BAC (колонка B) разошелся с суммой BAC этапов - это и есть незакрытый scope creep. Либо оформлять его как изменение плана, либо эскалировать; молча расти он не должен.
Схема выше исходит из того, что этапы выполняются последовательно. На практике часто иначе: контракт разбит на этапы юридически, ради приемки и оплаты, а задачи переплетены, и работы разных этапов делаются одновременно.
В этом случае этапная кривая не просто неточна - она перевернута и льстит. EV считает готовность по всем строкам сразу, включая задачи поздних этапов, а PV до срока первой сдачи планирует только первый этап. Числитель растет за счет того, чего нет в знаменателе, и SPI уверенно показывает опережение там, где его нет. На живом контракте разница вышла в 0,23 пункта SPI - разница между "идем с опережением" и "отстаем".
Признак, что попал в этот случай: SPI заметно лучше CPI при том, что команда не жалуется на простой.
Лечится заменой источника плана. Кривую надо строить не по этапам, а по плановой загрузке ресурсов - помесячному плану часов, который в проектах с бюджетной формой уже есть. Структура листа не меняется, меняется наполнение: вместо строки на этап - строка на месяц (Начало = 1-е число, Конец = 1-е число следующего). Формула PV остается прежней, она не знает слова "этап" - только интервалы и объемы.
| Кол | Поле | Пример |
|---|---|---|
| A | Период | июн.26 |
| B | Начало | 01.06.2026 |
| C | Конец | 01.07.2026 |
| D | BAC периода, ч | 728 |
| E | План по форме, ч | 768 |
Две тонкости, без которых получится мусор:
- Приводить план к
BAC. В бюджетной форме обычно сидит запас (буфер РП, резерв), поэтому сумма планового ресурса большеBAC, по которому считаетсяEV. Умножить все периоды наBAC / Σ плана, иначеPVне сойдется сEVна финише иSPIбудет вечно меньше единицы без всякой вины проекта. Исходные часы формы оставить в соседней колонке - иначе через полгода не проверить, откуда взялись числа. - Коэффициент считать один раз и вписывать числами, а не формулой. Живая формула
= план × BAC / Σвыглядит аккуратнее, но она автоматически подгонит базу под любой новыйBAC- и перекрестная проверка на scope creep перестанет срабатывать навсегда, тихо. База на то и база, что не ездит.
Юридические этапы при этом никуда не деваются - они остаются на том же листе справочным блоком ниже, вне расчетного диапазона: по ним идет приемка, и даты сдачи нужны для разговора с заказчиком.
Версию формы, с которой снят базовый план, записать прямо на лист. Бюджетная форма живет и правится; без явной пометки, из какой ее версии и на какую дату взята кривая, через квартал никто не докажет, что базу не подкручивали задним числом.
- SV (Schedule Variance) = EV - PV, в часах. Положительно - освоено больше, чем планировалось к этой дате (идем с опережением графика); отрицательно - отстаем.
- SPI (Schedule Performance Index) = EV / PV.
1.00- точно по графику,< 1- отставание,> 1- опережение.
Разделение с уже существующими метриками: CPI отвечает "во сколько нам обходится сделанное" (бюджет), SPI - "успеваем ли по календарю" (сроки). Проект может одновременно иметь CPI 1.05 и SPI 0.7: делаем эффективно, но медленно - людей на проекте меньше, чем планировалось.
Если PV построен по плановой загрузке (см. 6.5), сравнивать надо не только EV с PV, но и AC с PV. Это сравнение отвечает на вопрос, который иначе теряется: идет ли расход ресурса по плану. Три исхода:
ACзаметно нижеPV- людей на проекте меньше плана. Отставание лечится ресурсом.ACзаметно вышеPV- жжем быстрее плана; даже приSPIоколо единицы это значит, что бюджет кончится раньше срока.AC ≈ PV- расход идет ровно по плану, и тогдаSPIарифметически совпадает сCPI(приPV = ACформулыEV/PVиEV/ACдают одно число). Выглядит как избыточность, но это диагноз: ресурс потребляется как запланировано, а продукта дает меньше. Догонять наращиванием команды бессмысленно - упремся в деньги раньше, чем в объем.
Важное ограничение SPI. Он измеряется в часах, а не во времени, и к концу проекта неизбежно стремится к 1.00: EV и PV оба сходятся к BAC, даже если проект сдается с опозданием на два месяца. Поэтому SPI информативен в середине контракта и почти бесполезен на финише - там о сроках надо судить по календарю и остатку работ, а не по индексу. По той же причине не стоит показывать SPI заказчику без даты: индекс 0.95 в июне и в декабре означают совершенно разное.
Прогноз даты из срезов получается почти бесплатно и отвечает на самый частый вопрос заказчика - "когда закончите".
Темп (колонка S) - средний прирост готовности за неделю, сглаженный по 4 срезам. Делится не на 4, а на фактически прошедшие недели: (Σ%_текущий - Σ%_четыре_среза_назад) / ((дата_текущая - дата_четыре_среза_назад) / 7). Сглаживание нужно, потому что недельный прирост шумный: одна неделя с приемкой крупной задачи дает скачок, следующая - около нуля.
Деление по датам, а не на константу 4, снимает скрытое допущение о равных интервалах: пропущенная неделя, сдвинутый срез или восстановленные задним числом точки с неровным шагом больше не искажают темп. Требование снимать срез в один день недели от этого не отменяется (оно про полноту данных, см. 6.4), но формула перестает молча врать, когда его нарушили.
Прогноз (колонка T) - линейная экстраполяция: дата_среза + (1 - Σ%) / темп × 7 дней. Если темп нулевой или отрицательный (готовность стоит или откатилась после пересмотра оценок), формула честно пишет "нет темпа" вместо абсурдной даты в 2090 году.
Как читать: это не обещание, а зеркало текущей скорости. Ценность не в самой дате, а в ее дрейфе от среза к срезу. Дата, стабильно уползающая вправо на неделю каждую неделю, означает, что проект не движется к финишу вообще - и это видно за месяцы до формального срыва срока, когда еще можно что-то предпринять.
Прогноз по темпу и EAC отвечают на разные вопросы и должны сверяться: EAC - "во сколько часов обойдется", прогноз даты - "когда". Расхождение между ними (укладываемся в часы, но не в срок) - типичная ситуация при нехватке людей.
Все строятся на листе История по диапазонам вида A2:A (открытым вниз), чтобы новые срезы попадали в графики автоматически, без ручного расширения диапазона.
| График | Ряды | Что показывает |
|---|---|---|
| S-кривая (основной) | PV, EV, AC | Классика EVM: план против освоенного против потраченного. Расхождение EV и AC - деньги, EV и PV - сроки |
| Индексы | CPI, SPI | С опорной линией 1.00. Тренд важнее уровня: 0.9 растущий лучше, чем 0.95 падающий |
| Дрейф прогноза | EAC, BAC | Расходятся - прогноз уползает за бюджет. Ранний сигнал, задолго до фактического перерасхода |
| Overhead | Доля незапл., линия риск-буфера ~10% | Стабилизировался или растет; пересечение буфера - повод к эскалации |
| Темп | Темп Σ%/нед | Скорость набора готовности. Падение темпа и есть дрейф даты завершения вправо (см. 6.7) |
Для отчета заказчику обычно хватает S-кривой и доли overhead; индексы, дрейф прогноза и темп - внутренние, для себя и руководства.
Прогноз даты (колонка T) графиком не рисуется. В ней смешаны даты и текст "нет темпа", а такой ряд Google Sheets считает текстовым и строить отказывается ("добавьте ряд данных"). Это не потеря: дрейф даты читается прямо в колонке, а на графике то же самое видно по темпу - он падает ровно тогда, когда дата уползает.
У живой таблицы есть свойство самолечения: ошиблись в проценте - поправили, и все метрики стали верными. История этого свойства лишена: неверный срез остается неверным навсегда и портит все графики, которые через него проходят.
Отсюда правила:
- Не снимать срез по заведомо неполным данным. Лучше пропустить неделю, чем внести точку, про которую известно, что она врет.
- Снимок счетчика "Проверка" (колонка F) - страховка на этот случай: если в срезе много флагов рассогласования, к точке нужно относиться с недоверием. Через полгода это единственный способ отличить реальный провал CPI от недосписанных часов.
- Снятый срез не редактируется. Исключение - опечатка при вводе, замеченная сразу, до того как цифры ушли в отчет, и досведение предыдущей точки по разделу 6.10. Если ошибка нашлась позже - строку не переписывать (отчет с этими цифрами уже отправлен, и история должна объяснять, откуда они взялись), а дать пояснение в комментарии.
- Строки не удалять и не сортировать вручную. Формулы темпа (
S) ссылаются на строку четырьмя выше; перестановка строк тихо ломает расчет по всей истории.
Часы приходят задним числом. Списания попадают в трекер с задержкой: срез в пятницу видит только то, что уже разнесено, а настоящая стоимость недели дособирается в следующие дни. Поэтому самая свежая точка всегда немного приукрашивает - AC занижен, а CPI и EAC выглядят лучше реальных. Искажение временное: через неделю тот же срез на ту же дату даст большее число.
Что с этим делать:
- Снимать всегда в один день недели (6.4). Тогда запаздывание примерно одинаково у всех точек, и тренд читается верно, даже если каждый уровень чуть оптимистичен.
- Досводить предыдущую точку. На очередном срезе разрешено один раз пересчитать
ACи незапланированные часы прошлой строки по устоявшейся выгрузке и отметить это в ее комментарии. После этого строка замораживается окончательно. Это единственное санкционированное исключение из правила "срез не редактируется": правка механическая и воспроизводимая, а не суждение задним числом. - Глубже одного шага не пересводить. Иначе поедет вся серия и графики перестанут быть воспроизводимыми.
Что восстанавливается, а что нет. Если выгрузка часов из трекера хранит дату списания, то AC и незапланированные часы на любую прошлую дату восстанавливаются точно, обычным SUMIFS по дате:
AC на дату = SUMIFS(часы; версия; "<версия контракта>"; дата; "<=" & DATE(гггг; мм; дд))
Проценты готовности так не восстановить: Σ% живет только в живой таблице и перезаписывается при каждом обновлении. Отсюда практический вывод: невосполнимая ценность недельного среза - в Σ%, остальное при необходимости добывается из трекера задним числом. Правило "не пропускать срез" держится именно на этом.
Восстанавливать историю по отчетам (недельным, статусным) хуже, чем по выгрузке: отчет фиксирует то, что было разнесено на момент его написания, то есть тащит в историю ровно то запаздывание, от которого мы уходим. На живых данных расхождение между "по отчету" и "по выгрузке" вышло 5-7% AC - достаточно, чтобы сдвинуть CPI на 0,05, а прогноз на несколько сотен часов.