Case Problems

Download as a Word document: Case Problems Ch06 (267.0K)

You’ve just received the file EX06CP01, which contains the New Inventory Report (Figure 6-4). It was partially completed, and you’ve been asked to finish the job. After opening the file, save it with the name "Inventory." You have been instructed to assign Part numbers to each new inventory item. Part numbers are a combination of the first three letters of the Part Description and a three digit randomly generated number. No two part numbers may have the same number. The following list shows the items you’ve been asked to complete:

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

• In the "Scratchpad" area of the worksheet, generate a random list of 6 numbers. If necessary Press the F9 key until all numbers are unique. Use the formula: =INT(RAND()*1000).
• Copy and Paste Special the values only in the same location.
• Create a function that parses out the first three letters of the description and capitalizes them.
• Concatenate the letters from the previous step, followed by a dash, with the random value generated in the previous step. A part number should look like: KEY-836.
• Copy and Paste Special (values only) in column A under the part number column heading. At this point, your screen should look similar to Figure 6-5, the number part of the part number will be different.

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

When completed save and print the worksheet. Then save and close the file.

Data File: EX06CP01 (13.0K)

Roscommon Hardware and Gifts

The store is considering expanding their main location to accommodate the increased corporate staff due to growth. They have contacted the area banks and other lending institutions regarding financing for the project. You are to prepare the analysis of the various finance options. You start by opening the file "EX06CP02" and immediately saving it to your personal storage location as "Capital." perform the following steps to complete the analysis (shown in Figure 6-6):

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

Figure 6-6

• Format the labels in row 8, column A through F to wrap within their respective cells.
• Use the "PMT" Function to calculate the monthly payment at each financial institution. (Hint: use an absolute reference for Pv, remember you are calculating a monthly payment, and the rate and term are in annual rate and years respectively).
• Create a formula to calculate the Annual Payment Amount for each institution.
• Create a formula to calculate the total interest paid over the life of the loan. (Hint: computer the total payments, and subtract the original loan amount to arrive at the value).
• Make sure all the calculated values are using the same number format. Use the format painter if you need to change any values’ format. Also, you may need to adjust the column width.

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

Figure 6-7

When finished, your worksheet should look similar to Figure 6-7. Save and print the worksheet. Then close the file and exit Excel.

Data File: EX06CP02 (13.0K)