Moneytrackin Expense Reports

Moneytrackin is a self-described “free online tool to regain control of your financial life.” Through the organization’s website at www.moneytrackin.com, users can access a simple but elegant piece of software that allows for the tracking of individual inflows and outflows of cash. Moneytrackin also offers a free iPhone app, which I have been using to track all my income and expenses since September of 2008. Unlike popular sites like www.mint.com, Moneytrackin is entirely user-controlled and does not link to bank accounts. I enjoy this feature for two reasons: (1) it gives me the ability to specifically track cash-based transactions, and (2) it gives me the power to judgmentally classify each transaction.

The downside of this style of budgeting tool is the significant investment in time and labor. Each month for the past two years, I have had to spend approximately 30 minutes a month to manually transfer my data from Moneytrackin into a spreadsheet, classify each transaction into an expense category, and format the data. I have written a program that will request a date from the user and automatically download that month’s list of transactions to Excel. The program then formats the transaction data and allows the user to classify each transaction into a set of specified expense categories (a set of default expense categories is also provided). My program also allows the user to build a bar graph or a pie chart from the data, in order to see how money was allocated among different expense categories.

Click here for the write-up about my project

Click here for the Excel workbook

Executive Summary - Accounting Comission Automation

Jason Stowell

12/7/2010

Isys520

Executive Summary

For my VBA project I spoke with my brother-in-law who is a partner in an accounting firm that specializes in payroll, accounting, benefits, hr, and corporate design. I have spoken to him multiple times helping him with his questions regarding excel.

At the accounting firm, they have multiple tasks that are very repetitive and time consuming. These issues include renaming up to 30 sheets in one workbook, setting the print areas for each sheet, and getting bank statement information.

The solution I created for the firm was creating a custom AdminAssist ribbon that stores their most used excel features. Likewise, the custom ribbon has buttons that will initiate VBA sub procedures that assist in formatting and printing their commission statements created monthly for clients. These sub procedures include renaming sheets, defining print areas, and launching a custom print dialog box. Finally, the ribbon has a button that will log into a client’s bank and download their monthly bank statements.

Here are a list of files for the project.

Writeup

Custom Ribbon - Add In

Sample Commission Statement

Custom Ribbon - Excel File (if needed for grading purposes)

UVFA Inventory Project

Utah Valley Fencing Academy, Inc.(UVFA or the Academy) is a recently-registered non-profit organization in Utah Valley dedicated to teaching the art and sport of fencing and to prepare students of all ages to compete in fencing competitions. The organization itself has been around only since July of 2010 and is currently seeking 501(c)(3) non-profit status with the IRS. One of the most difficult parts of the application for the organization has been its inventory. The academy has an inventory of over $2,000 with items ranging from replacement sword parts of less than $2 a piece to fencing attire which can be over $150 a piece.

Normally the inventory record keeping would not be so hard, except that inventory is constantly changing within the organization. Parts are constantly being replaced, equipment is being lent out to students for competitions, new inventory is being bought, and some inventory is being sold. Since inventory accounts for the majority of the academy’s assets, they are required to keep a strict record of their inventory and provide accurate inventory numbers to meet the requirements of the 501(c)(3) application.

The UVFA Inventory System was created to provide the Academy with a simple tool to maintain the inventory records of the organization. The inventory systems shown here has the ability to track, search, and adjust inventory; account for depreciation; track inventory items issued to students; and track items purchased by students. The Excel XML ribbon was also modified to include a new UVFA Inventory table with custom buttons to allow easy access to the features and calculators of the inventory system. The financial figures and calculators offered in the system have must custom designed to match the accounts and records being maintained by the Academy in their QuickBooks accounting records.

If you would like to learn more about the UVFA inventory project, please feel free to read the full write-up on the project at:

• http://files.gove.net/shares/files/10f/jedi1446/UVFA_Inventory_Write-up.pdf

The full .xlsm file can be found at:

• http://files.gove.net/shares/files/10f/jedi1446/UVFA_Inventory.xlsm

Executive Summary: Health and Fitness Application

"RB's Health and Fitness Application" (HFA) is designed as a way for people to track their eating and exercising habits on daily, monthly, and yearly time frames. The application was built as a small business idea to be used in conjunction with regular exercise and healthy eating. The HFA conveniently allows the user to enter workout information, food consumption data, as well as enabling the user to create and modify health and fitness goals. The program compares actual data with the user's goals showing his or her progress level. With user-friendly buttons and forms, information is easily gathered, displayed, and monitored. The goal of the HFA is to allow individuals to track progress, create challenging goals, and assist in decision making.

Executive Summary: Rental Property Reconciliation

Executive Summary

Before returning to the MBA program I spent some time investing in real estate. I purchased single family homes around Minneapolis and manage these properties long distance. Currently my company owns four rental properties. To help track the profits and losses on the properties I created a spreadsheet which has amortization schedules for each of the properties, a sheet to track expenses, a sheet to help analyze the ROI on each of the properties, and a sheet dedicated to the yearly P&L analysis. I had already made the spreadsheet somewhat dynamic by creating inputs that filtered from one part of the spreadsheet to another. For example, on the P&L sheet I created a line for Rental Income, Loan Payment, Gross Profit, Expenses, and EBITDA. For each property I have a totals column that calculates the values for each of those lines and then feeds the values into a combines P&L statement. So the VBA project was not to help with the dynamic part but to help speed up the manual part of the process.

Typically I reconcile income and expenses by logging into my business checking account and manually entering in each expense. My VBA project was an effort to help minimize the amount of time spent on this process and more effectively streamline the data. Even though it works now there were some issues that I had to work around such as the variability of many of the inputs. Rental income and expenses can be very different month to month so I needed a way to enter some data manually but still make the process faster and more efficient. I was able to create a user form to input rental income and a list box to allocate transactions to different properties. The project was a success. A process that typically took 40 minutes can now be done in one minute.

Executive Summary: Stock Picker

Executive Summary

In order to make money on the side, and earn a better return on my money as opposed to using a savings account, I trade stocks. One thing that I have noticed over and over in the stock market is that investors tend to overreact. Whenever investors get overly excited or overly spooked by news or economic data it opens up the possibility that a stock’s price will be traded above or below its true value. One example of this was the Gulf of Mexico oil spill. In a matter of days BP’s stock price plummeted from around $60 a share to under $30 a share. The market essentially cut BP’s market capitalization in half, a loss of roughly $100 billion dollars. This occurred in spite of the fact that experts were predicting the total liabilities of BP to be between $30 and $60 billion. Although this is an extreme example these situations occur frequently in the market. Unfortunately for standard investors these market overreactions are often exploited by institutional investors in after-hours or early morning trading. I wanted to create a tool that can be used to calculate the percentage change data for all stocks in a list, in this case all stocks on the NYSE or NASDAQ. The spreadsheet then identifies the five biggest gainers and losers for the day and outputs that data into a separate sheet for review.

Writeup

Project Excel File

Executive Summary: Invoice Tracker

Executive Summary

I currently work for BYU Broadcasting, which is a part of BYU, as an accounting clerk. As we are a part of BYU, we do not actually cut checks for payments. Each week we send many different purchase requisitions and invoices to be paid. With several accounting clerks submitting invoices for payment it can get rather confusing when PRs and invoices are submitted. I created a spreadsheet to keep track of all purchase requisition and invoices. The spreadsheet consists of multiple user forms to input information as well as a user form to search for a specific line item. The spreadsheet also maintains a list of invoices that have been submitted for payment highlighting those that were submitted over a week ago but have not been paid.

http://files.gove.net/shares/files/10f/rsrobert/Fletcher_Rebecca_WriteUp1.pdf

http://files.gove.net/shares/files/10f/rsrobert/Fletcher_Rebecca_FinalProject.xlsm