Cupcake Chic

Business Description

For my final project, I chose to work with a gourmet cupcake bakery where my wife is currently employed, Cupcake Chic (pronunciation: \kŭp-kāk’ shēk\). Cupcake Chic is located in Orem, UT and their cupcakes are made from scratch with the finest ingredients to create a fresh and delicious product.

A the end of every quarter (3 months), every restaurant in the state of Utah needs to file two tax forms (TC-62S and TC-62F) to properly account for the combined sales and use tax, grocery food tax, and prepared food (restaurant) tax that they owe to the state. Since Cupcake Chic is a for profit business in the restaurant industry, they need to track their daily sales so that they can properly file these quarterly tax returns.

Overview of System Built

The VBA system I built for Cupcake Chic has been designed to streamline their process for tracking sales and filing both quarterly tax returns. The program I wrote consisted of three main processes: 1. tracking daily sales, 2. generating monthly reports, and 3. compiling quarterly tax returns.

1. Tracking Daily Sales

At the end of each day, the cash register at Cupcake Chic produces a receipt that has the total sales for the day, broken into several categories. In order to easily and effectively transfer this information into an excel spreadsheet, I created a user form that allows the user to simply input the total daily sales for each category of products at the end of each day. (Please note that all sales figures contained within this report and within the excel file are completely fictional and do not correspond in any way to actual sales figures for Cupcake Chic.)

2. Generating Monthly Reports

Along with tracking daily sales, the owners at Cupcake Chic also wanted to be able to track their total sales at the end of each month. To generate this report, I created a sub procedure that gathers all the sales information for each day during the month and creates a report that shows the total sales for each month, broken down by product category.

3. Compiling Quarterly Tax Returns

Prior to completing this project, the owners at Cupcake Chic had to fill out the two quarterly tax returns by hand. I created a sub procedure that will completely and correctly fill out both quarterly tax returns, based on the sales of the prior three months.

Links:

http://files.gove.net/shares/files/12w/jhowell2/Final_Project_Jason_Howell.pdf

http://files.gove.net/shares/files/12w/jhowell2/Final_Project_Jason_Howell.xlsm