formal system for creating or recording purchases

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

Please follow and like us: