Pack 77 Auto-Calculation Excel Spreadsheets:
These were developed by Pack 77 to eliminate math mistakes (actually all math is eliminated except for counting the actual money) and automatically roll up all the various totals and also the prizes across the entire Pack or Troop. All sheets are protected so you will only be able to select cells where you should enter data.
The ZIP file contains five basic types of sheets:
cash and check counter.xls – This is a stand-alone spreadsheet to make it easier to count bills, coins, and checks. Just enter the quantities for each denomination/check and the tool will calculate your totals. You can then clear the cells and start over again to use this tool multiple times.
Product list.xls – this contains the products for the year, their codes, prices, and case quantities. It is read automatically by the other files and is the only place to input the products. No changes need to be made here.
Prize list.xls – this contains the prizes for the year with their sales level and prize codes. It is read automatically by the other files and is the only place to input the prizes. No changes need to be made here.
Den patrol X summary.xls – this is what the popcorn kernel gives to each den or patrol leader (along with the product list and prize list files which are required for it to work) for them to fill out the info from their Scout sales sheets. You can enter every line of each order form if you want, but mostly we just verify the column totals for each Scout order form and then just enter one row with all the totals for each Scout. See the Start Here sheet for complete info. After the Orders are entered, the leader marks the desired prize(s) and then prints a Scout Summary sheet for each Scout and then a Den or Patrol Summary Sheet which is a roll-up across their entire Den/Patrol showing all the totals and the total prizes.
Pack troop summary.xls – this is the popcorn kernel’s roll-up over the entire Pack or Troop. The sheet reads directly from the den or patrol leader files, so the Popcorn Kernel does not have to do anything once all the files are finished except open all the files at the same time, adjust for Show/Sell quantities, enter your bonus qualification, and print out the final Pack or Troop order sheet to turn in to Council either in hardcopy or as a pdf (do not send the Excel file as it will not work). See the Start Here sheet for complete info.
All the files are linked and must be in the same directory for everything to work. Do not change any of the filenames as doing so will likely destroy the links. Den/Patrol leaders do not need the pack troop summary file but they do need the product list and prize list files. The Popcorn Kernel needs all the files, and any unused Den or Patrol sheets must be included with all zero values (as they are now).
Note: All Show and Sell items must be accounted for in the sheets, and if desired the pack/troop summary calculation allows any unsold Show/Sell to be used to fill Scout orders and thus reduce the Unit Take order placed with Council. The payment due to Council includes all Show/Sell orders plus the adjusted Unit Take order. See advice to the right.
To make sure you are kept up-to-date with the latest releases, you are strongly encouraged to email Pack 77 (via the troubleshooting box below left) to get on the mailing list for updates. If you don't submit your order with the latest version you will likely get a bounce-back from Council.
2015 Release 1: October 22, 2015
ZIP File of Excel Spreadsheets for 2015 Popcorn and Peanut Auto-Calculation (Release date: October 22, 2015)
More Detailed Show and Sell Adjustment Advice (same process since 2013)
Cash and Check Counter.xls (by itself)