These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, December 6, 2012

Sales Tax Return Helper

My project was to write a program that I could use at my workplace to fill out the so called “Summary sheet” that consisted of different number figures for ten different states: California, Colorado, Illinois, Indiana, Maryland, New York, Tennessee, Texas, Utah, and Washington. The number figures could be anything from Total Sales, Total Exempt Sales, etc. to Total Tax Collected and Total Retailing. Each of these ten states require the same set of numbers every month although which specific numbers are requested depend on each state. 

Each month, from the 1st to the 10th, my job is to first insert into a tax worksheet a column that is next to another column called “Item_Type” and names it “Taxable v Exempt.” Then, I would go ahead and start with one of the listed ten states. Let’s say I start with Texas. After inserting a new column, I would look at each of the categories listed under the “Item_Type” filtered cell of all of the Texas transactions, often starting with what was called “Company Fulfillment.” Then, after filtering a column called “Tax_Trans_Curr” with only the value of “0,” I would decide if I should type in “Exempt,” “Freight,” “Exempt-b2b,” or just leave it blank by looking at each of the transactions that show up and see which category its product name fit into. Next, I would move on to the second category under the “Item_Type” filtered cell. When I am done filling out the “Taxable v Exempt” column for the state, I would go ahead and do some sorting to get the numbers that I intend to get. For instance, if the Summary Sheet asks for a total exempt sale for Texas, then I would go to the tax worksheet and do some sorting, figure out that exempt number, and then copy and paste that number to the “Summary Sheet” at a specific place. When I am done filling out all of the numbers required for Texas, I would move on to a different state till I am done with all of the ten states. 

This whole process is very repetitive and intimidating sometimes. It is very easy to get mixed up when it comes to deciding which status the transaction should be or sorting the transactions and filling in the blanks in the “Summary Sheet.” Thus, since the first week of our VBA class, I have already thought of a way to automate my work process with the help of VBA. The code that I wrote allows me to shorten the whole process tremendously. Currently, I only need to click on a customized button and finish everything in minutes.

No comments:

Post a Comment

Blog Archive