Description
Unformatted Attachment Preview
ACCT 3080 –
Using Excel for a Basic Business
Spinyl Vinyl (SV) has expanded their inventory to include CDs in
addition to vinyl records. The company is still determining how to set
up their AIS, but has asked you to create a simple, automated system
to track inventory and create sales invoices while they are developing
the new system. You will do so with Excel, by completing the
following:
Part 1
• Data Entry
• Name and Group Worksheets
• Formatting
• Sorting and Filtering
• Page Layout
• Forms
Part 2
• Data Validation
• Formulas
• Conditional Formatting
• Add a title
Part 3
• Sales invoice
• VLOOKUP
Part 1: Data entry, name and group worksheets, formatting, sorting and
filtering, page layout, and Forms.
Workplace need: Allow SV managers to track inventory quantities on hand.
We will begin by creating one Excel workbook. Save your file as:
YourLastNameFirstName.xlsx
•
Data Entry, Naming, and Grouping Worksheets
•
On sheet 1, create a Vinyl Inventory table.
o Highlight the Vinyl Inventory table below by clicking on the upper left
corner (the cross-arrow box that appears when you move your cursor
over the upper left corner of the table). Note: Do not highlight the title
“Vinyl Inventory”.
1
•
•
•
o Copy the table.
o Paste the information onto sheet 1
On sheet 2, use the same process to create a Disc Inventory table.
Name sheet 1 Vinyl by double-clicking on the tab labeled Sheet 1 and typing
Vinyl. Change the tab color by right clicking on a tab and selecting tab color.
Use the same process for Disc Inventory, name sheet 2 Disc.
Vinyl Inventory
Product
Artist
Number
V1000 The Beatles
V1100 Gear
Daddies
V1150 Tame
Impala
V1200 Hüsker Dü
V1250 J. Roddy
Walston &
The
Business
V1275 Melody
Gardot
V1300 My Morning
Jacket
V1305 The Beatles
V1350 Charlotte
Gainsbourg
Source
I
D
D
Title
Rubber Soul
Billy’s Live
Bait
Currents
Quantity
on Hand
35
60
Cost
Price
5.50
5.50
11.00
10.00
15
5.35
9.00
D
D
Zen Arcade
Essential
Tremors
40
65
5.75
5.35
11.00
9.00
I
25
6.25
10.50
D
My One and
Only Thrill
The Waterfall
30
5.00
9.50
I
I
Revolver
Rest
35
25
5.00
7.25
11.00
11.25
Disc Inventory
Product
Number
D1400
D1500
D1700
D1800
D1900
D2000
D2050
•
Artist
Source
Nirvana
Wilco
D
D
Brick +
Mortar
Jason Isbell
and the 400
Unit
Nick Cave &
The Bad
Seeds
13th Floor
Elevators
Beck
D
D
I
I
D
Title
Nevermind
Yankee
Hotel
Foxtrot
Dropped
The
Nashville
Sound
Your
Funeral My
Trial
Easter
Everywhere
Colors
Quantity on
Hand
50
40
Cost
Price
4.50
4.55
8.50
9.00
10
4.60
8.50
25
5.75
10.50
15
5.25
10.50
25
10.25
26.50
20
4.75
9.50
Formatting
2
o
o
o
•
•
•
Format the data within the worksheets to widen the columns and format
the cost and price columns as currency, with 2 decimal places.
Format each inventory list as a table. Highlight the entire table (including
Header row), on the Home tab, in the Styles section, select Format as
table, select a table style. Note: be sure to check the “my table has
headers” box.
Note that drop-down arrows appear next to each header row. These
enable you to sort and filter the data.
Sorting and Filtering
o Click on the Vinyl Inventory sheet.
o Using the drop down arrows, use the sort and filter functions to:
o Alphabetical Sort. Sort by Title, ascending. Select the arrow next to the
Title column heading, click on sort, A to Z.
o Numerical Filter. Show only products that have a cost between 5.00 and
5.35. Select the arrow next to the cost column heading, then you can
either select the click boxes next to the data you want, or click on number
filters, choose Between, enter 5.00 AND 5.35.
▪ Which product is displayed last in the list? Type the answer to
this question on your Vinyl Inventory tab in cell A 30 and B30.
Include the Artist and Title, respectively.
o Additional numerical filter. Now display only products with a price of
11.00. Using drop down arrow, check the box next to $11.00.
▪ Which product is displayed? Type the answer to this question
on your Vinyl Inventory tab in cell A 31 and B 31. Include the Artist
and Title, respectively.
o Display all products by turning off filtering. In the Home tab, editing
section, locate the sort and filter icon, in the drop-down list, click Clear.
o Note, Clear removes all filtering criteria, but leaves the drop-down arrows
visible. FYI (do not do this, next step, this is strictly FYI): to remove the
drop-down arrows completely, in the Home tab, editing section, locate
the sort and filter icon, in the drop-down list, click the Filter icon.
o Note: you may also use the sort and filter icon in the edit section of the
Home tab to apply sorting and filtering.
Page Layout
o Change page layout to landscape. Page Layout tab, orientation,
landscape. Note you may also make other printing decisions here, such
as setting margins and hiding gridlines.
Forms
• Workplace need: A tool to improve data entry efficiency
o
o
Entering a lot of data in an Excel spreadsheet can be tedious, particularly
if you have to enter data horizontally across columns. It is also more
difficult to catch errors if you cannot see all the data on the screen at
once. Using a Form to enter data allows you to see all the data vertically,
which is easier for some data entry. This is an Excel feature that might
help you in the future.
Add the Form command to the main toolbar, if it is not already there.
Look to the right of the undo/redo buttons. Is there a small blue and
white icon next to it? (See picture with red arrow below). If yes, point your
mouse to the icon and wait for the description to pop up. It should say
3
Form. If the Form icon is not there, do the following: Click the down arrow
to the right of the undo/redo buttons, select more commands, select all
commands. Scroll down to form, highlight, add and click ok (see the
screen shot below) now the Form icon should be visible in the top toolbar.
Note: The form command allows you to enter data in a form view, rather
than a spreadsheet view.
o
o
With your cursor in the Vinyl table, click the form icon then within the form
dialog box, click New and add the following products using the form.
Be very careful when inputting data. Any misspelling or other typo will
result in inaccurate, unreliable accounting records.
4
Product
Number
V1400
V1425
V1450
V1500
V1550
•
Artist
Nick Drake
Run the
Jewels
Foo
Fighters
The Beatles
Kendrick
Lamar
Source
D
I
D
I
D
Title
Pink Moon
Run the
Jewels 3
Concrete and
Gold
Help!
Damn
Quantity on
Hand
45
28
Cost
Price
5.25
5.75
9.75
10.00
60
5.80
10.00
15
45
5.50
6.50
10.00
12.00
Workplace need: Use Forms to search spreadsheets for specific data
• Say you want to locate Revolver on Vinyl. Open the Form and click Criteria.
• Type Essential Tremors in the Title field.
• Click, Find Next.
• The Essential Tremors record will appear in the form.
• Search for other products by quantity on hand, cost, and price. This feature is
very helpful when you have thousands of lines of data.
• Search for the Product Number V1114. V1114 is not a product on this list.
What result does the search return for this query?
5
Part 2: Data validation, formulas, and conditional formatting
•
•
Formulas and Conditional Formatting
o Workplace needs: Determine which products are the most profitable per
unit. Alert the manager when quantities drop below a reorder point.
o We want to create a single table with both types of product. Copy and
paste the tables from each worksheet to a new worksheet to form a single
table. Note: Copy the header rows from only one table. Name this new
sheet Total inventory, color the tab.
o Create additional columns to display:
▪ Gross profit per product
▪ Gross margin % per product
▪ Total Cost per product line
▪ Total Price per product line
▪ Enter appropriate formulas to calculate each of these for each
product line. If you do not know the equation to calculate, Google
it. Note: formulas will “copy down” to all rows as long as the data
is still in a table format (it will be if you copied the two inventory
sheets over correctly)
▪ Format as appropriate. (Percentage for gross margin, currency for
the other products, two decimals).
o Add formulas to the bottom row to display “Total quantity on hand” by
entering an autosum function to the entire row.
▪ First, freeze the top row of the table so that it stays visible as you
scroll down. On the View tab, select the Freeze Panes icon and
freeze the top row.
▪ Place the cursor in the first empty cell under the quantity on hand
column.
▪ Select Formulas, autosum, sum.
▪ If your table is still in table format, the Total row should now be
formatted. Move your cursor over to the Cost column (still in the
Total row). You should see a drop down arrow that has a listing of
formula options.
o Use the formula options as follows:
▪ Average: Cost, price, gross profit, gross margin %
▪ Sum: Total cost, Total price
▪ FYI: Note the other Formula options, Count, Min, Max, etc.,
available.
Data Validation
o Workplace need: Make sure that the data entered is reliable (error-free).
Consider different ways to achieve this goal.
o Create a data validation rule to ensure that the Product Number has only
five digits.
▪ Highlight all the product numbers. Do not highlight by the whole
column. Highlight only the cells with product numbers in them.
▪ Select Data tab, Data tools, data validation.
▪ There are three tabs:
▪ Settings, allow text length, equal to, 5.
▪ Input message, Title, Product Number, Input message, Product
Number must be 5 digits long. Be sure “Show input message
when cell is selected” is checked.
6
Error alert: Set style to “Stop” Title: “Product Number error”
Error Message, “Product Number must be exactly 5 digits.” Be
sure “Show error alert after invalid data is entered” is checked.
▪ Click OK.
▪ Check to see if your Message shows up on every data cell in the
column.
▪ Test the above rule by trying to enter an Product Number with
more or less than 5 digits.
Add the following rules. Adjust settings appropriately and provide an error
alert. You must provide an appropriate input message and error message
for each. Consider what input and error message makes sense for the
column and the data validation rule. The messages should be useful and
descriptive to the inputter.
▪ Quantity on hand must be a whole number between 0 and 250.
Error alert style is information.
▪ Cost must be a decimal greater than 0. Error alert style is
warning.
▪ Price must be a decimal greater than or equal to .25. Error alert
style is stop.
▪ Test the rules to see if they provide the desired controls.
What are the options following the error message when you “break the
rule” on Cost (negative #)? Write your answer in cell A30 on the Total
Inventory Tab.
What are the options following the error message when you “break the
rule” on Price? List the options into cell A31 on the Total Inventory Tab.
Why are the options for Cost and Price different? Put your answer to this
question into cell A32 on the Total Inventory Tab.
What is the difference between an Input and Error message? Why should
you use both? Put your answer in Cell A33 on the Total Inventory Tab.
What is the difference between the three types of Errors (Stop, Warning,
Information)? Why would you use each one? Put your answer in Cell A34
on the Total Inventory Tab.
▪
o
o
o
o
o
o
•
Conditional Formatting
o
o
o
Apply conditional formatting to alert management when products fall
below minimum levels.
▪ Highlight the quantity on hand column, select Home tab,
▪ In the Styles section, select conditional formatting.
▪ Choose New rule,
▪ Format all cells based on their value
▪ Format style: Icon Sets,
▪ Icon style: your choice of icon style
Set all quantities greater than or equal to 50 (green), greater than or
equal to 15, (yellow), others, (red). Note: under type, make sure to select
number type first.
Apply conditional formatting to alert management when gross profit
falls below 4.50 per product by formatting those products in red text.
▪ Highlight gross profit column, select Home tab, Styles,
conditional formatting, select highlight cells rules, select less
than, enter 4.50, with red text.
7
o
Adding a Title
▪ Add a title to the table. Add a row by highlighting row 1, right-click
and select Insert.
▪ Type: Total Inventory in cell A1.
▪ Highlight from cell A1 to the cell in row 1 above the right hand side
of the table.
▪ Merge and center the title using the Home tab, Alignment, Merge
and center icon.
▪ Increase the font size to 16 and bold the title using functions in
Home tab, Font.
**NOTE: The screen shots in this document are examples of how your screen
should approximately look, but have different data.
Part 3: Invoice with Controls and VLOOKUP
Workplace need: Create an automated invoice with built-in error controls. The
parameters of the form should prevent backdating invoices, entering unrealistic
quantities, avoiding calculation errors, and entering incorrect products.
o
o
Add a new worksheet and name it “Sales Invoice.” Color the tab.
Create an invoice similar to the invoice below, formatting the cells to
appear similar, and then we will add controls. You must make Spinyl Vinyl
8
o
o
o
o
appear in a color other than black, larger, and with a different font from
the standard font (as below).
To personalize your invoice, insert a picture at the top – You may copy
and paste from Google Images to find a picture.
Now add the following controls and formatting.
Add data validation to restrict date entry from backdating. Excel can
automatically fill in the current date in a cell by entering =today(), but SV
allows values to be a date between today and 5 days from now. We will
use data validation to restrict this entry. Put your cursor on the cell where
you want the date entered. Open the Data Validation window. In Settings,
select Date, between, =today() and =today()+5 for the minimum and
maximum. Include an input message. Make this a stop, with error
explanation. You may need to format the cell as Date.
Using data validation, we will create a drop down list for Product Number
within data validation. First, you must assign a name to the list of
Product Numbers within the inventory table worksheet :
• Go to the Total Inventory worksheet.
• Locate the range name box. This is a box in the upper left
corner of the worksheet, just below the command ribbon. It
contains the cell reference. For example, if you put your
cursor on the Product Number of the first product listed in
your table, it will say A3. If you move your cursor down a
row to the next Product Number, it will change to A4.
9
•
o
Now, highlight the list of Product Numbers in the inventory
table. You need to highlight the full list of numbers, not the
whole column A.
• Then type a unique name in the range name box. I
suggest you use the name prod_number. Do not include
any spaces. Hit enter. Don’t worry if your cell reference is
still showing A3 after you hit enter.
• Go back to the sales invoice and highlight the entire
column for Product Number. Then use data validation,
Allow, List. In the Source box for the list, type = and then
type the exact range name you assigned to the Product
Number list (for example, =prod_number). OK. Check your
work to be sure that the list contains all the available
Product Numbers to choose from. Every cell in this
column should now have a drop down arrow when you
click on each cell. Now a customer service agent can
use the drop down list to enter Product Numbers for a
customer sales order.
We will use the VLOOKUP function (in the Formulas tab) to use Product
Number to return Artist, Title, and price from the inventory table. This is a
valuable tool because when a customer service agent selects an Product
Number from the drop down list you created above, we want the invoice
to automatically fill in the artist, title, and price of the product being sold.
Go back to the Total Inventory table.
• Important: Make sure the inventory table is sorted
ascending by Product Number. Check before you go
further. If it is not, use your drop down filter to sort
ascending (A-Z).
• Now go back to the Sales Invoice worksheet. Place your
cursor in the first Artist cell on the sales invoice, and type
=VLOOKUP(
• The first item to enter is the lookup value. Click on the
Product Number cell to the left. This is the value you want
Excel to find in the inventory table. Type a comma.
• The next item to enter is the table array information – this
is the place Excel will search for the Product Number. Go
to the Total Inventory table and highlight all of the data
from the Product Number column through the price
column. Note: Do not highlight the header row. Type a
comma. DO NOT go back to the sales invoice worksheet.
• The last item is the col-index-num (column index
number). This is the number of the column that contains
the information you want Excel to return. In this case, Artist
is column 2, so type a “2”. Note: the Product Number
column is column 1 because this is the first column in the
table array you just highlighted in the step above. Now hit
Enter. You should be taken back to the sales invoice
worksheet. Note that you will see an error in the cell: #N/A.
This will be the case until you select an Product Number
list from the drop down you created. Go ahead and click on
an Product Number in the drop down list. If your VLookup
10
•
command worked, it should return the corresponding
Artist’s name.
o Repeat for Title and price, remembering to select the number of the Title
and price columns as the appropriate col-index-num. This is the only input
that changes from the VLOOKUP you performed above. Format the price
column as currency with 2 decimals.
o Copy all three VLookup formulas down to two more of the remaining
blank rows. You will see the #N/A errors in all of these cells.
o Format all of the columns appropriately, so that the full data appears in
each cell (e.g., you should be able to see the whole title)
o Now, create a sales invoice. Your customer, Drastic Plastic has just
called in to order three different products. Make up an address for the
customer and pick whichever three products (vinyl or CDs) and quantites
you want. Use the current date for sales order date. You should be able
to use the drop down for Product Numbers, and the Artist, Title and price
should be returned by your VLOOKUP formulas. An example is shown
below.
▪ Use appropriate Formulas to calculate the other amounts as
necessary.
▪ Format cells in each column as appropriate (e.g., date, currency,
number).
▪ Format the Deposit to show up in red font. This deposit is an
amount paid by the customer at the time of order. The Balance
Due is the remaining amount owed. Use a fill color to call attention
to the Balance due.
▪ Create a cell and enter a formula for Date due. All balances are
due 10 days after the sale date. Your formula should calculate the
due date as 10 days after the date of the invoice.
▪ Remember: this is an invoice to be sent to your customer. It
should look professional. This means all formatting must be
appropriate, all spelling must be correct, and all data must be
visible in each cell.
More information about specialized lookup functions— they can be used to
have Excel search a table and return a certain value. Lookup tables can be
oriented (and thus searched) either horizontally (HLOOKUP) or vertically
(VLOOKUP). Note: This additional information below on V and HLOOKUP is
FYI, do not change your VLOOKUP work from above.
o Formula requires 4 arguments:=vlookup(a, b, c, d) where:
• “a”=cell reference you want to look up
• “b”=name of lookup table or range of cells where the
lookup table is
• “c”=the column in the lookup table you want to return to the
cell
• “d” = true or false
• Put “false” if you need the search to find an exact match
• Put “true” or leave blank if you need search to find a range
• True is the default if left blank
o Rules:
▪ 1) Leftmost (first) column has to include the number that “a” is
being compared to.
11
▪
▪
▪
2) Column must be sorted in ascending order or the lookup
formula will not work.
3) If using a range, the lowest number in the bracket goes in the
left column.
4) Best to name the lookup table using a range name.
12
Purchase answer to see full
attachment
