Page 364 - Aida Hovsepian Onboarding

 True Index Chart – Shows categories / items that have been on the price index from 2012
through current and the index deviation based solely off of its price. Volume is stagnant for
the time period.

Managing the Price Index

Monthly Process
1. The Data Analytics team by COB on the first Wednesday after the first of the month will pull
the Price Index Report from HAVI Sourcing for the most recent closed month. They will then
copy and paste it into the “YY-MM” excel file located in the following folder:
L:\All\Lenexa\_New CSCS Folder Structure\Procurement\Price Index & Budget\2012
Reformat\Monthly New saving it as the year and month of the data in the report in the same
naming convention.
2. The next step is to open the respective price index reports and write a VLOOKUP in order to
bring in the most recent actual landed at restaurant pricing and populate in column AB
“Actual Store Cost”
3. Save report and close out of file.
4. The last step is to send out a notification email to the Procurement team informing them
that they can now go in and review the most recent added information as well as populate
any updates to the budget or reforecast columns.
Annual Process

1. The price index is currently set up to handle data from January 2012 through December
2020. Hopefully by 2021 we will have a better solution than this because majority of the
formulas and tables will have to be updated.
2. In order to populate the index with a new year’s worth of information, all you have to do is
on the “CM Update” tab, add the items (each for twelve months) in column H “Index Item”
and then populate the following columns:
 “Date” – in the following format of MM/01/YYYY
 Category
 Unit of Measure
 Net Weight
 Category Manager
 Budgeted Volume
 True Index Item – is only populated if the item has been on the price index for all
years back to 2012
3. Columns A-C, E-F, U, AA–AM are all formulas and can simply be dragged down to the lowest
row
4. If the average store count needs to be updated for the appropriate reporting calculations,
unlock the “CM Update” tab and simply update cell E7 with the new number. All reporting
will automatically re-calibrate to that number.
5. Save file and close

Protecting Workbook
 The format of cells highlighted in yellow will be used to indicate editable fields for the users.
This will be used on the report tabs as well as the “CM Update” tab.
 During the budgeting process, unprotect the Budget FOB and Freight columns (columns P &
Q) and highlight yellow.
 When the budgeting process is complete, remove the yellow highlighting, protect the cells
and then unlock the Reforecast FOB and Freight cells (columns V & W) and highlight yellow.

Price Index Tool – Data Analytics Page 4