Excel assignment

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.