Lab Description Assume that you are a new staff member in a small retail firm. The other employees have very limited technology skills, and the firm has no formal system for creating or recording purchases. Currently, they just scribble purchase orders on random pieces of paper. Over the past several years, the firm has made many errors when recording its payables, and many bills have not been paid on time.
Lab 1 – ACTG 378 (Winter 2018)
Requirements Turn in an electronic copy of your completed lab to the Canvas site. All requirements should be
included in one Excel file. Name your file: yourlastname_lab1.
Lab Description Assume that you are a new staff member in a small retail firm. The other employees have very
limited technology skills, and the firm has no formal system for creating or recording purchases.
Currently, they just scribble purchase orders on random pieces of paper. Over the past several
years, the firm has made many errors when recording its payables, and many bills have not been
paid on time.
Being an energetic and skilled new member of the firm, you offer to develop a purchases system.
You suggest that this system could be constructed using Excel (a program that is currently in use
on all of the firm’s PCs and is used by most employees). The system will allow for easy entry of
purchase orders, which can be mailed to vendors. The system will also keep track of payables
and indicate when each account should be paid. You will also create some internal controls
(general and application controls) for this new system and make it user friendly.
You will need to complete four major steps in order to accomplish this task:
1) Prepare a purchase order form and include internal controls (Part A)
2) Prepare a purchases register (Part B)
3) Create macros that will make the purchase order entry easy for the other employees at your
firm (Part C)
4) Respond to the questions (Part D)
Part A: Prepare the purchase order form
1) To get an idea of the form’s design before you begin, examine my example form (Figure 1).
2) Select cells B2-M9 and Merge the cells. Now go to the Insert menu and select Wordart to
create a logo for the business name. Don’t use all of the space in the merged cells. Now click
on Insert and select Text Box. Put the text box in the free area in your merged cells. Type the
company name and address. On the last line of the text box, type Purchase Order. Format the
text as you desire.
3) Merge cells B12-M14. Type the words “Vendor Information”. Make the text centered, bold,
and a large font.
4) Create the vendor information section according to my example. Underline cells using the
border button in the Font submenu.
2
5) Enter the “Order Information” heading and format it the same as you formatted “Vendor
Information.” Enter the order information details as I did on my sheet.
Input Formulas to the Purchase Order and Create Vendor and Price Lists
1) The purchase order form will automatically fill in all of the vendor information once the
name of the vendor is input to the order form. This is your first input control. We will
prevent any input errors from being made on many parts of the purchase order. You will
accomplish this by creating a valid vendor list on another sheet. This is a list of vendors that
have been approved by management. We will only allow orders to be made to these vendors.
Notice that this will prevent errors in order addresses and prevent fraudulent orders. Name a
sheet “Vendors” and create a table that looks like mine (see Figure 2). Also create a few
companies of your own and fill in their information in the vendor table. The vendor table is
basically a list of all of the vendors that sell products to the client company. Sort the table by
vendor name.
2) Prices will also be filled-in automatically when an item number is entered into the purchase
order form. Name a sheet “Prices” and create a table with headings identical to mine (see
Figure 3). Please put five products into your table. You should choose your own product
descriptions and prices.
3) In order to automatically fill in data on the purchase order form, you will use VLOOKUP
functions (which can be accessed in the Formula menu within the lookup and reference
functions). Users will input the company name, and all other cells related to the company
will have a VLOOKUP function. When you create the functions to fill in vendor information,
refer to the cell where the company name is entered into the purchase order when you are
asked for the lookup value. For the table array, choose all data from the vendor table, but not
the headings. For the column index number, type the column number (this is the column
number in your table, not the column heading at the top of the sheet) where the specific data
field you want to retrieve is located. For example, when you create a VLOOKUP function in
the Contact cell, you would enter “2” for the column number, because the Contacts are listed
in the second column of the data from the vendor table. Also notice that the only portion of
the VLOOKUP function that changes for the various vendor information fields is the column
number. Hint: this means that if you create absolute addresses for the other two references in
the VLOOKUP function (i.e. the lookup value and the table array) you will be able to copy
this formula to many cells, and you will only have to alter the column number.
4) Now make sure that these formulas are working. Simply type in a company name on the
purchase order form (you must use a name that is in your vendor list) and all of the #N/A
cells should be filled in with the correct data. (Note: your valid vendor list and product price
lists must be sorted in alphabetical order). Notice that your cells will have #N/A in them
before you input a company name, while mine are blank. You will make this look better in
the next requirement.
3
5) Filling in the order information items also requires VLOOKUP functions. The description
and unit price fields will be filled in automatically when you enter an item number. So, for
these VLOOKUP functions, you will refer to the cell where the item number is entered for
the lookup value. The table array will be the prices table, and the column index number will
again correspond to the column that matches the data you wish to pull out of the prices table.
Notice again that #N/A does not appear on my sheet for the unit price and description. This is
because I have nested the VLOOKUP function inside an IF function. My IF function tells
Excel not to input anything into the description or unit prices cells when the item number cell
is blank. When the item number cell is not blank, the IF function uses the VLOOKUP
function to fill in the cells. Hint: the If function looks something like this
=IF(C33=””,””,VLOOKUP(C33,Prices!$B$4:$D$6,2))
6) In this example c33 is a cell where the item number is entered, and the VLOOKUP function
is retrieving information from the second column of the prices table. The double set of quotes
is how Excel describes a blank space. So, this function says to leave the space blank if the
item# field is blank, or fill in the space with the VLOOKUP value if the item# is filled in.
Create similar nested IF functions on your sheet everywhere there is a VLOOKUP function.
7) The total and subtotal fields have sum formulas. The total price calculation is a sum function
within an IF function that is similar to the IF function I used for the description and unit price
cells.
8) Now make an input control on the PO sheet. I want an input control on the quantity field.
Let’s assume that orders cannot be made for quantities greater than 100 without the owner’s
approval. Select the cells where the quantity ordered will be input. Go to Data, then Data
Validation, and then set the restriction to whole numbers less than 101. Create an input
message that tells the user about the restriction, and an error message that informs the users
why an amount over 100 cannot be entered.
9) Make the date field automatically update to today’s date. This is easily accomplished with an
Excel function. Enter “=Today()” in the date cell, and you are done.
10) Now, you will add an access control that prevents any unauthorized changes to the valid
vendor list. This is a control that prevents users from making unauthorized changes to the
information system. Switch to the vendor worksheet. Unlock any cells you want users to be
able to change: select each cell or range, click Format, Format Cells, Protection (Format is
found in the Home main menu). Clear the Locked check box. For this sheet, leave all cells
locked. Next, you will set protection on the Purchase Order sheet. In this sheet, you will need
to unlock any cells where users make inputs. Hide any formulas that you don’t want to be
visible. You don’t need to unlock buttons or controls for users to be able to click and use
them. Click Protect Sheet in the Format menu when you are ready to protect a sheet. Type a
password for the sheet. Note: You can also view and access protections from the File main
menu.
4
Note: Make sure you choose a password you can remember, because if you lose the
password, you cannot gain access to the protected elements on the worksheet again! Now,
insert a New Comment in cell A1 of your purchase order sheet (comments can be found in
the Review menu). Make sure to type your password here, or I will not be able to grade your
lab. Obviously, you would not want to type the password on this sheet in real life!
Believe it or not – the purchase order form is now done! Time to move on.
Part B: The Purchases Register
The purchases register is a sheet that keeps track of all purchase orders and determines when
account balances should be paid. Your employer wants all payment dates and amounts to be
automatically determined by the system. This is useful because the business will no longer miss
any payments, and they can structure payments to always take advantage of any helpful discount
terms.
1) Create a purchase register table with the same column headings as mine (see Figure 4). Name
the sheet “Register”.
2) The first seven items on the register will be automatically entered from the purchase order
form. This will be discussed later.
3) The remaining items are calculated. The formulas are:
Cost of Credit = (Net Days-Discount Days)/360*Interest rate. (You will assume an
interest rate of 10%. Think about what this means. You are calculating your cost of
paying a balance early).
Date to Pay = This will require a nested IF function. If the cost of credit is less than the
discount you receive, then you should pay the balance within the discount period because
it saves you money. Pay on the last day of discount in this case. If you can earn more than
you save, then pay off the balance on the last possible day.
Amount to Pay = Also an IF function. If you pay within the discount period, then you
should pay the balance minus the discount. If you pay after the discount period, you will
have to pay the entire balance.
4) In cell A3, type in the function for today’s date “=Today()”.
5) Now, format the payment date different colors for payment dates before and after today’s date
by making a reference to the cell with today’s date. Make the payment days that have already
5
passed red, and make the days that have not yet occurred blue. Use the Conditional
Formatting option under the Home menu.
6) Don’t protect this sheet. The problem here is that we cannot paste to a protected sheet, and
you will not be able to move the information from a purchase order to this sheet without
writing custom VBA code.
That takes care of the register.
Part C – Macros 1) Record a simple macro for practice purposes. Excel lets you to record macros that allow you
to perform a sequence of actions by running the macro. First set up a sheet called Macro. To
begin recording a macro, go to the Developer Menu and click on Record Macro.
Alternatively, you can select View, Macros, Record Macro. Type in a name for your macro
when prompted (use the name “Simple”). This will be the name used to save the macro in the
workbook. You can now record any actions that you perform. We will record a macro that
takes you from the PO form to the Purchases Register. Start the recording while on the PO
sheet and then move you cursor to the Register sheet and put the cursor on the cell A1.
2) Now stop the recording. Go to the Insert menu, select Shapes and choose a shape for a button.
Draw the shape on your PO sheet below the items list. Now right click on the button and use
Assign Macro to assign your “Simple” macro to the button. When this is finished, give your
button a name (e.g., “Go To Register”) by typing text into the button. Whenever you click on
the button, it will automatically take you to the register.
Now you know how to record a macro and assign it to a button. Next, you will use this skill
to make everything that you have created so far work together with the use of a single
button.
3) The creation of two macros will make this purchase order program very easy for the client to
use. I know that we don’t have time to cover how to write macros using Visual Basic, so I will
tell you the proper syntax for anything that cannot be recorded. One macro will make data
entry to the purchase order very simple, and the second macro will take purchase order
information and put it in the purchases register.
4) Macro #1 – This macro will do two things for you: 1) input a new PO number automatically
and, 2) ask for the vendor name. You cannot record all of this macro, you will have to create a
little code for it.
5) Select Developer and then Macros (or View, Macros, and then View Macros). Type in a name
for the new macro (“Start”) and then click on Create. Now you can type your own code. The
6
syntax for this macro is included in Figure 5. I called it “Start.” The first lines of code create
an input box that prompt the user for specific information and inputs the information into
specified cells. Note that your cell references must match your own purchase order sheet. So,
this code asks the user for the name of the company, and then inputs the name into the
appropriate cell on your PO sheet.
6) The second portion of the subroutine can be created using the macro recorder. I recorded this
code, and then combined the recorded code with the first two lines that I typed simply by
copying from the recorded macro and then pasting to the bottom of the Start macro.
7) You need to record the actions of: 1) copying a purchase order number from the register sheet
(I will also tell you how to create this number), and 2) pasting the number into the PO# cell on
the PO sheet. Notice that you must choose Paste Values and only paste the value into the cell.
8) You can use a little imagination and built in Excel functions to generate the next PO# for
excel. One method is to use the MAX function to find the highest existing PO number, and
then add some increment to this number. This is how I accomplished the task. Then I copy the
result of this function from the register to the new PO using the macro recorder. I put the
MAX function in cell A2 of my register sheet (“=MAX(B:B)+10”). Note that you need to
enter one row into the Purchase Register before this will work. So, manually enter the first
entry to the sheet.
8) Macro #2 – This macro will post all of the information from each new PO to the register.
9) This macro can be completely recorded. You need to copy the necessary material from the PO
to the register. You will also need to copy the formulas in the register for each new purchase
order that you post. There is a nice trick you can use to fill in the register without writing any
fancy code to find the appropriate cell location. By inserting a blank row into the register
before pasting a new row, you can enter new data without deleting the existing data.
10) To complete the macro section, you will need to create buttons as you did in the first
requirement of Part D. Then assign macros to the buttons. I put a Post to register button on
the PO form.
Part D – Understand Your System and its Weaknesses
Now you need to take some time to understand how your system works. Study the purchase
order system you have just created. Notice that it could still use many improvements and
additional internal controls. Identify 5 significant weaknesses of the system you have
created. Write a brief explanation for each weakness (explain why the weakness would result
in problems for users of this system). Type the weaknesses and explanations on a new
worksheet in your Excel file called “Weaknesses.”
7
Figure 1
8
Figure 2
9
Figure 3
10
Figure 4
11
You cut and paste this code from a recorded macro.
You type this line of code