Weekly Marketing Report

I work for the BYU Arts Production Business Office. We do all of the business functions for any Music, Dance, or Theater production that occurs on BYU campus. BYU Arts Production employs several departments to help shows run smoothly. These departments include the Business Office, Scene Shop, Stage Operations, Lighting, and Marketing. My office, the Business Office, keeps the accounting records for all of the departments in BYU Arts Production.

Our marketing department employs mainly students. The students in this department develop posters, programs, and media advertisements to help publicize the arts events to the community. The marketing department rarely works off of up-to-date data to track their own costs. In recent years is has become apparent that more frequent, up-to-date information would be beneficial for this department.

Before this project, it was very time-consuming to provide a useful report to the marketing department. The process involves running online queries, manipulating QuickBooks reports, and somehow organizing the information into an easy-to-read, useful report. This process would take several days’ worth of work, and was simply not feasible for our office.

I decided to conquer this problem. The program I have written automates this report generation process. It creates a report of marketing costs per show in less than two minutes. The program downloads labor reports from the internet and manipulates the information into a useful format. It also prompts the user to upload a marketing report from QuickBooks and manipulates that information into a useful format. The final procedure is report generation – the program takes all of the information extracted from the two data sources and creates a readable, useful, brief report that can be used for better budgeting, tracking and planning of costs in our marketing department.

I also developed two other macros to make daily data entry easier in our office. One manipulates our daily deposits from ticket sales data, and the other automates our monthly inventory adjustment entry. While these two macros are both shorter, more simple macros, they have proved to be time-saving tools for our office.

Note: All VBA code for this project is in the Collyer_Amy_Final Project - Marketing Report.xlsm file. There are five separate processes in this code. I have not provided files with which to run the inventory adjustment macro or the daily deposits macro, but they are not terribly complicated and a brief look at the code should suffice should you desire to double-check my work.

The other three processes are a bit more difficult, and explanation is provided here:

Access to our online labor database is password protected. Thus, you will not be able to successfully run the "Import Labor Data" macro without a password. To facilitate report generation, I input a dummy labor report that will work to run the other parts of my program. Names and numbers on the dummy report have been changed.

If you choose to run the "Import Printing Data" macro, you be prompted to upload a file after you click the "Import Printing Data" button. Upload the PrintingData.xlsx file provided (see below). This file also contains dummy information, but is in the same format as a real QuickBooks download.

The "Generate Report" macro should run with no special instructions.

• http://files.gove.net/shares/files/13w/acollyer/Collyer_Amy_Final_Project_-_Marketing_Report.xlsm
• http://files.gove.net/shares/files/13w/acollyer/PrintingData.xlsx
• http://files.gove.net/shares/files/13w/acollyer/Collyer_Amy_Final_Project_-_Marketing_Report.pd