Case Problems

Download as a Word document: Case Problems Ch02 (192.0K)

You have just accepted an internship in the accounting department of Hancock Industries. Since you are taking an Excel class, your supervisor has asked you to help her improve some of the company’s workbooks. The first project your assigned is to improve the Cash from Operations statement, saved as "EX02CP-HIIS" as shown below in Figure 2-3. After opening the file, save it as "Income" to your personal storage location.

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=gif:: ::/sites/dl/free/0072470941/26807/Ch02_Image1cp.gif','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>

The first thing you notice is that the format is a little crowded. You and your supervisor decide to add some white space to the various sections. Increase the row height of rows 3, 8 and 14 to 22 points. Next Bold face the text labels for the same rows.

Your next assignment is to enter the formulas in column E to calculate Total Revenue, Total Expenses, and Net Income. Use the AutoSum feature for the revenues and expenses (Note: with expenses you will need to select the cell range, as it is in a different column). Subtract expenses from revenue to calculate net income.

Lastly the Report Heading area needs to be enhanced. First, make the main heading "Hancock Industries" an 18-point font, and boldface. Insert a new row between rows two and three. On this row, type the label "For the Period Ending April 30, 2001" AutoFit the row height of the new row. Merge and center the text labels in rows 1, 2 and 3 between columns A and E. Your window should now look similar to Figure 2-4. Save the file and close Excel.

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=gif:: ::/sites/dl/free/0072470941/26807/Ch02_Image2cp.gif','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>

Data File: EX02CP_HIIS (13.0K)

Your employer, Mike Richardson the owner, is taking out a loan to make some improvements to the store such as new display cases, a point of sale computer, and back office computer. He has started an Amortization Schedule, but would like you to complete it. To help him complete the schedule, open the file "EX02CP-RHG" as shown below in Figure 2-5. Then save the file as "Loan" to your personal storage location.

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470941/26807/Ch02_Image3cp.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>

First, Widen each column to fit the labels in row 9 as well as the values in column D and E. ( Hint: Select a range of cells that includes all the above constraints, and Click Format, Column, Autofit Selection.) Next, fill the date down to cover the 24 month loan period. Be sure to select both cell A10 and A11, before dragging the fill handle. The formulas which perform the calculations have already been entered in B11 through E11, fill these down as well.

You should now have a worksheet which looks like Figure 2-6.

<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg:: ::/sites/dl/free/0072470941/26807/Ch02_Image4cp.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>

Enhance the worksheet’s title lines, numbers, rows, and column headings to make a pleasing report. Print a copy. Finally, save the file and exit Excel.

Data File: EX02CP_RHG (13.0K)