Of Excel Essay, Research Paper
As accounting professionals, accuracy in financial calculations and the integrity of the raw data are vital to producing useful information. Our clients prevail upon us to be more accurate and more thorough in less time. The tremendous advances in information technology are apparent across most industries. Accountants can gain much from this revolution through advances in spreadsheeting.
Microsoft Excel is an application that can produce an almost infinite number of financial calculations. A way to orchestrate a manipulation of data is to use spreadsheeting. Another more efficient way of manipulating or extrapolating data is by playing a macro.
A macro is a stored sequence of commands that can be imposed on data. Playing the macro initiates the sequence. When the macro is finished playing, the final manipulation or extrapolation is produced. This could be for example the production of a summary report.
An example of this is to view the information in a spreadsheet as raw data. (We must assume the data is all accurate otherwise rubbish in rubbish out). The data is strewn all over the spreadsheet. The macro can be seen as a filter. As we apply the macro to the data, it is like we pour the data through a filter. The information we need is collected in the filter.
Macros are more than just filters however. Using a real world example; an accountant is required to prepare a report showing the changes profit and loss figures over time for interpretation. The macro will not collect any existing data. It will manipulate the data and prepare a report showing percentages and ratios relevant to turnover and gross profit margins etc.
The macro provides this function repeatedly when applied to new data. It can save the time of manual calculation, or a tailored spreadsheet. And it improves accuracy.
Whilst Excel enjoys the reputation as the most widely used spreadsheet application , many users (not just the accounting fraternity) do not get all the power available from this enormously flexible application because they do not venture beyond basic spreadsheets or simple macros.
A recent discussion with a banker at ANZ mortgage services revealed that bank data is being converted from active data (spreadsheets with in-built commands) to raw data to be actioned by macros.
The reasons for the change are several.
When the active sheets have data changed, this has implications for the rest of the spreadsheet. All the linked cells will need to recalculate their cell values according to their formula. This takes time to execute. It may seem like a speed of light adjustment on a state of the art computer with a simple spreadsheet, however some of the Bank’s files were as large as fifty megabytes.
Another reason; the change in the file size when storing only data and not active spreadsheets. Commands such as “vlookup” take up large amounts of memory. When the files are converted to data only the files reduced in size from fifty megabytes to only five.
The final reason is that the data only files improve the integrity of the data. If one cell reference is interfered with or some code is inadvertently changes it can throw out the entire spreadsheet. If a macro is incorrect is can be corrected and applied to the data.
Macros are written in Visual Basic for Applications (VBA code). This is a powerful language that can be difficult to learn. For the beginner, Excel has a record Macro Function. This function records the commands that an individual makes as they are working on a spreadsheet. The sequence of commands can then be stored and replayed with new data.
Below are five years results for Evans Timber Pty Ltd.
My objective is to record a macro that provides the average growth figures over the five-year period. These figures will be represented as percentages in column “J”..
The record macro function is selected, Tools, Macro, Record new Macro. Every keystroke is recorded. The recorded macro is automatically converted into VBA. Even the revisions are recorded, as evidenced below.
Notice here that the “if” command was required. This nullified the prior formula, however the code is still recorded.
After the macro is completed the work done on the spreadsheet can be deleted and the macro applied to the raw data. This is done by Tools, Macro, Play Macro.
A screen will appear as follows, and the Average Growth macro is selected. Press Run and the commands are applied to the spreadsheet.
The results are the appearance of a new data column “J’ with average growth figures expressed as a percentage.
Whilst the record macro function is easy to use, it does have limitations. Most recorded macros are designed to perform a specific task. Most designers are also the users and being under time constraints take shortcuts. This means that the macros can become so specific they will not be useful in even a slightly different situation.
In the example performed above, what happens in the next year where there are six years of trading history? The recorded macro is rendered useless. Writing a macro in VBA can provide a tailored outcome no matter what the data range as the instruction can capture the data range until “blank”, rather than be limited to a prerecorded cell range.
Attempts to change prerecorded macros can become incredibly frustrating.
There are many commands that you need to learn to program VBA, one benefit of the record macro facility is that it can become a ready reference for the 300 VBA instructions.
In all the VBA component can allow for a myriad of functions that recording macros cannot.
There are plenty of texts and courses on writing VBA code. Or for free you can surf the net at www.add-ins/tricks.htm. or www.excelvba/?source=goto www.beyondtechnology/tips.shtml .
In short, be more professional by being more accurate, timely and providing accurate data.
Use spreadsheeting.
Use Macro’s.
(1) Beaman I. R., Ratnatunga, J. A., Krueger, P. and Mudalige, N. (1998). Financial Modelling, 2nd Edition, Quill Press
(2) Lee, J.C., (2000), Business and Financial Statistics Using Minitab 12 and Excel 97, Singapore, River Edge.
(3) Kyd, C. W., Kinata, C., (1992),Complete Guide to Microsoft Excel Macros, Microsoft Press
(4) Kinkoph, S., (1999), The complete idiots guide to Microsoft Excel 2000, Indianapolis Ind, Que.
(5) Albright, S. C., Winston, W. L., Zappe, C., (1999), Data Analysis ans Decision Making with Microsoft Excel, Pacific Grove CA, Duxbury Press.
(6) Berk, K.N., Carey, P., (1996), Data Analysis with Microsoft Excel, Pacific Grove, Microsoft Press.
(7) Wempen, F., Payne, D., (1999), The Essential Excel 2000 Book, Rocklin CA, Prima Tech
(8) Perry, G., (1999), Excel 2000 Answers!, Berkley, CA, Osbourne.
(9) Walkenbach, J., (1999), Excel 2000 Programming for Dummies, Foster City CA, Transworld.
(10) support.microsoft/support/kb/articles/q177/7/60.asp
(11) surfer.ch/opendir/Top/Computers/Software/Spreadsheets/Excel
(12) oclc/oclc/ill_mgmt_stats/reportmc.htm
(13) support.microsoft/support/kb/articles/q177/7/60.asp
(14) add-ins/online.htm
(15) press.oreilly/excel.html
(16) directory.google/alpha/Top/Computers/Software/Spreadsheets/Excel
(17) oreilly/
(18) accountingadvisors/
(19) hqda.army.mil/AAAWEB/auditinfo.htm
(20) mctcmnet.edu/staff/kelly/ac105.html
(21) npo/itrc/courses/spreadsheet.html
Другие работы по теме:
Транзисторний перетворювач з дроселем в первинному ланцюзі
Транзисторний перетворювач із дроселем у первинному ланцюзі на основі найпростішої схеми, із системою керування. Розробка основної структурної схеми, принципової схеми, силової частини, системи керування, силової частини і вузлів системи керування.
Вивчення елементів середовища СУБД MS Access
Характеристика сучасних баз даних. Вивчення складу та призначення різноманітних елементів меню. Сутність об’єктів баз даних та елементів середовища керування СУБД MS Access. Основні засоби опрацювання об’єктів, принцип запуску середовища СУБД MS Access.
Разработка приложения средствами VBA
Разработка графического интерфейса проекта (панель инструментов имеет 6 кнопок). Процедуры разделов программы: документа ThisDocument, программного модуля Module1 и пользовательских форм UserForm1, UserForm2 и Деление_амёбы. Тестирование программы.
Конкатенація строк Assembler
Розробка граф-схем алгоритмів строкової функції з метою наглядного представлення поставленої задачі і розбиття її на менші частини для створення коду програми. Написання функцій arithm proc, string proc, strCat proc на машинно-орієнтованій мові Асемблера.
Анализ макросов в Microsoft Office Word 2007
Изучение понятия, видов и методов создания стандартных макросов Microsoft Office Word - набора инструкций, которые сообщают программе, какие действия следует выполнить, чтобы достичь определенной цели. Функции макрорекордера. Редактирование макросов.
Учет товаров в фирме по продаже аудиотехники
Разработка базы данных для учета товаров в фирме по продаже аудиотехники. Требования к программе: подобрать поля для базы данных - название фильма, тип носитель, продолжительность, качество, цена, код поставщика, жанр, главный артист, код фильма.
Макровирусы
В этой главе рассказано о макровирусах. Подробно описана процедура и методы заражения файлов. Представлен исходный текст макровируса с подробными комментариями. Приведены основные сведения о языке VBA.
Разработка СУБД FoxPro 2.0
Содержание: Структуры базы данных……………………………………………..3 Алгоритм работы программы……………………………………….4 Рисунки, отображающие внешний вид экранных форм и меню….5
Макровирусы
МАКРО ВИРУСЫ В этой главе рассказано о макровирусах. Подробно опи- сана процедура и методы за- ражения файлов. Представлен исходный текст макровируса
Возможности Excel
Большинство пользователей постепенно осваивают те возможности Excel, которые наиболее пригодны для создания документов нужного им типа. Определив для себя оптимальное количество требуемых инструментов, они, как правило, оставляют без внимания множество средств и функций программы. Наиболее честолюбивые пользователи, для того чтобы облегчить доступ к привычным командам, изменяют пользовательский интерфейс Excel.
Работа с макросами в Excel
Работа с макросами в Excel. Создание макроса. Запуск макроса. Назначение макроса кнопке или объекту. Использование Visual Basic for Applications. Создание макроса.
Макросы в Super Calc4
ВЕДЕНИЕ В MACRO. MACRO - это последовательность указаний (MACRO - команд), которые могут контролировать работу таблицы и состояние экрана в SuperCalc4.SuperCalc4 предлагает широкий выбор MACRO - команд.
Internet security
Federal Agency of Education State Educational Institution of Higher Education «Siberian State Aerospace University named after M.F. Reshetnev» Finance and Economics Faculty
Security Essay Research Paper OverviewSecurity is the
Security Essay, Research Paper Overview Security is the discipline of using effective protection measures to safeguard important assets from abuse. In other words, ?security? is about protecting important things. Protection involves not just mechanisms (such as locks and doors), but also proper selection and use of mechanisms.
IT SPREADSHEETS Essay Research Paper Introduction Spreadsheets
IT SPREADSHEETS Essay, Research Paper Introduction Spreadsheets are used in all aspects of financial computing. Shop owners, Company chairman and directors all need to know how their businesses are doing and the main way to do this is by using spreadsheets.
VMI Essay Research Paper Thiscountry
VMI Essay, Research Paper This country’s economy thrives on its trade secrets and without them the economy would lack its competitive edge and economic value. The trade secret laws date
Hackers Essay Research Paper OutlineIn the late
Hackers Essay, Research Paper Outline In the late 1970s, hackers were people who enjoyed learning the details of computer systems. Today, hackers (or crackers) refer to people who break into computer systems.
Computers And Disabled People In Education Essay
, Research Paper Computers and Disabled People According to the United Nations more than 500 million people suffer from some type of physical, mental, or sensory impairment. Despite many efforts disabled people continue to be denied equal opportunities and in some societies still remain isolated. The United Nations has prompted cities and towns throughout the world to provide such seemingly simple, basic, and obvious services as access ramps and sidewalk indentations for the convenience of the disabled people.
Database Design Essay Research Paper Before you
Database Design Essay, Research Paper Before you use Microsoft Access to actually build the tables, forms, and other objects that will make up your database, it is important to take time to design your database. A good database design is the keystone to creating a database that foes what you want it to do effectively, accurately, and efficiently.
Theories Of Symbolic Interactionism Exchange Theory And
Rational Choice Theory Essay, Research Paper This essay will address actions of individuals and the contribution individual actions make to the social structure, how society flows to the actor via the ?Me? and is constructed or reconstructed by the ?I,? giving the ?I? a place in creating society. I will further analyze the theories and explore the impact of norms and values on the decisions by the actors.
The Camera Essay Research Paper The most
The Camera Essay, Research Paper The most important thing to photography is light. The camera is a precise instrument for capturing light. The word camera in Latin means room. The name camera comes from the first invention towards capturing the world on film, the camera obscura or dark room. This invention came about in the 14th century and was used by such artists as Da Vinci and Michelangelo to more accurately draw their subjects onto paper.
Computer Viruses Essay Research Paper Almost every
Computer Viruses Essay, Research Paper Almost every End-user in the world has heard of computer viruses and/or has had one at one point in time. Don?t worry if you haven?t heard about them, you won?t find it in your bloodstream. Unfortunately you may find one in your computer memory or disk storage. ? Some may be as benign as the common cold and others as deadly to your hard drive as the Ebola virus ?.
Intro To Computer Virus Essay Research Paper
Virus What is a Virus? The computer virus is something that has been around almost as long as the Personal Computer (PC) and certainly since the advent of the affordable home computer, but what exactly is a virus? And how does it affect your PC? Is there any way to prevent your hard drive becoming infected and just where do they come from anyway?
Historia De Microsoft Word Essay Research Paper
Es uno de los mejores procesadores de texto, quiz? sea el mejor, sobre todo por su amplia cadena de herramientas con las que se cuenta. Fue creado por la compa??a de Microsoft, en la b?squeda de un procesador que trabajara de una forma m?s eficaz y completa, que se adecuara a las necesidades m?s estrictas de las personas y de quienes buscaban un procesador de textos mejorado.
Managing A PC Essay Research Paper Managing
Managing A PC Essay, Research Paper Managing A Personal Computer 1.1 The AUTOEXEC.BAT file is one of files which loads every time the computer is booted. It contains command lines and procedures to run programs and load settings for the system?s hardware and software configuration. It also may contain command lines procedures to run programs which may clean your system?s hard drive of temporary files and viruses.
Internet Network Of Networks Essay Research Paper
The Internet The Internet is, literally, a network of networks. It is made of thousands of interconnected networks spanning the globe. The computers that form the Internet range from huge mainframes in research establishments to humble PCs in people’s homes and offices. Despite the recent publicity, the Internet is not a new thing.