98 PART 5 USING SPREADSHEET SOFTWARE Learning Objectives After completing Part 5, you will 9 Comprehend how spreadsheets are used in the business office, and their importance. 9 Be familiar with the process of creating spreadsheets. 9 Enter data into a spreadsheet. 9 Create a Business Statement report from spreadsheet data. NOTE: Students must have access to Microsoft Excel or other spreadsheet software. Additionally, to complete the spreadsheet exercises, students must be proficient in creating a basic spreadsheet or they may download the student spreadsheet template for each practice job from www.mhhe.com/slater8e. For many years people used calculators and adding machines to perform numerical calculations, however, with recent innovations in computer technology and spreadsheet software, these manual tools have become virtually obsolete. The ease with which spreadsheet software can calculate and manipulate volumes of numerical data makes managerial decision making more timely and effective. For example, when using a balance sheet set up on a spreadsheet, if one number is changed, all affected totals are automatically changed throughout the spreadsheet. A spreadsheet contains horizontal rows and vertical columns. Columns are labeled with letters; rows are numbered. The intersection of a row and a column is called a cell. Cell locations are identified as “cell addresses”; a column letter and a row number identify the corresponding cell address. Cells may contain labels (alphanumeric characters), numerical values, or formulas. For example Cell A1 is labeled Temporary Employees, B1 is labeled Permanent Employees, C1 is labeled Total Employees. Cell A2 and B2 have numerical; values and cell C2 has a formula that adds Cell A2 and B2. The exercises that follow allow you to practice using the computer number pad to enter data with speed and accuracy. They also provide an opportunity to practice creating spreadsheets. P e r c e n t o f M o n t h l y E x p e n s e 4 4 % 3 % 1 2 % 4 % 1 8 % 1 9 % H o u s e M o r t g a g e G a s C a r n o t e C a r I n s u r a n c e C r e d i t C a r d F o o d a n d L i v i n g 99 Spreadsheet Exercise 1 Create a Budget Spreadsheet and Input Data. Instructions 1. Start the Window Excel Program. 2. For this exercise you will have to create a Budget Expense Spreadsheet. The data and labels for the spreadsheet are shown below. 3. The formulas required to have the spreadsheet automatically tabulate the data are: % Of Total Expense = (Amount/ Total Monthly Expense) * 100 Total Monthly Expense = Use the ∑ key to tabulate Total Monthly Expense. 4. The Total Monthly Expense is $___________. 5. Print a copy of the spreadsheet and save as the file BUDGET, to a diskette. Expense Amount % of Total Expense House Mortgage $ 1,200.00 Gas $ 70.00 Car note $ 325.00 Car Insurance $ 100.00 Credit Card $ 500.00 Food and Living $ 500.00 Total Monthly Expenses TIP: After typing the first value in the amount column, do not press enter. Use your thumb to press the down arrow to move to the next cell. In the Amount column, *The template is listed as Exercise1 on the Website. 100 Special Feature: Once data has been entered into a spreadsheet, a graph of the data may be displayed. Bar, line, and pie graphs are examples of the graphs that may be generated from the inputted data. The following is an example of a Pie Chart that could be generated from the spreadsheet data. Percent of Monthly Expense 44% 3% 12% 4% 18% 19% House Mortgage Gas Car note Car Insurance Credit Card Food and Living 101 Spreadsheet Exercise 2 Input Data to find Total Inventory Cost. Instructions: 1. Start Windows Excel program. 2. For this exercise, you will have to create an Inventory Spreadsheet. 3 The data and labels are shown below. Use the following formulas to tabulate the data: Total Item Cost = (Unit Cost * Quantity) Total Inventory Cost = ∑ of the Item Costs 4 Print a copy of the spreadsheet and save the file as inventory to a diskette. 5 Your objective is to ensure accurate and complete data entry. *The template is listed as Exercice2 on the Website. Sporting Goods Store First Quarter Inventory January 2007 Description Product # Vendor # Unit Cost Qty. Total Cost Baseball Jersey (Small) 99504 22154 $10.25 30 Baseball Jersey (Medium) 99514 22154 $12.25 45 Baseball Jersey (Large) 99499 22154 $13.25 22 Baseball Pants (Small) 99498 22154 $11.95 28 Baseball Pants (Medium) 99527 22154 $13.99 40 Baseball Pants (Large) 99521 22154 $15.50 32 Baseball Glove (Children) 97789 22154 $28.90 15 Baseball Glove (Adult) 86791 22154 $38.95 22 Baseball Bat (Std.) 86789 22154 $35.75 15 Baseball Bat (Steel) 86796 22154 $48.25 29 Baseball 86793 22154 $22.99 48 Softball 86795 22154 $18.15 39 Football 41232 25190 $29.80 50 Football Pads 41235 25190 $54.45 43 Football Jersey 41238 25190 $15.50 55 Football Helmets 41239 25190 $52.90 43 Knee pads 41234 25190 $25.50 120 Basketballs 41233 10541 $32.00 53 Backboard and Hoop 70015 10541 $109.50 19 Hockey Stick 70020 10009 $38.90 21 Soccer Ball 70030 11154 $28.25 32 102 Sporting Goods Store First Quarter Inventory January 2007 Description Product # Vendor # Unit Cost Qty. Total Cost Tennis Rackets 55723 12383 $48.99 19 Tennis Balls 55725 12383 $12.00 49 Calloway Golf Clubs 55177 27474 $899.00 12 Cleveland Golf Clubs 55092 27474 $689.00 8 Taylor Made Golf Clubs 68545 27474 $799.00 11 Topflight Golf Balls 68157 27474 $25.00 70 Hogan Golf Balls 60001 27474 $22.00 50 Putter 60017 27474 $89.90 12 Sand wedge 55 degree loft 60025 27474 $61.75 5 Sand wedge 60 degree loft 60035 27474 $70.25 13 Golf Bag 60036 27474 $119.00 16 Fishing Rod 60098 11567 $43.50 25 Tackle Box 36019 11567 $19.99 19 Bass Lure 36022 11567 $8.99 31 Bowling Ball 36054 22221 $82.75 21 Bowling Bag 36099 22221 $28.50 22 TOTAL INVENTORY COST 6. The Total Inventory Cost is $__________________. 103 Spreadsheet Exercise 3 Input Travel Expense Data to a Travel Expense Report Instructions 1. Start the Windows Excel program. This exercise will require you to create a Travel Expense Report Spreadsheet. 2. The data for the Travel Expense Report is shown below for the week of April 26th. The following formula should be used to automatically calculate the Expense Reimbursement. Total = ∑ of each expense in that row. Total Meals = ∑ of the Breakfast, Lunch and Dinner column; then ∑ the total meal expense for that row into the total column. Reimbursement = ∑ of the Total Column Travel Expense Report Name: Harry Hemphill Monday Tuesday Wednesday Thursday Friday Saturday Sunday Category 26-Apr-07 27-Apr-07 28-Apr-07 29-Apr-07 30-Apr-07 1-May-07 2-May-07 Total Lodging $ 80.85 $ 80.85 $ 80.85 $ 80.85 $ 80.85 $ Meals Breakfast $ 13.00 $ 12.00 $ 11.25 $ 12.75 $ 11.50 Lunch $ 8.99 $ 9.25 $ 10.00 $ 11.00 $ 11.75 Dinner $ 32.00 $ 24.00 $ 22.00 $ 26.00 $ 29.00 Total Meals $ 53.99 $ 45.25 $ 43.25 $ 49.75 $ 52.25 $ $ Auto Rental $ 51.00 $ 51.00 $ 51.00 $ 51.00 $ 51.00 $ Telephone $ 8.56 $ 9.25 $ Parking $ Entertainment $ Misc. $ TOTAL EXPENSE REIMBURSEMENT $ 3. Print a copy of the Travel Expense Report and save the file as Travel to a diskette. 4. The Total Expense Reimbursement $_____________. *The template is listed as Exercice3 on the Website. 104 Spreadsheet Exercise 4 Create a Business Statement Report Instructions: 1. Start Windows Excel Program. 2. For this exercise you will have to create the following Business Statement Report. 3. The data and labels required to create the spreadsheet are shown below. 4. Print a copy of the Business Statement and save the file as Statement to a diskette. 2007 2008 2009 Annual Income $800,000.00 $900,000.00 $1,000,000.00 Expense $350,000.00 $400,000.00 $ 475,000.00 Net Profit $450,000.00 $500,000.00 $ 525,000.00 *The template is listed as Exercise4 on the website. Special Feature: Data from a spreadsheet can quickly be formatted into a Bar Chart as shown below. 2004 2005 2006 $- $200,000.00 $400,000.00 $600,000.00 $800,000.00 $1,000,000.00 Annual Income Expense Net Profit 105 Spreadsheet Exercise 5 Input Payroll Data to a Payroll Register Report Instructions 1. Start the Windows Excel program. This exercise will require you to create a Payroll Register Report Spreadsheet. 2. The data to create the payroll spreadsheet may be entered from Part 3, page 52. This job was completed and all information has been obtained. Or you may choose to set up your spreadsheet with formulas to assist you in calculating the final payroll expense. You will experience the ability to complete the payroll at three times the speed of the electronic calculator hand calculations. The following formulas will assist you in completing the payroll exercise. Hours Earnings Soc Sec No. Rates Regular Overtime Total Regular Overtime Total given given given given ? ? ? ? Total Hours = (Regular hours + Overtime hours) Regular Earnings = (Rates * Regular Hours) Overtime Earnings = [(Rates * 1.5) *(Overtime Hours)] Total Earnings = (Regular Earnings +Overtime Earnings) Total Payroll Expense = ∑ of Total Earnings Column. 3. The Total Pay Expense =$______________________ 4. Print a copy of the Payroll Register Report and save the file as Payroll to a diskette. *The template is listed as Exercice5 on the Website. 106 PAYROLL REGISTER TEMPORARY EMPLOYESS Pay Period Ending: 4-30-2007 Hours Earnings Soc Sec No. Rates Regular Overtime Total Regular Overtime Total 274-74-3700 $ 10.00 40 282-74-4968 $ 10.00 40 5 273-69-4256 $ 7.00 35 600-45-3117 $ 7.00 37.75 305-20-8129 $ 7.00 38 300-84-7254 $ 8.50 20 3 427-39-3663 $ 8.50 30 600-47-5106 $ 8.50 40 6 725-69-6491 $ 8.50 40 8 200-20-2020 $ 8.50 35 406-64-3591 $ 7.75 39 927-76-8461 $ 6.95 38.25 654-65-6229 $ 8.00 38.25 305-54-5047 $ 9.00 40 4.3 280-13-4836 $ 9.00 40 6.5 610-22-2629 $ 9.25 40 8.95 620-31-3451 $ 9.25 40 625-40-6252 $ 9.25 40 600-89-9100 $ 9.50 40 12 605-71-1181 $ 9.50 40 15 269-68-4716 $ 8.10 40 5 452-64-5201 $ 7.00 30 300-58-9269 $ 7.50 40 10 471-05-5800 $ 8.50 40 2.25 906-25-7101 $ 9.00 40 5 320-52-3769 $ 8.00 38.75 501-50-4020 $ 7.00 38.75 696-32-4644 $ 8.10 40 10 211-64-3939 $ 7.50 39 322-22-2332 $ 9.50 35 451-49-6672 $ 10.00 35 100-66-9952 $ 9.00 40 6 321-65-9874 $ 9.25 40 4.25 789-23-7413 $ 7.00 40 4.75 100-64-4126 $ 7.50 40 5.25 105-21-7843 $ 7.75 40 665-27-7000 $ 7.75 38.75 332-45-6679 $ 8.25 39.25 608-29-4791 $ 8.50 40 3 202-26-4165 $ 9.50 40 6 Total Payroll Expense 107 CLASSROOM NOTES 108 CLASSROOM NOTES 109 CLASSROOM NOTES 110 CLASSROOM NOTES