Auditing Government Departments

EXECUTIVE SUMMARY

Description of Business

The Office of the Utah State Auditor is a state government department responsible for auditing the financial statements of various departments of the State and the public universities in the state of Utah. The Office is performs financial audits, performance audits, and special projects. The financial audit division performs financial statement and internal control audits for all the State departments, agencies, and colleges. The performance audit division audits agency programs and evaluates the effectiveness of their operations. The special projects division performs audits based on requests from citizens.

When I did a sample audit at the Office, I compared the records of the purchasing department with the accounting department to look for discrepancies between capital assets in the accounting department records and capital assets in the purchasing department records.

Overview of System

The program that I have created automates the process that I did by hand when I did a sample audit at the Office. A user puts the transactions from the current year on two preformatted sheets (one from purchasing and one from accounting) after exporting them from the respective department databases. The user is then able to click a few buttons and immediately see all of the problems with the transactions. The user can audit capital asset numbers and find items that should be classified as capital assets that are not, duplicates in the departments, items that should not be classified as capital assets that are, items that are not in the other department that should be, and items with invalid asset numbers. Auditing dates finds items that have the same asset number in both departments, but different dates, and finds invalid dates. Auditing departments finds items that have the same asset number in both departments, but different departments, and finds invalid departments. Auditing historical amounts finds items that have the same asset number in both departments, but different historical amounts, and finds invalid amounts. Finally, auditing for risky items finds items that have a higher risk of being misstated. After clicking the auditing buttons, transactions that are problems or potential problems are marked, and a summary report of the number and types of problems is displayed in aesthetic graphical form. The objective is to speed up the auditing process by automating the audit of most items, and pointing out potential problems for the auditor to think about.

Project Files:
http://files.gove.net/shares/files/14f/benste/FinalVBAProjectWriteUp_BenjaminStephens.pdf
http://files.gove.net/shares/files/14f/benste/FinalProject_BenjaminStephens.xlsm

Maintenance Request

Executive Summary

For my project, I designed a sheet that can help any management company who deals with maintenance requests to deal more effectively with those requests.  Currently, the management company I rent from has been very inefficient in dealing with our maintenance requests mainly because there is no standardized system.  In this project, I used a Google form to collect data and then created a file using VBA that can read the downloaded data and put it into a way for the maintenance manager to see what has been done and also notify the various tenants of the status of their maintenance request when it changes.  

• http://files.gove.net/shares/files/14f/kbrady24/Final_Project_Write_Up.pdf
• http://files.gove.net/shares/files/14f/kbrady24/Final_Project.xlsm
• http://files.gove.net/shares/files/14f/kbrady24/completeletter.txt
• http://files.gove.net/shares/files/14f/kbrady24/Maintenance_Request_Responses.xlsx
• http://files.gove.net/shares/files/14f/kbrady24/updateletter.txt
• https://docs.google.com/spreadsheets/d/1W8Mhgcv3O6cusMguHX4bpn4-3WdlAKBAlbCApmA2oWQ/edit?usp=sharing

• https://docs.google.com/forms/d/1nSkKsWiW59KJrhLXqXgCPAK4oi726JpIvhTYe3L71_k/viewform?usp=send_form

Executive Summary

Marcus & Millichap is a commercial real estate company that specializes in investment sales. They employ sales agents as independent contractors that act as intermediaries between sellers and buyers in the transaction of real estate. Once the real estate property is selected, the seller and agent sign a contract that allows the agent to list and market the property. A part of this marketing strategy requires a pitch book, or offering memorandum. The offering memorandum includes geographic information, pricing information, and tenant information. 

This firm uses a program called iMpact3 to produce offering memoranda. The portion of the offering memorandum I am going to focus on is the rent roll. This section contains a table of current tenant information, another table of tenant lease expiration data, and a graph that represents the lease expiration data. Though effective in other areas such as financial analysis and sales comparison presentation, iMpact3 is very clunky and inefficient compared to Excel when it comes to rent rolls. The largest set back is the lack of keyboard shortcuts. In order to avoid wasting time trying to enter data in their program by pointing and clicking, I created a model in Excel that produces the same results and looks exactly the same as the rent roll section in iMpact3, but is much more user friendly. As a result, the user is able to manipulate data easily in Excel and then take a screen shot of the final product and insert the screen shot into the final iMpact3 document as a .PNG image. 

• Write Up - http://files.gove.net/shares/files/14f/hansen89/TenantRoleModule.pdf

• Project - http://files.gove.net/shares/files/14f/hansen89/Tenant_Roll_MOD.xlsm

Shopping List Generator 

Executive Summary

For this project, I chose to create a grocery shopping list generator for my wife. My wife is a registered dietitian who focused in food service management. While in school she managed the Pendulum Court Café in the Eyring Science Building on the BYU campus. As the manager, she used software that could generate a grocery list based on the recipes that the Café would be using for the next week or so. I decided I wanted to replicate, on a smaller scale, the software that she used in her work using Microsoft Excel and VBA. This grocery shopping list generator can then be used in our home as we plan our meals and buy the necessary ingredients, while trying to stay within a budget.

With this shopping list generator (a user form), the user can select multiple recipes from a list box as well as individual food items from the database to create a shopping list of the major ingredients needed to make those recipes. Upon selecting “OK”, each ingredient that has been selected with the user form appears in the Excel spreadsheet, along with its price per unit. The list is then organized by food category and the total estimated price for the shopping list appears at the bottom of the list. Additionally, the user can enter new recipes and new food items, along with their prices, into the database with user forms. The shopping list generator then draws upon these updated databased to create a shopping list.

• Write-up =  http://files.gove.net/shares/files/14f/kauffie/Taylor_Kauffman_IS520_Final_Project_Write-up.pdf

• Excel File = http://files.gove.net/shares/files/14f/kauffie/Taylor_Kauffman_IS520_Final_Project.xlsm

Paycheck Generator

Executive Summary

Steffani Mortensen is a home caregiver in Provo, Utah.  She manages several nurses in administering care to a single elderly lady at her home. Her client needs care from 9:00am until she goes to bed every day.  Currently, Steff’s biggest pain point is calculating paychecks.  It is a very manual and tedious process.  What I have created is a user-friendly interface that allows Steffani to easily enter start and end times for shifts, provides her with data validation, automatically calculates paychecks and some simple statistics, captures shift and paycheck information for historical records, and emails her nurses paycheck information all at the click of a button.

*Note: email functionality will not work until valid email credentials have been entered.
Project File for a PC: http://files.gove.net/shares/files/14f/nmort/NickMortensenFinalProject.xlsm
Project Writeup:  http://files.gove.net/shares/files/14f/nmort/VBA_Final_Project.pdf

Brevium Insurance Code VBA Script

Executive Summary

Brevium Inc. is a software company that provides doctors with software to reactivate lost patients, to keep track of patients due for appointments, etc. Currently Brevium only serves a small number of specialized doctors, including ophthalmologists and dermatologists, but management would like to add the ability for Brevium software to be able to serve more types of specialties. Part of this expansion includes adding more insurance codes to our software. A consultant has been hired to provide us insurance codes. The codes are changed into regex form and put into our database. Now the test team has been given the responsibility to test to make sure that all the codes in the database are in the spreadsheet and vice versa. Also all codes need to be verified to be sure they apply to the specialty we are working with and any missing codes need to be added. The VBA script I created has been helping use look through all the codes to see which are missing, which are extra, and which have errors in them. 

• HomeworkProjectWriteUp.pdf

Recipe Book and Meal Calendar

Executive Summary:

With this project I wanted to create something that I could actually use to solve a problem. Since the company I work for didn't have any recommendations, I turned to problems in my own home.

Problem
My wife and I face a problem that many single people and young families face. We are always too busy, too lazy, or too unprepared to make a decent dinner when we are hungry. One quick look through the cupboards and our stomach usually forces us to put some Ramen Noodles on the stove or throw a bowl of Spaghetti-o’s in the microwave. This reaction is a result of lack of preparation, we haven’t shopped for anything useful, and lack of creativity, it’s hard to think on an empty stomach. Even though we both enjoy eating real meals and are relatively decent cooks, we often end up eating these quick meals and the problem has yet to fix itself.

Solution
I created a program to help us solve the two main problems we face at dinner time. What should we eat? And do we have the stuff to make it? The spreadsheet I created allows a user to input and store recipes and makes it quick and easy to sort by prep time required or by how many days the food usually lasts. In addition, you can then add those recipes to a meal calendar to help you plan ahead. To make sure you have everything you need for your meals there is a tab that will build you a complete list of ingredients you need based on the meals you have planned in the calendar. With this program you know what food you want to eat, what you need in order to make it, and how long it’s going to take you. My wife and I plan to use this spreadsheet from now on so that we can make meal time more satisfying and less depressing!

Excel Document: 

• http://files.gove.net/shares/files/14f/rcurtis8/RecipeBook.xlsm

Write Up:

• http://files.gove.net/shares/files/14f/rcurtis8/VBAFinalProjectWriteUp.pdf

Brand Automation - Prateek Oberoi

Brand Automation

Executive Summary:

Brand Managers often spend a lot of time analyzing the growth trends of their brand. Performing and analyzing growth trends for different brands on 25 different characteristics and sharing it with the manager or other stakeholders becomes a huge task. This project is aimed at solving this problem, automating the process of analyzing and plotting growth rates in all 25 attributes and also automating the process of sending an email with the required report, filtering out all the unnecessary raw data.

The project calculates growth rates of the selected brand in all the 25 attributes for the overall market and for the individual retailers. It generates a new sheet referred as “Brand Report” which has all the data in a format which enables simple comparison of growth rates across retailers and in overall market. A chart comparing the growth trends is dynamically generated based on the calculated growth rates.

Finally, the project provides an “Email Report” option in the ribbon to send out reports quickly. When you send the email it creates a copy of your report generates a new excel sheet, and sends it to the concerned person as an attachment. This helps to filter the information and send it to the Brand Manager who is associated with that particular brand. 

• http://files.gove.net/shares/files/14f/poberoi/BrandAutomation_Prateek_FinalProject.pdf
• http://files.gove.net/shares/files/14f/poberoi/Brand_Report_Automation.xlsm

Hinckley Center Inventory System

The Gordon B. Hinckley Alumni and Visitor’s Center is home to several campus departments.  These departments are technologically serviced by the BYU Alumni Computer Support Representatives.  As part of their job, the Alumni CSRs spend a month each year purchasing new and replacing old computers used by staff.  The process of determining systems to be replaced, tracking computer location, and avoiding out-of-warranty repair needs has historically been very difficult.  An complete inventory tracking system with easy, intuitive user interface and simple data manipulation will greatly streamline the processes involved with computer maintenance at the Hinckley Center.

The newly built Hinckley Center inventory system allows for the easy manipulation of inventory data, and additionally facilitates painless addition of computers, configurations, and personnel to whom the computers are assigned.  Input forms, relational database design, data tabulation, and automated email systems all work together to provide a better inventory experience for Alumni CSRs and their end users alike.

Microsoft Excel and Access underpin the inventory system, and unlock the door for data manipulation power.  Coupled with a familiar and intuitive interface, the new system allows for inventory tracking by laymen and computer geeks alike.  It truly helps create a more welcoming, inviting system for Alumni CSRs and removes many of the previous burdens to use, the end result of which is a more utilized, accurate, robust inventory solution.

Access Database: http://files.gove.net/shares/files/14f/jbohling/HCInventory.mdb
Inventory Spreadsheet: http://files.gove.net/shares/files/14f/jbohling/Inventory.xlsm
Project Write-up: http://files.gove.net/shares/files/14f/jbohling/HinckleyCenterInventory.pdf

Diet Database

Executive summary:

The project provides a way of manipulating an Access database that tracks a person’s diet. The project can be used by physicians or personal trainers who want their patients/customers to track certain nutritional information, like cholesterol or saturated fats eaten, or exercise information. By using user forms and VBA the project is more user friendly than working directly in Access and in Excel we can create graphs with queries. Using Access to store the data makes it easier to enforce data integrity than storing all data in Excel.

Uploaded Files:

• http://files.gove.net/shares/files/14f/jrdjacob/DietDataProject.xlsm
• http://files.gove.net/shares/files/14f/jrdjacob/DietData.accdb
• http://files.gove.net/shares/files/14f/jrdjacob/VBA-Final_write-up.pdf