Automating Circulation Reporting: The Montana Standard Newspaper

Executive Summary

Description of the Business

The Montana Standard is a daily newspaper that circulates to 14,000 subscribers. It sells advertising space as well as newspaper subscriptions in various daily and online packages. The business seeks to perform in a few ways:

1. Create content that entices readers to purchase a copy or a subscription.
2. Drive circulation to increase the cost of ad space in their publications.
3. Keep carrier costs low by delivering newspapers in the most efficient manner possible.
4. Keep manufacturing costs low by encouraging online subscriptions versus hard copy.

System Overview

I worked with the Circulation Director to automate their monthly reporting process. I created a master spreadsheet called "Create Circulation Reports.xlsm” that resides in the directory where all historical circulation reports sit. Once opened, this report provides the user with the option to create five reports (via the Ribbon or main spreadsheet buttons), which are:

1. Zero Balance
2. Comp Report
3. Customer with more than Two Copies
4. Inactive with Balance
5. Long Sub Period

As an aside, each of these reports is important to the business because it lets them know who their most profitable customers are as well as which ones are the most costly. These reports are run on a monthly basis.

When the report button is clicked, the user is prompted to select the raw Excel file the program will work from. Once selected, unnecessary data from file is removed, the file’s rows are sorted against the most important criteria, cells are formatted to be easily understood, total columns are provided to determine the impact of aggregate subscriber costs or profits, and the reports are formatted for immediate printing.

The file is saved with a date preceding the report name for easy archiving. Additionally, should the circulation personnel decide to email rather than print, there is an option to do so.

The result is that the circulation analyst in charge of creating and delivering these reports saves a day’s worth of time every month by avoiding to do tedious calculations and formatting that can be automated with Excel.

Files

• http://files.gove.net/shares/files/15w/dbanks/Spreadsheet_Automation_Final_Project.pdf
• http://files.gove.net/shares/files/15w/dbanks/Create_Circulation_Reports.xlsm
• http://files.gove.net/shares/files/15w/dbanks/0_Balance_Report_Butte_first_pull.xls
• http://files.gove.net/shares/files/15w/dbanks/Comp_Report_Butte_first_pull.xls
• http://files.gove.net/shares/files/15w/dbanks/Inactive_Subscription_Butte_first_pull.xls
• http://files.gove.net/shares/files/15w/dbanks/Long_Subscription_Butte_first_pull.xls
• http://files.gove.net/shares/files/15w/dbanks/More_than_2_copies_Butte_first_pull.xls