Hands-On Exercises

Download as a Word document: Hands-On Exercises Ch06 (214.0K)

Sunny Side Marina-Five Year Projected Financial Statement

In this exercise you will practice entering formulas and functions.

1. Open the data file named EX06HE01. You screen should look similar to Figure 6-1.

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470941/26807/Ch06_Image1ho.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (50.0K)</a>

Figure 6-1

2. Save the file as "Five Year Plan" to your personal storage location.
3. There are a number of formulas to enter for starters. To begin:
   CLICK: Cell B7
   TYPE: =B5-B6
   PRESS: Enter
4. Fill the formula across the remaining columns.
   SELECT: Cells B7 to F7
   CLICK: Edit, Fill, Right
5. Now you will total the Expenses for each year using the SUM Function:
   SELECT: Cells B14 to F14
   CLICK: AutoSum Button on the Standard tool bar.
6. To calculate the Income before taxes:
   SELECT: Cell B15
   TYPE: = (Equals)
   SELECT: Cell B7
   TYPE: - (minus)
   CLICK: B14
   PRESS: Enter
7. To fill the formula across, using the fill handle:
   SELECT: Cell B15
   CLICK: Fill handle, and hold the mouse key down
   DRAG: To highlight row 15 over to column F
   Notice that the last two columns numbers are in parenthesis. What is the reason for this?
8. Since the company doesn’t pay taxes when income is less than zero, you will use the IF function to determine when to calculate the tax.
   SELECT: Cell B16
   CLICK: the Insert function button
   SELECT: the IF function,
   CLICK: OK
9. The functions arguments dialog box opens (Figure 6-2) to allow you to specify the arguments for this calculation. To perform the calculation, you need to test if the value for Income Before Taxes is greater than zero, if it is the tax rate is a fixed 48%, if the value is less than or equal to zero, then the Income Tax is 0. To begin:
   TYPE: b15>0
   PRESS: Tab
   TYPE: b15*48%
   PRESS: Tab
   TYPE: 0
   CLICK: OK

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470941/26807/Ch06_Image2ho.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (50.0K)</a>

Figure 6-2

10. On your own, fill the formula you just created across to column F. Notice that the last two columns are displaying a "-." What is the reason for this?
11. The final calculation subtracts the Income taxes from Income Before Taxes. On your own enter this formula and fill it across the columns. Format the cells by decreasing the decimals to match the other values in the worksheet.
12. When you are finished, save your work, and print the worksheet. Finally, close the file and exit Excel.

Data File: EX06HE01 (14.0K)

 
Turtle River Casino-Condominium Sales

In addition to the casino facilities, Turtle River Casinos also sells time-share condominiums. In this exercise you will practice using and entering if functions.

1. Open the data file named "EX06HE02." Your screen should look similar to Figure 6-3.

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470941/26807/Ch06_Image3ho.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (50.0K)</a>

Figure 6-3

2. Save the workbook as "Condo Sales" to your personal storage location.
3. There are some formulas already entered in Column B, in rows 13 and 15 to18. review the formulas to familiarize yourself with the calculations. However, the ones you will be using are slightly different. To make the formulas easier to enter, you start by naming the ranges in the data section:
   SELECT: Cell D6
   CLICK: Name text box
   TYPE: Jan_Sales
   PRESS: Enter
4. On your own name the other cells, D7, D8, and D9; "Gro_Rate", "Sell_Exp," and "Gen_Exp" respectively.
5. Now, enter the formula to calculate February sales:
   SELECT: Cell C13
   TYPE: =b13*(1+Gro_rate)
   CLICK: Fill handle, and
   DRAG: to column F.
6. To enter the formula for selling expense:
   SELECT: cell c15
   TYPE: =IF((b15=0),0,(c13*Sell_Exp))
   Note: the parenthesis don’t affect the order of operations, they are only there to make it easier to read the formula.
   CLICK and DRAG: to fill the formula across
7. To calculate general expenses:
   SELECT: Cell C16
   TYPE: =if(b13=0,0,
   CLICK: Cell D9
   TYPE: )
   PRESS: Enter
   Notice that Excel entered the range name, and not the cell column and row reference.
8. To finish the worksheet:
   SELECT: Cells B17:B18
   Use the fill handle to copy the selected formulas in column B across to column F.
9. Save your work. Print the worksheet. Close the file and exit Excel.

Data File: EX06HE02 (14.0K)