Spreadsheet Templates to Accompany Reimers, Financial Accounting 1E Templates by Albert Fisher EXCEL TUTORIAL ___
The accompanying templates for Financial Accounting 1E were written with the user in mind. These templates are partially completed to allow students to concentrate on solving the problem and not worry about the formatting or the presentation of the answer. All cells that need to be completed by the student are shaded blue. Certain problems and exercises have no shaded cells, therefore, the student needs to complete the entire template. Students will need only minimal knowledge of spreadsheet techniques. I usually take one class period at the beginning of the semester to run through this tutorial with my class. I then go over the formulas from the first problem I assign from the chapter material to make sure students understand what is expected from them. BASIC SKILLS: Starting Excel is similar to starting other Windows applications. The following steps are required: Launching Excel Double click the Microsoft Excel icon from your desktop icons. If Excel does not have a desktop icon, then do the following: a. b. c. d. Click Start to display the start menu. Click Program to display the programs available on your computer. Scroll down until you find the Excel program and double click on it. If you are new to Microsoft Excel, the online Quick Preview lesson AGetting Started@ should give you the basic knowledge to complete these templates.
Opening a Worksheet File a. Make sure your student disk is in the disk drive. (This should be known as the “a@ drive.) b. There are two ways to open a worksheet file. 1. The first way is to click the Open File Button. Buttons are shortcuts for many worksheet tasks. We will be using some of these buttons to facilitate the student’s work. The three we will be using are Open, Print, and Save.
Opening a Worksheet File (continued) 2. The second way is to click File from the main menu bar and then Open to display the Open File dialog box. A dialog box is used to select options and identify data you want a command to work on.
c. Click the Drives drop-down list arrow to see the list of available drives. The Open File dialog box will appear. d. Select the drive where your student disk is located by clicking that drive. (We will assume this is drive a:) e. Finally, click the file name you want to retrieve, then click OK. All file names refer to the corresponding problems in the book. For example, P2-1A refers to Problem 21A from Chapter 2. Completing a Template A template is a pre-labeled worksheet that needs to be completed by filling certain cells with data. Spreadsheets (Excel) are computer programs, which store data in cells. A cell is where a row and a column intersect. Rows are labeled numerically, and columns are labeled alphabetically. There are only four things that can go into a cell. 1. A LABEL (TITLE) is descriptive text. 2. A FORMULA is the arithmetic used to calculate numbers displayed in the template. 3. A VALUE (AMOUNT) is a number, formula, or function. 4. A FUNCTION is a predefined formula. To complete a template, a student will be asked to enter one of the above items into a cell. Let’s look at different examples.
Completing a Template (continued) LABEL EXAMPLE: Assume the following template appeared on the screen: A 1 2 3 4 5 6 B Sales TITLE Gross Profit C D $100,000 40,000 ----------FORMULA E F G
The student would move the cursor to B3 and type COST OF GOODS SOLD. FORMULA EXAMPLES: The student would move the cell pointer (indicates current cell location) to cell D5 and enter +D2-D3. Although no numbers were entered, we are instructing Excel to subtract whatever amount is in cell D3 from whatever amount is in cell D2. The answer $60,000 appears on the screen.
A 1 2 3 4 5 6
B Sales Cost of goods sold Gross Profit
C
D $100,000 40,000 ----------$ 60,000
E
F
G
To add numbers, the student would move the cell pointer to Cell D5 and enter +D2+D3. Although no numbers were entered, we are instructing Excel to add whatever amount is in cell D2 with whatever amount is in cell D3. The answer $1,500 appears on the screen. A 1 2 3 4 5 6 B C D E F ADD DIVIDE MULTIPLY $1,000 25 10 500 5 6 -----------FORMULA FORMULA FORMULA G
Completing a Template (continued) ANSWERS A 1 2 3 4 5 6 B C D ADD $1,000 500 -------$1,500 E DIVIDE 250 50 ---5 F MULTIPLY 10 6 --60 G
To divide numbers, the student would move the cell pointer to cell E5 and enter +E2/E3. The quotient, 5, would appear in cell E5. To multiply numbers, the student would enter +F2*F3 in cell F5 or any cell where he would want the answer to appear. The product 60 would appear. VALUE EXAMPLE: Assume the student is instructed to change the sales amount to $150,000. He would move the cell pointer to cell D2 and type 150000. Do not type numbers with commas. All cells have been formatted for the student in the templates. The new template would appear as below: A 1 2 3 4 5 6 B Sales Cost of goods sold Gross Profit C D $150,000 40,000 ----------$110,000 E F G
Notice how the Gross Profit is automatically recalculated. spreadsheets. FUNCTION EXAMPLE: Again, assume the following template appears on the screen: A 1 2 3 4 5 6 B C D ADD $1,000 500 -------FORMULA E
This is the power of
F
G
Completing a Template (continued) In lieu of entering +D2+D3 in Cell D5, we could use a built-in function called SUM. The student would move the cell pointer to D5 and enter the following: @SUM(D2.D3). Cell D5 would then show $1,500. I have provided instructions for the students when functions should be used in the templates. Printing a Template The template will print when you click the Print Button. CAUTION: The biggest problem students will have when printing is forgetting to turn on the printer. They think if the computer is on, the printer must be also. This is not necessarily the case. The two may have to be turned on separately. Saving a Template Click the Save File Button to save your completed template. Caution: The information on the screen will overwrite the existing file. Refer to the Help menu if you encounter problems not covered by this tutorial. Some finer points using Excel: The following will appear in some cells: #value! This will be replaced with a number when you enter the required FORMULAS or AMOUNTS in the shaded cells. To view the Formulas, press CTRL+` (left single quotation mark to the left of the number 1 key). Helpful hints from CyberCoach: Certain cells will have a small red triangle in the upper right corner of the cell indicating an attached comment from CyberCoach to help you complete the template. It could be either a check figure, a formula for the cell, or hints on how to complete the template. To view the helpful comments, move the cell pointer to the cell with the red triangle in the upper right corner of the cell and the helpful hint will appear from CyberCoach.
Cells with a red background: Certain cells will appear in red. When the correct answer is entered, the red background will change to white indicating a correct answer. Worksheet protection: All the workbook files are read-only. Therefore, changes to the workbook cannot be saved in the same workbook file. You can, however, use the Save As command to save another version of the file with a different name or in another location. Refer to the “Saving a Template” paragraph of this tutorial. Please feel free to e-mail me if you have any questions or suggestions regarding the templates. Albert Fisher Las Vegas, Nevada Albert_Fisher@CCSN.NEVADA.edu