Business Productivity Tools

Multiple Worksheet Practice

Office 2007 instructions are in Red. Instructions specific to older versions are in Blue.

Create a form to track souvenir sales, edit it for each quarter, and create a year-end summary. When finished, print the Summary worksheet in mirror view. Submit the printout to the instructor.

Items sold = [Starting Inventory + Purchases – Ending Inventory.] Use an IF function to decide when to order more inventory. [IF (Starting Inv <100, order 144 more, if not, order zero more)]

Office 2007 – Look in the Formulas Tab, Functions Group for the functions dialog box.The large icon shows all of the functions. Other icons show the functions by categories.

Build the Generic Form

Office 2007 – Formatting Options are found on the Home Tab, in the Font, Alignment, and Number Groups

1. Rename the worksheets as Q1, Q2, and Summary.
2. Select all tabs. (Right click on any tab. Select All. The tabs all turn white.)
3. Create the form as shown below. It will automatically be copied into all four worksheets.

	A	B	C	D	E	F
1	Souvenir Sales
2
3	Item	Units	Starting Inv	Purchases	Ending Inv	Items Sold
4	Mugs	Each		=IF(C4<100,144,0)		=C4+D4-E4
5	Shirts	Each		=IF(C5<100,144,0)		=C5+D5-E5
6	Posters	Each		=IF(C6<100,144,0)		=C6+D6-E6
7	CD’s	Each		=IF(C7<100,144,0)		=C7+D7-E7
8	Caps	Each		=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 showing in bold type.) Click on the Q1 tab.
2. Type “Quarter 1” in cell A2.
3. In column C, enter the Starting Inv values, as given below.
4. In column E, enter the Ending Inv numbers, as given below.
5. The If Functions in column D should tell you to order Posters and Caps, but nothing else.

	A	B	C	D	E	F
1	Souvenir Sales
2	Quarter 1
3	Item	Units	Starting Inv	Purchases	Ending Inv	Items Sold
4	Mugs	Each	260	=IF(C4<100,144,0)	52	=C4+D4-E4
5	Shirts	Each	160	=IF(C5<100,144,0)	104	=C5+D5-E5
6	Posters	Each	80	=IF(C6<100,144,0)	64	=C6+D6-E6
7	CD’s	Each	100	=IF(C7<100,144,0)	96	=C7+D7-E7
8	Caps	Each	60	=IF(C8<100,144,0)	132	=C8+D8-E8

To write formulas that refer to cells in other worksheets, include the name of the other worksheet follow by an exclamation mark in your cell reference. For example: =Q2!E4 refers to cell E4 in the Q2 worksheet.

Complete the Second Quarter (Q2) worksheet

1. Type “Quarter 2” in cell A2.
2. The Starting Inv for each quarter = the Ending Inv from the previous quarter. The formula for the Starting Inv for Q2 Mugs is the Ending Inv for Q1 Mugs: =Q1!E4
3. In column C, enter formulas for the Starting Inv. for each product.
4. In column E, enter a formula = to 1/4 * the (Starting Inv + Purchases) for each item.

Complete the Summary worksheet.

1. Change the subtitle in row 2 to Mid-Year Summary - followed by your name
2. The Starting Inv on the Mid-Year Summary = the first quarter’s Starting Inv.
3. The Ending Inv on the Mid-Year Summary = the second quarter’s Ending Inv.
4. Delete the formulas in the Purchases column.
5. Enter new formulas in the purchases column, equal to the sum of Purchases from the two quarters. For example, Mug Purchases =Q1!D4+Q2!D4.
6. 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.
7. Copy the formula from D4 into rows 5-8.

	A	B	C	D	E	F
1	Souvenir Sales
2	Mid-Year Summary – My Name
3	Item	Units	Starting Inv	Purchases	Ending Inv	Items Sold
4	Mugs	Each	=Q1!C4		=Q2!E4	=C4+D4-E4
5	Shirts	Each	=Q1!C5		=Q2!E5	=C5+D5-E5
6	Posters	Each	=Q1!C6		=Q2!E6	=C6+D6-E6
7	CD’s	Each	=Q1!C7		=Q2!E7	=C7+D7-E7
8	Caps	Each	=Q1!C8		=Q2!E8	=C8+D8-E8

Your manager asks for more information on the Mid - Year Summary worksheet.

1. Add a column (G) showing Price.
2. Make up a sales price for each item and enter it in the column
3. Add a column (H) showing Unit Cost.
4. Make up a cost for each item and enter it in the column.
5. Add a column (I) showing Revenue [= Items Sold * Price]
6. Add a column (J) showing Total Cost. [= Items Sold * Unit Cost]
7. Add a column (K) showing Profit. [= Revenue – Total Cost]
8. Format columns G-K as centered, Currency Style, with no decimal places.

Optional

9. In cell A10 type the word: Item. In cell B10 type the word: Profit.
10. Sort the area from B4 to K8, putting it in alphabetical order by type of souvenir.
11. In cell A11 create a data validation list for: Mugs, Shirts, Posters, CD’s and Caps. From the Data menu, Validation submenu, change Allow Any to Allow List. Mouse over the list. Enter and OK. Office 2007 – Look in the Data Tab, Data Tools Group. Data Validation is the icon in the upper right.
12. In cell B11 write a formula to show the profit for each item. =Vlookup(A11,A4:K8,11)