Chapter Eight: Multiple Worksheets

Background

Formulas Using Cells in Other Worksheets

To write formulas that refer to cells in other worksheets, add the worksheet name, followed by en exclamation mark, before the cell address. For example, =Sheet2!C4 will show the contents of cell C4 in Sheet 2. If the content of the cell is a formula, the results of the calculation are shown.

If the name of the worksheet is changed later, Excel automatically corrects the formulas.

Macros

A macro is a stored set of keystrokes that can be replayed to perform a repetitive task. When you create a macro, Excel records every mouse-click and keystroke you take as you perform the task. The steps are converted into a series of Visual Basic commands.

After the macro is created you can run it if you want to repeat, or "play back," the steps.

To create a macro, go to the View tab and click on the pull-down arrow below the Macro icon. Select record macro. Fill in the name and description of your macro, and then start executing your planned series of keystrokes. If you make a mistake when you record the macro, corrections you make are also recorded. When you have completed the macro, go back to the macro icon pull-down box and select stop recording, or click on the stop macro icon in the status bar.

After you record a macro, you can review the Visual Basic code and make changes.

Beverage Inventory Exercise

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.

To find the number of items sold in a period, you start with the number you have on hand, and then add in any purchases you made. This tells you how many items you had available to sell. At the end of the period you count how many items you have left. By subtracting you determine how many pieces are gone. The assumption is all the missing items were sold. Items sold = [Starting Inventory + Purchases – Ending Inventory.]

When inventory falls below a certain level, sometimes called a reorder level, you will order more. The reorder level equals the number of items you would expect to sell while waiting for new stock to be delivered.

You can use an IF function to decide when to order more inventory. IF current inventory < reorder level, order more, if not, do nothing. For the assignment, we assume the standard order size is a case of 12 dozen, and the reorder level is 100.

The beginning inventory of any period will always equal the ending inventory of the previous period. Nobody counts their inventory twice.

Build the Form

1. Open Excel

2. Rename the worksheet tabs as Q1, Q2, 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. Create the form as shown below. It will automatically be copied into all of the selected worksheets.

	A	B	C		D	E	F
1	Beverage Sales
2
3	Item	Units	Starting Inv	Purchases		Ending Inv	Items Sold
4	Beer 1	Can		=IF(C4<100,144,0)			=C4+D4-E4
5	Beer 2	Bottle		=IF(C5<100,144,0)			=C5+D5-E5
6	Soda	Can		=IF(C6<100,144,0)			=C6+D6-E6
7	Juice	Bottle		=IF(C7<100,144,0)			=C7+D7-E7
8	Wine	Bottle		=IF(C8<100,144,0)			=C8+D8-E8

Complete the First Quarter (Q1) Worksheet

1. Deselect all tabs. (Click on a tab that is not in bold.) Click on the Q1 tab.

2. Type “Quarter 1” in cell A2.

3. In column C, enter the starting inventory values as 260, 160, 82, 100, and 60.

4. In column E, enter the ending Inventory values as 52, 104, 64, 96, and 132.

5. The If Functions in column D will show that you ordered more Soda and more Wine.

	A	B	C	D	E	F
1	Beverage Sales
2	Quarter 1
3	Item	Units	Starting Inv	Purchases	Ending Inv	Items Sold
4	Beer 1	Can	260	=IF(C4<100,144,0)	52	=C4+D4-E4
5	Beer 2	Bottle	160	=IF(C5<100,144,0)	104	=C5+D5-E5
6	Soda	Can	80	=IF(C6<100,144,0)	64	=C6+D6-E6
7	Juice	Bottle	100	=IF(C7<100,144,0)	96	=C7+D7-E7
8	Wine	Bottle	60	=IF(C8<100,144,0)	132	=C8+D8-E8

Complete the Second Quarter (Q2) Worksheet

1. Click on the Q2 tab.

2. Type “Quarter 2” in cell A2.

3. The Starting Inv for each quarter = the Ending Inv from the previous quarter. The formula for the Starting Inv for Q2 Beer 1 is the Ending Inv for Q1 Beer 1: =Q1!E4

4. In column C, enter formulas for the Starting Inv. for each product.

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

Complete the Summary Worksheet

1. Click on the Summary tab.

2. Change the subtitle in row 2 to Mid-Year Summary.

3. The Starting Inv. on the Mid-Year Summary = the 1st quarter’s Starting Inv.

4. The Ending Inv. on the Mid-Year Summary = the 2nd quarter’s Ending Inv.

5. Delete the formulas in the Purchases column.

6. Enter new formulas in the purchases column, equal to the sum of Purchases from the two quarters. For example, Beer 1 Purchases =Q1!D4+Q2!D4.

7. You can point and click to build this formula. Start with an = sign in cell D4 in the Summary worksheet. Click on cell D4 in the Q1 worksheet. Type +. Click on cell D4 in the Q2 worksheet. Press Enter.

8. Copy the formula from D4 into rows 5-8.

	A	B	C	D	E	F
1	Beverage Sales
2	Mid-Year Summary
3	Item	Units	Starting Inv	Purchases	Ending Inv	Items Sold
4	Beer 1	Can	=Q1!C4	=Q1!D4+Q2!D4	=Q2!E4	=C4+D4-E4
5	Beer 2	Bottle	=Q1!C5	=Q1!D5+Q2!D5	=Q2!E5	=C5+D5-E5
6	Soda	Can	=Q1!C6	=Q1!D6+Q2!D6	=Q2!E6	=C6+D6-E6
7	Juice	Bottle	=Q1!C7	=Q1!D7+Q2!D7	=Q2!E7	=C7+D7-E7
8	Wine	Bottle	=Q1!C8	=Q1!D8+Q2!D8	=Q2!E8	=C8+D8-E8

Optional: Add More Features to the Mid - Year Summary Worksheet

1. Add a column (G) showing Unit Cost. Make up a number price for each item and enter it in the column.

2. Add a column (H) showing Total Cost. [= Items Sold * Unit Cost]

3. Add a column (I) showing Cost Ratios. Use these numbers for your cost ratios: Beer 18%, Soda 20%, Juice 15%, Wine 37%.

4. Add a column (J) showing Sales Price. Calculate the sales price for each item, and enter it in the column. [= Unit Cost / Cost Ratio]. Use a =ROUND function and round to zero decimal places.

5. Add a column (K) showing Revenue. [= Items Sold * Sales Price]

6. Add a column (L) showing Profit. [= Revenue – Total Cost]

7. Format columns G-L as Currency Style.

8. In cell A10 type the word: Item. In cell B10 type the word: Profit.

9. Sort the area from B4 to L8, putting it in alphabetical order by type of beverage.

10. In cell A11 create a data validation list for Beer 1, Beer 2. Juice, Soda, Wine. Hint: Look in the Data tab, Data Tools group for the Data Validation icon.

11. In cell B11 write a VLOOKUP Function to show the profit for each item.

Create a Macro to Move the Cursor into Cell A1in the Summary Worksheet

1. Insert a new worksheet.

2. Change its name to Contents.

3. Drag it so it is the first worksheet, position it to the left of Q1.

4. Click the pull-down arrow below the macro icon on the View tab. Choose "Record Macro."

5. Name the Macro “Summary.”

6. Describe the macro: “Moves the cursor to the Summary worksheet.” Click OK.

7. The stop recording macro icon will appear in the lower left corner, just below the worksheet.

8. Click on the Summary worksheet tab.

9. Click anywhere in the worksheet. Press Enter.

10. Press the Ctrl and Home keys to move the cursor to cell A1.

11. Stop the recording by either clicking on the icon, or by clicking the pull-down arrow below the macro icon on the View tab and choosing "Stop Recording."

Create a Button on the Contents Worksheet to Run the Macro

1. Position the cursor in cell C7 of The Contents worksheet.

2. Draw a textbox covering cells C7, C8, D7, and D8.

3. Add the word Summary to the textbox.

4. Right-click on the textbox and choose "Assign Macro."

5. The Assign Macro dialog box should open.

6. Select the Summary macro. Click OK.

7. Click on another cell in the worksheet.

8. Test the Summary button by clicking on it.

Optional

Create macros to move the cursor into the Q1 and Q2 worksheets.

Add two more text boxes to the Contents worksheet.

Assign the macros to the text boxes.