School of Accounting and Commercial Law

MMPA513 ACCOUNTING INFORMATION SYSTEMS

Trimester Three 2021

Assignment 4: Excel

Marks will be given for satisfactory completion.



Background

:

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 simply scribble purchase orders on random pieces of paper. Over the past several years, the firm has made many errors in 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, as this is a program currently in use on all the firm’s PCs and 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 include some internal controls (general and application controls) for this new system and make it user-friendly.



Required

:

All requirements are to be included in one Excel file. Name your file as [yourfamilyname]_excel.xlsx.

1. Complete all instructions for Labs 2 & 3 (Excel):

a. Prepare a Purchase Order form, Valid Vendors list, and Price list (Parts A-C) and add FOUR controls;

(16 marks)

b. Respond to the questions in Part D (reproduced in abbreviated form below):

1. list the four controls that you added to the PO form or other sheets at the end of Lab 2 AND explain what each added control achieves.

(6 marks)

1

2. identify FOUR additional controls that should be included in this application. You do not need to implement these suggested controls. Write a brief explanation for each suggested control showing why it would be beneficial.

(8 marks)

Marks will be posted on Blackboard. This assessment is worth 30 marks in total (15% of your total course grade).

1

School of Accounting and Commercial Law

MMPA513 ACCOUNTING SYSTEMS

Trimester One 2021

Lab 2 instructions: Introduction to Excel

Objective of this lab:
Through this lab, students will:

• Demonstrate high-level knowledge of the use of Excel as part of an Accounting
Information System (AIS) that will facilitate further analysis and discussion in the
related assignment and in class; and

• Apply an understanding of the expenditure cycle and associated internal controls.

We expect that before the cyber lab:

• Students will have read through the instructions and familiarised themselves with
the basics of using Excel.

We expect that during the cyber lab:

• The lecturer will provide a brief demonstration of the software; and

• Students will follow the instructions below to create a functional purchase order
system in Excel. (NB: students are unlikely to complete all steps during lab time)

These instructions form part of Lab Assignment 2.

Lab Scenario

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 simply scribble purchase orders on random pieces of paper. Over the past
several years, the firm has made many errors in 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, as this is a program
currently in use on all the firm’s PCs and 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 include
some internal controls (general and application controls) for this new system and make it
user-friendly.

2

Instructions
Part A: Prepare the purchase order form

1) To get an idea of the form’s design before you begin, examine a previous finished form
(Figure 1 below). Do not copy the data in this form – make up your own company.

2) Open a new Excel file and rename the first sheet ‘PO’.

3) On this sheet, 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. Do not 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 your own company name and address. On the last line of the
text box, type ‘Purchase Order’. Format the text as you desire.

4) Merge cells B12-M14. Type the words “Vendor Information”. Make the text centred,

bold, and a large font.

5) Create the vendor information section according to the example. Underline cells

using the Border button in the Font sub-menu.

5) Enter the ‘Order Information’ heading and format it the same as “Vendor

Information.” Enter the order information details as in the example.

Figure 1: Example of completed PO form

3

Part B: Create Vendor and Price Lists

You want the purchase order form to fill in automatically vendor information based on the
name of the vendor input to the order form. This is your first input control, helping to prevent
input errors on many parts of the purchase order. You will do 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 help prevent fraudulent orders.

1) Add a new sheet and name it ‘Vendors’. Create a table that looks like the example in

Figure 2 (below). Copy the first two lines of vendor information, then create (make up)
a few of your own (at least four) and complete their information in the vendor table. The
vendor table is a list of all the approved vendors who sell products to the company you
work for. Sort the table by vendor name.

Figure 2: Example of completed vendors list

We also want prices will also be filled in automatically when an item number is entered on

the purchase order form:

2) Add another sheet and name it ‘Prices’. Create a table with headings identical to those
in Figure 3 (overleaf). Enter details for at least five products in your table with your own
descriptions and prices. Choose products suitable to the company you named in Part
A, step 3. Sort the table by item number.

Figure 3: Example of completed prices list

4

Part C: Add Formulae to the Purchase Order;

1) You will use VLOOKUP functions (which can be accessed in the Formula menu within
the lookup and reference functions) to automatically fill in data on the PO form. 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 on the purchase order when you are asked for the lookup
value. For the table array, select 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.

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.

2) Now make sure that these formulae are working. Type in a company name from your
vendor list on the purchase order form and all the #N/A cells should fill in with the
correct data. (NB: your valid vendor list and product price lists must be sorted in
alphabetical/numerical order for this to work correctly). Notice that your cells will have
#N/A in them before you input a company name, whilst they are blank in Figure 1. You
will make this look better in the next step.

3) Filling in the order items information 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 extract from the prices table.

Notice again that #N/A does not appear on the example sheet for the unit price and

description. This is because it uses a nested VLOOKUP function inside an IF function.

This IF function tells Excel not to show 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: an IF function looks like this:

=IF([logical test],[value if true],[value if false])

5

For example:

=IF(C33=””,””,VLOOKUP(C33,Prices!$B$4:$D$6,2))

In this example, C33 is the cell where the item number is entered, and the VLOOKUP
function retrieves information from the second column (i.e. Description) of the prices
table. The double set of quotes (“”) is how Excel describes a blank space. So, this

function says if the item number field is blank, leave this space blank too, but if there is
data in C33, fill in the space with the VLOOKUP value.

4) Create nested IF functions on your PO sheet everywhere there is a VLOOKUP
function.

5) Add SUM formulas to the total and subtotal fields, then nest these within an IF
statement as above so they appear blank before items numbers are entered.

Hint: when writing the formula for the total field, Excel will not recognise the item
sub-total cells as blank since they contain formulae – use the first item number field
for your logical test instead.

6) Now make an input control on the quantity field of the PO sheet. Let’s assume the

owner’s approval is needed before ordering quantities greater than 100. Select the
cells where the quantity ordered is entered. Go to Data, then Data Validation, and then
set a 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.

7) 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.

8) Now, add an access control that prevents unauthorised changes to the valid vendor

list. Switch to the Vendor sheet. 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.

9) Next, you will set protection on the PO sheet. On this sheet, you need to unlock any

cells where users make inputs. Hide any formulas that you do not want to be visible.
You do not 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: 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 on the Review menu). Make sure to type your password

here, or your lecturer will not be able to mark your lab. Obviously, you would not

type the password on this sheet in real life!

6

Part D: Add internal controls;

11) Implement FOUR more internal controls on the PO sheet, Vendor sheet, and/or the
Prices sheet, remembering the hierarchy of controls (prevent, detect, control). You
choose your own controls, but you cannot repeat the controls given above or
implement the same control more than once.

12) Document your additional controls by creating a new worksheet called

“FourControls.” On this worksheet, list the controls that you added AND explain what
each added control achieves.

13) Now take some time to understand how your system works. Study the system you

have just created. Notice that it could still use many improvements and additional
internal controls.

14) Identify FOUR additional controls that should be included in this application. You do
not need to implement these. Write a brief explanation for each suggested control
that explains why it would be beneficial. Type these suggested controls and
explanations on a new worksheet called “Improvements”.

Hint: be specific to controls over purchases and that could be implemented in THIS
system (i.e. within/around Excel).

This completes the Excel lab.