Excel 2010/2011/2013 Inventory Project

This lesson introduces multiple-worksheet and macro tools.

Instructions:

Read through the set of learning objectives. This is what you are supposed to know how to do when you finish the lesson.

Make sure you understand the terminology in the key terms table. It is there to help you if you get stuck on an instruction.

Complete every step in the assignment, in the order given. Do not skip steps.

When you have done all of the steps, fill out the Check for Understanding (CFU) form.

A completed CFU is required for every assignment, both in-class and homework. Turn it in during your next class. 

The CFU forms are not to be submitted by email. Only printed hardcopies are allowed.

Summary of Key Points:

Simultaneously create the same form in multiple worksheets.

Hide and unhide columns.

Use cell references to other worksheets in a formula.

Use paste-special to replace formulas with their values.

 

Key Terms and Concepts

Inventory

An inventory is a count of products on hand. Inventories are taken at regular intervals: once a week, once a month, once a quarter, and so on.

The purpose of an inventory is to determine how many items were used or sold during the period. For each item a standard formula is used: Starting Inventory amount + purchases during the period – ending inventory amount.

The starting inventory for each period is the ending inventory from the previous period.

The ending inventory is found by counting the stock on hand at the end of the period.

Margin and Profit

The difference between the cost of each unit of a product and the price that it sells for is called the margin.

The formula is Price – Cost = Margin.

Multiplying the margin times the number sold during a period gives the profit on that item for that period.

The formula is Margin * Number Sold  =  Profit.

References to cells in other worksheets

When you want to refer to a cell in a different worksheet, include the name of the other worksheet, followed by the ! symbol, before the cell reference. Example =Sheet2!C4

Reorder point.

The number of items you expect to sell in the period between the time when you order more inventory and the time it arrives. Each product has a different reorder number. When the inventory level reaches the reorder point for an item you order more.

You can use an IF function to decide when to order more inventory. IF current inventory < reorder point, order more, if not, do nothing. For example =IF(C4<100,144,0)

Select all sheets

An option on the worksheet tabs that is used to enter the same content into multiple worksheets at the same time.

Unit Size

When taking an inventory, you have to know the size of the item you are counting. Catsup, for example, comes in several kinds of sizes - packets, bottles, and cans.

Inventory Project

In this project we are going to practice skills covered in the Lookup and IF function lessons, create and use a macro, and learn how to write formulas referencing cells in multiple worksheets. You will also be asked to analyze some data and do some critical thinking about product management. 

Build three copies of a form

1.       Open a new workbook in Excel.  2010 and 2011 will show three worksheets. In Excel 2013 you will need to insert two more worksheets.

2.       Right-click on each worksheet tab. Rename the worksheets LASTW, THISW, and Summary.

3.       Right-click on any tab. Click Select All Sheets. The tabs all turn white. Anything you do in one worksheet will be copied into the other worksheets.

4.       Click in cell A1. Use the merge and center icon [ALT  H  M] to merge cells A1:H1 and cells A2:H2.

5.       Create the form as shown below. It will automatically be copied into all of the selected worksheets.

 

 

A

B

C

D

E

F

G

H

1

Beverage Sales

2

 

3

Item

Units

Stockout

Order

Starting

Purchases

Ending

Sold

4

Beer 1

Can

100

144

 

=IF(E4<C4,D4,0)

 

=E4+F4-G4

5

Beer 2

Bottle

100

144

 

=IF(E5<C5,D5,0)

 

=E5+F5-G5

6

Soda

Can

100

144

 

=IF(E6<C6,D6,0)

 

=E6+F6-G6

7

Juice

Bottle

100

144

 

=IF(E7<C7,D7,0)

 

=E7+F7-G7

8

Wine

Bottle

100

144

 

=IF(E8<C8,D8,0)

 

=E8+F8-G8

 

6.       Deselect the “select all sheets” feature by clicking on a worksheet tab other than the one you have been working in (choose one where the name is not formatted in bold text.).

7.       Right Click on any of the worksheet tabs. Select Move or Copy and make a copy.  In the dialog box click on Create a copy. Click OK.

8.       Right click on the new worksheet tab. You’ll know it is the copy because it will have (2) as part of its name.  Change its name (rename it) to Purchases.

 

Complete the LASTW Worksheet

9.       Click on the LASTW tab. It should show the Inventory Form that was created in steps 4 to 8. Type “Last Week” in cell A2.

10.   In column E, enter the starting inventory values as 340, 160, 80, 100, and 60.

11.   In column G, enter the ending Inventory values as 152, 88, 64, 96, and 62.

The If Functions in column F will show that you ordered Soda and Wine.  “Wine” refers to wine coolers, small bottle of individual servings.

 

A

B

E

F

G

H

1

Beverage Sales

2

Last Week

3

Item

Units

Starting

Purchases

Ending

Sold

4

Beer 1

Can

340

=IF(E4<C4,D4,0)

152

=E4+F4-G4

5

Beer 2

Bottle

160

=IF(E5<C5,D5,0)

88

=E5+F5-G5

6

Soda

Can

80

=IF(E6<C6,D6,0)

64

=E6+F6-G6

7

Juice

Bottle

100

=IF(E7<C7,D7,0)

96

=E7+F7-G7

8

Wine

Bottle

60

=IF(E8<C8,D8,0)

62

=E8+F8-G8

Analysis Practice – What do the numbers tell us?

Take a look at the numbers sold. You should notice that you only sold 4 bottles of juice.  This number is too low; you would have sold more than four bottles of juice in one week, so you know that there is something wrong with the report. One of the numbers in E7, F7 or G7 must be wrong.  Don’t worry about errors like this, over two periods your total number sold will be correct. If the number sold is too low in one period due to an error it will be too high the next period (and vice-versa). 

Compare the number sold for the two beers.  A manager would look at the big difference in sales and try to figure out why Beer2 is not selling as well as Beer1. Then he would try to fix the problem. 

Critical Thinking Practice - What should we do to improve sales?

Should you try to replace the products that don’t sell well with ones that would sell better? That decision would depend on how much profit you made on the sales of each type of item.  Sometimes a low volume product is very profitable. If you sold cupcakes at $10,000 apiece you would be very happy if people were buying them, even if sales were low.

Can you think of some reasons why one beer would sell more than another?  They could have different prices, come in different containers, come in different sizes, one could be a nationally known brand name and the other a local beer, they could have different flavors/tastes/calories, they could be sold from different locations inside the cafe, how and where the beers are listed on the menu might matter, and maybe one of the beers was on sale. In marketing they call this type of minor product differences the four P’s: Product, Price, Place and Promotion. In real life a manager would look at the Beer2 sales and make some changes to one or more of the four Ps. Then they would check the sales again after the next period to see if their new strategy was more successful.

Complete the THISW Worksheet

12.   Click on the THISW tab. Type “This Week” in cell A2.

The Starting Inventory values for this week = the Ending Inventory values from last week. We don’t have to, and shouldn’t, re-enter the data. Instead we will use formulas that refer to cells in the LASTW worksheet.

13.   In Cells E4:E8 enter formulas referring to cells G4:G8 in the LASTW worksheet. For example the formula in cell E4 would be =LASTW!G4.  Don’t forget to include the exclamation mark ! after the name of the worksheet. If you leave it out you will see the #NAME? error message. I have also seen students get a dialog box asking for a file name when they try to enter this formula.  That means the name of the worksheet tab in your formulas does not exactly match the actual worksheet name. Try renaming the worksheet tab and then re-write the formula using the new name.

14.   In column G enter the ending inventory values as 49, 26, 52, 60, and 33.

Complete the Summary Worksheet

15.   Click on the SUMMARY tab. It should show the Inventory Form that was created in step 48. Change the subtitle in row 2 to Summary.

16.   The Starting Inventory for the Summary is equal to Last Week’s Starting Inventory. In Cells E4:E8 enter formulas referring to cells E4:E8 in the LASTW worksheet.

17.   The Ending Inventory for the Summary is equal to This Week’s Ending Inventory. In Cells G4:G8 enter formulas referring to cells G4:G8 in the THISW worksheet.

18.   Delete the IF Functions in cells F4:F8.

19.   Enter the formula =LASTW!F4+THISW!F4 into cell F4 . This shows the sum of the Beer 1 purchases in the two-week period.

20.   You can point and click to build this type of cross-worksheet formula. Start by typing an = sign in cell F5 in the SUMMARY worksheet. Click on cell F5 in the LASTW worksheet. Type +. Click on cell F5 in the THISW worksheet. Press Enter.

21.   Copy the formula from F5 into cells F6:F8. Press Escape.

 

 

A

B

E

F

G

H

1

Beverage Sales

2

Summary

3

Item

Units

Starting

Purchases

Ending

Sold

4

Beer 1

Can

=LASTW!E4

=LASTW!F4+THISW!F4

=THISW!G4

=E4+F4-G4

5

Beer 2

Bottle

=LASTW!E5

=LASTW!F5+THISW!F5

=THISW!G5

=E5+F5-G5

6

Soda

Can

=LASTW!E6

=LASTW!F6+THISW!F6

=THISW!G6

=E6+F6-G6

7

Juice

Bottle

=LASTW!E7

=LASTW!F7+THISW!F7

=THISW!G7

=E7+F7-G7

8

Wine

Bottle

=LASTW!E8

=LASTW!F8+THISW!F8

=THISW!G8

=E8+F8-G8

Now we are going to expand the SUMMARY worksheet to show gross profit. It will look like this:

 

A

B

H

I

J

K

L

1

Beverage Sales

2

Summary

3

Item

Units

Sold

Cost

Price

Margin

Gross Profit

4

Beer 1

Can

=E4+F4-G4

.75

3.75

=J4 – i4

=H4 * K4

5

Beer 2

Bottle

=E5+F5-G5

.85

4.25

=J5 – i5

=H5* K5

6

Soda

Can

=E6+F6-G6

.25

2.00

=J6 – i6

=H6 * K6

7

Juice

Bottle

=E7+F7-G7

.20

2.00

=J7 – i7

=H7 * K7

8

Wine

Bottle

=E8+F8-G8

1.50

4.50

=J8 – i8

=H8 * K8

9

 

 

 

 

 

 

 

10

Item

Profit

 

 

 

 

 

 

22.   Hide columns C, D, E, F, and G. Ctrl+0 is the keyboard shortcut.

23.   Type the word Cost into cell i3.

24.   Type the word Price into cell J3.

25.   Type the word Margin into cell K3.

26.   Type the words Gross Profit into cell L3.

27.   In cells i4:J8 enter the amount you paid to purchase each product and the price you are selling it for.

28.   In cell K4 enter the formula = J4-i4. Copy the formula into cells K5:K8.

29.   In cell L4 enter the formula = H4 * K4. Copy the formula into cells L5:L8.

30.   Format the range i4:L8 as Currency Style.

31.   Format the range H3:L8 to center the cell contents.

32.   In cell A10 type the word: Item. In cell B10 type the words: Gross Profit.

33.   Sort the area from A4 to L8, putting the first column in alphabetical order by type of beverage.  You can use the A/Z icon [ALT  H  S]  for this task.

34.   In cell A11 create a data validation list [ALT  A  V  V]  for Beer 1, Beer 2. Juice, Soda, Wine.  Hint; You learned how to do this in the Lookup lesson.

35.   In cell B11 write a VLOOKUP Function to show the profit for each item: =VLOOKUP(A11,A4:L8,12). We use 12 because we have to count the hidden columns. Our gross profit values are in the twelfth column of the table.  If the formula displays N/A that is ok.  You just need to make a beverage selection in cell A11

36.   Click on cell A1. Use the merge and center icon [ALT  H  M]  to unmerge the cells. 

Select cells A1:L1 and merge them [ALT  H  M].

37.   Click on cell A2. Use the merge and center icon [ALT  H  M]  to unmerge the cells.

Select cells A2:L2 and merge them [ALT  H  M].

Complete the Purchases Worksheet

38.   Click on the Purchases tab. It should show the Inventory Form that was created in steps 4 to 8. Type “Purchases” in cell A2.

We want to use this form to order supplies for the following week. Our reorder numbers will be equal to our average weekly sales of each item for the previous two weeks. That is the number of sales we expect to make in the next week. Our order amount will be equal to the amount of sales we had during the previous two weeks. This gives us a safety margin so we should always have at least a two-week supply of products on hand to sell.

39.   Unhide columns C and D. (Click and drag over the letters above columns B and E. Right click and choose Unhide.  Another method is to select the entire worksheet. On the home tab choose the Format icon. Click on Visibility and choose Unhide Columns. [ALT  H  O  U  L] )

40.   In cell C4 enter the formula =ROUNDUP(Summary!H4/2,0). Copy this formula into cells C5:C8. This will give us the average amount of Beer 1 sales per week. We use the roundup function because we don’t want to order half of a bottle or can.

41.   In cell D4 enter the formula =Summary!H4. Copy this formula into cells D5:D8. We want to order two weeks supply of expected sales when we run low.

42.   The Starting Inventory values for next week = the Ending Inventory values from this week. In Cells E4:E8 enter formulas referring to cells G4:G8 in the THISW worksheet. For example the formula in cell E4 would be =THISW!G4. 

43.   You should now be seeing the amount to order in the purchases column.

44.   Copy the range A4:F8.

45.   Use Paste Special - Values to paste the contents of this range into cells A11:F15. This will create a permanent record in rows 11 to 15 of the values generated by the formulas in rows 4 to 8.

Study Guide for the 91L Final Exam

http://bus91L.altervista.org/Ideas/Guide.htm

Skills brush up - Review of the 91L Excel lessons.

http://bus91l.altervista.org/Ideas/96X_Prereqs.htm

Instructions for the Check for Understanding (CFU) form.

A CFU form is turned in for every assignment.

Starting with the line for your name, copy and paste the CFU form into a Word document.

Fill in your name, date, section number

Circle (or indicate by highlighting) a Y or N response for each statement. I do not subtract points for No answers.

Below the list of Y/N statements, insert ONE screenshot of any part of your work. I just need to see something to prove that you did the assignment. I don’t need to see all of the assignment.  Resize the screenshot, if necessary, so both the form and graphic can fit on one page.

A copy of the CFU is due to be turned in during the next class session.  I do not accept CFU’s through email. 

 

Name _______________________________      Date___________       Section _______

Check for Understanding – Excel 2010 Inventory Project

Y/N Simultaneously create the same form in multiple worksheets.

Y/N Hide and unhide columns.

Y/N Use cell references to other worksheets in a formula.

Y/N Replace formulas with their values using Paste Special.