Coursehero >> Arizona >> DeVry Mesa >> COMP 100

Course Hero has millions of student submitted documents similar to the one below including study guides, homework solutions, papers, exam answer keys and textbook solutions.

100 COMP Student Name Instructor Computer Applications for Business LAB 3 Group Due Date Project Maximum Points 1 2 points 2 2 points 3 2 points 4 2 points 5 1 point 6 1 point TOTAL 10 points Your Score PROJECT ONE Objective To construct an electronic worksheet in a step - by - step fashion. PROJECT DESCRIPTION Construct an electronic spreadsheet, which computes an employee's weekly net pay. Information About This Project One use of a spreadsheet is to compute financial payroll figures. Steps To Complete This Project STEP 1 Open Excel on Your Computer and Design a Spreadsheet Open MS Excel on your computer. When Excel opens to a new worksheet, type, edit and format a new spreadsheet according to the instructions outlined below. . First click cell A1, if it is not already selected, type the word Gross and press Enter . With your cursor located in cell A2, type the word Taxes and press Enter . With your cursor located in cell A3, type the word Net and press Enter . Click cell B1, type the amount $ 578.39 and press Enter . . With your cursor located in cell B2, type the amount $ 123.57 and press Enter . In cell B3, type the following formula, without any spaces, and press Enter . = B1 - B2 Your worksheet should now appear as shown in Figure 1 , below. Figure 1 A 1 2 3 4 5 Gross Taxes Net B $ 578.39 $ 123.57 $ 454.82 C D E F $ 461.31 $ 520.64 $ 415.16 Next, click cell B1, click Insert on the Excel menu bar and select Rows . You should now have a new row inserted above your original top row. Copyright 2005 by P.E.P. Page 1 of 15 COMP 100 Student Name Computer Applications for Business PROJECT ONE LAB 3 Group Now, in your new cell B1, type the following text and press Enter : Week 1 Now click back into cell B1 to make it your active cell once again, position your mouse pointer to the lower right corner of this cell until it becomes a + cross, left click and hold your mouse button and then drag across until you reach worksheet column E, then release your button. With the cell range B1 through E1 now highlighted, click the Center text icon the center the text shown within this cell range. If this icon is not to locate it or, you can also center the available, click the More Buttons icon text by navigating to Format , selecting Cells... , clicking the Alignment tab and changing the Horizontal text alignment to Center. Your worksheet should now appear as shown in Figure 2 , below. Figure 2 A 1 2 3 4 5 Gross Taxes Net B Week 1 $ 578.39 $ 123.57 $ 454.82 C Week 2 D Week 3 E Week 4 F $ 461.31 $ 520.64 $ 415.16 Next, complete your worksheet by typing the remaining amounts given in rows 2 and 3 of Columns C, D and E from Figure 3 , shown below. Figure 3 A 1 2 3 4 5 Gross Taxes Net B Week 1 $ 578.39 $ 123.57 $ 454.82 C Week 2 $ 590.12 $ 128.81 $ 461.31 D Week 3 $ 693.15 $ 172.51 $ 520.64 E Week 4 $ 519.97 $ 104.81 $ 415.16 F Then, use appropriate spreadsheet formulas to compute the missing amounts in cells, C4, D4 and E4. After you have formulated the cells C4, D4 and E4 , ensure that the cells are displaying their respective values with a Currency format. To do this, use your mouse to select ( highlight ) the cell range B2 : E4 , then navigate to Format , choose Cells... and, when the Format Cells window opens, select the Number tab, select the Category: Currency, choose the options 2 decimal places and the dollar $ symbol and finally click OK . Copyright 2005 by P.E.P. Page 2 of 15 COMP 100 Student Name Computer Applications for Business PROJECT ONE LAB 3 Group STEP 2 Total the Entries in your Worksheet With your initial worksheet completed, you will now total the entries in the worksheet. First, click cell F1 and type the word Totals and press Enter . Then use your mouse to highlight the cell range B2 : F4 . With this range selected, click the AutoSum icon located on the Standard Excel Toolbar. Note: if the Standard Toolbar is not visible, click View , point to Toolbars and select Standard . The totals of all the four week amounts are now displayed in cells F2 , F3 and F4 . Ensure that these total amounts are displayed in a Currency format by applying, to these cells, the formatting procedure given in STEP 1 above. . STEP 3 Modify your Worksheet With your initial worksheet completed, modify your worksheet by changing some entries involving the Gross and Taxes. Change the Week 1 Gross amount from $ 578.39 to $ 421.25 . Note that when you enter this change the Net pay in cell B4 changes automatically since you entered a formula in this cell. Similarly, change the Week 2 Taxes amount from $ 128.81 to $ 135.48 . Note that when you enter this change the Net pay in cell C4 changes automatically since you entered a formula in this cell. Also, change the Week 3 Gross to $ 694.15 and change the Week 4 Taxes to $ 110.13 . STEP 4 Print Your Electronic Spreadsheet Finally, type your name and course number in an empty cell below your worksheet amounts. Print your completed worksheet and submit the hardcopy for credit. To print your worksheet, click an empty cell to ensure that no cell is active and then select File from the main Excel menu and choose Print . When the Print dialog box appears, click OK . Copyright 2005 by P.E.P. Page 3 of 15 COMP 100 Student Name Computer Applications for Business LAB 3 Group PROJECT TWO Objective To use formulas within a spreadsheet program. PROJECT DESCRIPTION Design and utilize a spreadsheet that computes the selling price of a retail item based upon a markup on cost according to the following problem scenario: For each item sold, a certain retailer requires a 60 % markup on cost to cover overhead expenses and net profit. Given the cost for various individual items in the accompanying spreadsheet, determine the selling price and the total sales for each item. Information About This Project This lab project uses Microsoft Excel to enable you to design a spreadsheet that simulates the standard retail equation, which is: Cost added to the Markup equals the Selling Price. Or, algebraically, this can be stated as: C + M = S where C is the Cost, M is the Markup and S is the selling price. Often the Markup M is based upon a percentage of the Cost C . Steps To Complete This Project STEP 1 Copy the required worksheet onto your computer screen If it is not yet open, launch MS Excel on your computer. Excel opens to a new worksheet. If you already have Excel open, use the keyboard shortcut Ctrl + N to open a new worksheet. In a new worksheet, type, exactly as it appears, the spreadsheet shown in Figure 1, below. When typing, use the same cell addresses as given below, that is, type the words ITEM NUMBER in cell A1, the word Cost in B1 , etc. Figure 1 Data Table for the Cost and Markup Spreadsheet . A 1 2 3 4 5 6 7 8 9 10 11 12 ITEM NUMBER B COST C MARKUP D SELLING PRICE E F QUANTITY G TOTAL SALES A 2230 B 1189 C 7516 D 2120 E 1030 F 9030 G 8080 L 8991 M 7829 TOTALS $ 12.34 $ 18.19 $ 21.20 $ 24.20 $ 14.24 $ 12.18 $ 20.00 $ 79.81 $ 29.55 12 10 42 18 31 22 33 52 26 Copyright 2005 by P.E.P. Page 4 of 15 COMP 100 Student Name Computer Applications for Business PROJECT TWO LAB 3 Group After you type the worksheet entries from Figure 1 onto your computer worksheet screen, ensure that all the column widths are wide enough to properly display the contents of the cells. To do this, double - click exactly between the column headings ( the gray - colored area ). Perform this maneuver for each of the columns that contain data. A B C D Example, double - click here ( between the columns ) to widen Column A . STEP 2 Determine the Markup Price for Each Item After you have copied the accompanying worksheet onto your computer, you will now begin the computation phase of this project. The computation scheme for this Cost and Markup spreadsheet is as follows: For each item in this table, take a markup percentage of the cost and then add this result to the cost to determine the selling price. Take the selling price and multiply it by the quantity to arrive at the total sales. You will first find the markup on cost for each of the Item Number entries. You can accomplish this by multiplying each of the entries in the Cost column by the required markup percentage and enter these values in their respective cells. First formulate the spreadsheet cell associated with the markup of retail item A 2230 and then perform a drag - and - fill operation for each of the successive markup values. To formulate cell C2 , click on cell C2 to make it your active cell, type the following formula and press Enter . = 0.60 * B2 Click back on cell C2 to make it your active cell once again, position your mouse pointer to the lower right corner of this cell until it becomes a + cross, left click and hold your mouse button and then drag downward until you reach worksheet row 10 , release your button to automatically fill the cells in the range C3 : C10 . Note: the amounts that should appear in the Markup column are dollar amounts, that is, each entry in this column should be the product of 60 % times the cost of the individual item. If they do not show as dollar amounts after you have formulated the cell range C2 : C10 , then highlight this cell range, navigate to Format , choose Cells... then, when the Format Cells window opens, select the Number tab, select the Category: Currency, choose the options 2 decimal places and $ and finally click OK . Determine the Selling Price For Each Item Next find the selling price for each of the retail items. The selling price will be the item's individual cost added to the item's particular markup on cost. Therefore in cell D2 you would enter the formula: = B2 + C2 After you formulate cell D2 , perform a drag - and - fill maneuver on this cell to complete the formulation of the cell range D2 : D10 . . STEP 3 Copyright 2005 by P.E.P. Page 5 of 15 COMP 100 Student Name Computer Applications for Business PROJECT TWO LAB 3 Group STEP 4 Determine the Total Sales for Each Item Compute the total sales by multiplying each individual selling price that you just computed, by the quantity or number of items. For example, in cell G2 you would enter the formula: = D2 * F2 Perform a drag - and - fill maneuver on cell G2 to complete the formulation of the cell range G2 : G10 . STEP 5 Determine the overall total sales for all items Finally, compute the overall total sales by first using your mouse to highlight the cell range B2 : G11 and then clicking the AutoSum icon on the Excel Standard toolbar. If your Standard Excel toolbar is not visible, click View on your main menu bar and then navigate to Toolbars and then select Standard . STEP 6 Prepare your Worksheet for Printing When finished with the design and computational portion of this project, you may improve your spreadsheet's appearance by performing an AutoFormat. This can be accomplished by highlighting, with your mouse, the entire range of cells that comprise your worksheet area. Then click - on Format on the main menu bar and then point to and select AutoFormat . When the AutoFormat window appears, you may choose one of the many Table Formats that appear. To have your spreadsheet centered horizontally across your printed hardcopy, on the main menu bar click - on File , then point to and select the Page Setup option. When the Page Setup window appears, choose the Margins tab and select Horizontally in the Center on page section. Once you are finished with your spreadsheet, remember to type your name, course number and date at the lower portion of your spreadsheet. Next, spell check, save and print your worksheet. Attach the hardcopy to your lab cover sheet for credit. Copyright 2005 by P.E.P. Page 6 of 15 COMP 100 Student Name Computer Applications for Business LAB 3 Group PROJECT THREE Objective To sort data arrays within a spreadsheet program. PROJECT DESCRIPTION For this project, you are to complete TWO separate data sorts. One of these sorts is to be performed in ascending order and the other in descending order. Consult the accompanying instructions to complete this project. Information About This Project The ability to sort data is an important feature of an electronic spreadsheet program. A sort of a selected range of data values can be accomplished in either of two ways. A sort can be performed in ascending order, whereby the data will be ordered from the lowest value to the highest value. Or a sort can be performed in descending order, whereby the data will be ordered from the highest value to lowest value. Many users of spreadsheet programs sort data using the sort ascending A Z or sort descending Z A icons, which typically appear on a spreadsheet's Standard toolbar. These icons are perfect to sort data that is in a stand - alone column, that is, a data array which is not related to another column of data values. However, when sorting a range of cells which come from multiple related columns it is better to use the Sort command that is available in many spreadsheet packages. This is exactly the objective of this project: to sort a group of columns containing related data values. In Excel, to use the Sort command, position your mouse on the Standard menu toolbar and select Data then choose Sort . When sorting a range of worksheet data that includes multiple rows and columns, always ensure that you have the correct match of items after the sort has been performed. Steps To Complete This Project STEP 1 Copy The Required Worksheet Onto Your Computer Screen If it is not yet open, launch MS Excel on your computer. Excel opens to a new worksheet. If you already have Excel open, use the keyboard shortcut Ctrl + N to open a new worksheet. Next, copy the worksheet shown below into your spreadsheet software. Data Sort Worksheet . A 1 2 3 4 5 6 7 8 9 10 Product ID B Average Length C Average Width D Average Height A25 H32 J21 E18 H05 J10 Z16 21.22 28.46 31.15 77.29 32.19 1.06 2.84 38.61 6.45 15.13 44.87 31.00 3.78 9.73 32.19 9.11 18.44 85.15 33.17 1.85 1.88 Copyright 2005 P.E.P. by Page 7 of 15 COMP 100 Student Name Computer Applications for Business PROJECT THREE LAB 3 Group STEP 2 Perform the First Sort - Ascending Order To perform the first sort required for this lab project, follow the steps given below. First use your mouse to highlight, that is, to select, all of the data values in the range of columns from column A to column D and the range of rows from row 3 through row 9 . Now on the MS Excel main menu bar select Data then choose Sort . When the Sort window opens, select Sort By Column B and select the Ascending order option. Since you originally did not use your mouse to highlight the column titles of your spreadsheet, the Sort window should have the following option selected: " My Data has ... No header row ". Now that you have selected the correct options for this particular sort, perform the sort by clicking OK to close the Sort window. Figure 1 select this click this You have now sorted column B in ascending order. This corresponds to sorting the " Average Length " column of the given spreadsheet. To ensure that you have sorted correctly, you should check if (1) the data in column B is indeed arranged in ascending order and (2) if the individual values in column B correspond to their respective original row entries. For example, for product ID number A25, the average length should still be 21.22 , the average width should still be 38.61 and the average height should still be 32.19 . After sorting by Column B, print your spreadsheet and attach it to your lab packet. Make sure that your name, today's date and the course number are included on your spreadsheet. Copyright 2005 by P.E.P. Page 8 of 15 COMP 100 Student Name Computer Applications for Business PROJECT THREE LAB 3 Group STEP 3 Perform the Second Sort - Descending Order To perform the second sort required for this lab project, follow the steps given below. First use your mouse to highlight, that is to select, all of the data values in the range of columns from column A to column D and the range of rows from row 3 through row 9 . Now on the MS Excel main menu bar select Data then choose Sort . When the Sort window opens, select Sort By Column D and select the Descending order option. Since you originally did not use your mouse to highlight the column titles of your spreadsheet, the Sort window should have the following option selected: " My Data has ... No header row ". Now that you have selected the correct options for this particular sort, perform the sort by clicking OK to close the Sort window. You have now sorted column D in descending order. This corresponds to sorting the " Average Height " column of the given spreadsheet. To ensure that you have sorted correctly, you should check if (1) the data in column D is indeed arranged in descending order and (2) if the individual values in column D correspond to their respective original row entries. For example, for product ID number A25, the average length should still be 21.22 , the average width should still be 38.61 and the average height should still be 32.19 . After sorting by Column D, print your spreadsheet and attach it to your lab packet. Make sure that your name, today's date and the course number are included on your spreadsheet. Copyright 2005 by P.E.P. Page 9 of 15 COMP 100 Student Name Computer Applications for Business LAB 3 Group PROJECT FOUR Objective To construct a worksheet to compute a monthly home mortgage payment. PROJECT DESCRIPTION The Simmons family plans on purchasing a new home that has a purchase price of $ 250,000 . They intend on making a down payment and then securing a loan, which will be repaid to the loan institution over a period of 30 years. The loan institution offers the Simmons family an 8.50 % loan rate and requires that they make a down payment of 15 % of the purchase price. Prepare a worksheet that will display all the necessary information for the above scenario and then use the Excel PMT function to determine the monthly mortgage payment. Information About This Project This project utilizes the Excel PMT function, which is used to determine the monthly payment amount for a personal home mortgage. The structure of a PMT function is: = PMT ( annual rate / compounding period , number of payments , loan amount ) Steps To Complete This Project STEP 1 Open Excel And Copy The Given Worksheet Onto Your Computer Screen First open MS Excel on your computer. Then, copy the worksheet shown below onto a new Excel worksheet. Figure 1 1 2 3 4 5 6 7 8 9 10 When designing your worksheet, make sure that your column widths are wide enough to display each of the entries within the column. To make a border around cell B9, click this cell and then click the drop down button on the Borders icon icon . on the Excel formatting toolbar and choose the Thick Box Border Monthly Payment Mortgage Payment Worksheet A 30 Year Mortgage Purchase Price Down Payment Loan Amount Interest Rate Mortgage Term Total Payments B $ 250,000.00 C D 8.50 % 30 years Copyright 2005 by P.E.P. Page 10 of 15 COMP 100 Student Name Computer Applications for Business PROJECT FOUR LAB 3 Group STEP 2 Formulate Your Excel Mortgage Worksheet After you have copied the worksheet shown in Figure 1, use cell references to enter the correct formulas that will complete any missing entries. In cell B3, compute the down payment by typing in this cell the following formula and then pressing Enter . This formula multiplies the applicable down payment percentage by the purchase price. = B2 *15 % Next, subtract cell B2, which contains the purchase price, by B3, which contains the down payment, to arrive at the loan amount. To do this type the following formula in cell B4 and then press Enter . = B2 - B3 In cell B7, enter the following formula and press Enter . = B6 * 12 This formula will display, in cell B7, the total number of monthly payments that are to be made in the span of 30 years. Finally, in cell B9, enter the following formula and press Enter . = PMT ( B5 / 12 , B7 , - B4 ) Note: do not use any spaces when typing the above formula This formula will compute a monthly mortgage payment given the interest rate in cell B5, the number of payments in cell B7 and the loan amount in cell B4. Note: the negative sign within the PMT function is used so that a positive monthly payment is displayed in the cell containing this formula. STEP 3 STEP 4 Print Your Excel Mortgage Worksheet Print your mortgage worksheet and submit it with your lab packet for credit. Modify Your Excel Mortgage Worksheet Next, modify your project by changing the down payment percentage from 15 % to 20 % . STEP 5 Print Your Modified Excel Mortgage Worksheet Print your modified mortgage worksheet and submit it with your lab packet for credit. STEP 6 Save Your Modified Excel Mortgage Worksheet Finally, save your completed mortgage worksheet and proceed to the next project. Copyright 2005 by P.E.P. Page 11 of 15 COMP 100 Student Name Computer Applications for Business LAB 3 Group PROJECT FIVE Objective To construct a worksheet to compare monthly home mortgage payments. PROJECT DESCRIPTION The Higgins family plans on purchasing a new home that has a purchase price of $ 325,000 . They plan on making a down payment and then securing a loan, which will be repaid to the loan institution over a period of years. The loan institution offers the Higgins family an 8.75 % loan rate and an option of a 15 year or a 30 year mortgage term. The loan institution requires that they make a down payment of 10 % in either case. Prepare a worksheet that will show a side - by - side comparison of the different monthly mortgage payments for both the 30 year and the 15 year options. Use the Excel PMT function and determine the monthly mortgage payments. Information About This Project This project utilizes the Excel PMT function to determine the monthly payment amount for a personal home mortgage. The structure of a PMT function is: = PMT ( annual rate / compounding period , number of payments , loan amount ) Steps To Complete This Project STEP 1 Open Excel And Copy The Given Worksheet Onto Your Computer Screen This particular project is a modification of the prior project. With MS Excel open to your worksheet from the prior project, highlight the cell range A1 to C9 and click the Copy icon to copy this cell range. Then click cell E1 and click the Paste icon . Now change your worksheet to include the amounts stated in the Project Description portion above. Refer to Figure 1. Figure 1 1 2 3 4 5 6 7 8 9 10 Monthly Payment Monthly Payment Expanded Mortgage Payment Worksheet A 30 Year Mortgage Purchase Price Down Payment Loan Amount Interest Rate Mortgage Term Total Payments B $ 325,000.00 C D E 15 Year Mortgage Purchase Price Down Payment Loan Amount F $ 325,000.00 G 8.75 % 30 years Interest Rate Mortgage Term Total Payments 8.75 % 15 years Copyright 2005 by P.E.P. Page 12 of 15 COMP 100 Student Name Computer Applications for Business PROJECT FIVE LAB 3 Group STEP 2 Formulate your Worksheet With your new worksheet modified to conform to the amounts described in the scenario within the Project Description potion of this project, you can now begin formulating your worksheet to compute both the 15 year and the 30 year monthly mortgage payments. First, compute the monthly mortgage payment for the 30 year mortgage option. To compute the down payment in cell B3, multiply the purchase price by the applicable down payment percent. To compute the loan amount in cell B4, subtract the down payment from the purchase price. Next, subtract cell B2 by B3 to arrive at the loan amount in cell B4. In cell B7, find the total number of payments to be made by multiplying the number of months in one year by the term of the mortgage. Now compute the monthly mortgage payment in cell B9 by using the formula. = PMT ( B5 / 12 , B7 , - B4 ) Similarly, determine the required entries to find the monthly mortgage payment for the 15 year option. STEP 3 Compute the Difference in the Two Payments Below the main entries of your mortgage worksheet, compute the difference between the two payments and display your result within your worksheet. Label it as such. Ensure that the difference amount is displayed in a Currency format. STEP 4 Print Your Worksheet When you are finished finding the monthly payments for both options, enter your name, course number and date in an unused portion of your worksheet. Then print a copy of your worksheet and attach it to this lab packet for credit. Save your worksheet and proceed to the next project. STEP 5 Modify Your Worksheet Modify this lab project by changing the problem scenario such that the loan institution now offers the Higgins family a 9.60 % loan rate and an option of a 15 year or a 20 year mortgage term. The loan institution requires that they make a down payment of 20 % in either case. As before, below the main entries of your mortgage worksheet, compute the difference between the two payments and display your result within your worksheet. Label it as such. Copyright 2005 by P.E.P. Page 13 of 15 COMP 100 Student Name Computer Applications for Business LAB 3 Group PROJECT SIX Objective To construct a MS Word document in a step - by - step fashion. PROJECT DESCRIPTION This project has you preparing a word processing document in a step - by - step fashion. Information About This Project Formatting and editing techniques are critical when preparing professional documents. Steps To Complete This Project STEP 1 STEP 2 Launch MS Word on your computer. Starting with a new MS Word document and with your cursor located at the upper left corner, press your keyboard Enter key four times. Then press your keyboard upward arrow cursor key two times to go back up. Change the font size to 18, click your B Bold icon, click your I Italic icon and type the word Invoice. Press your keyboard Enter key two times. Change the font size to 12 and deselect both the B Bold icon and the I Italic icon. Click Format on the menu bar and select Borders and Shading... . As shown below, click the Borders tab, change the width to 2 pt and, in the Preview section, click to apply the bottom border button window by clicking OK . as shown below. Close the STEP 3 STEP 4 STEP 5 Copyright 2005 by P.E.P. Page 14 of 15 COMP 100 Student Name Computer Applications for Business PROJECT SIX LAB 3 Group STEP 6 STEP 7 STEP 8 Press your keyboard Enter key four times. Type the word Date and immediately after this word type a colon. Press your keyboard Tab key twice and type today's date. Press your keyboard Enter key two times. Type the word Amount and immediately after this word type a colon. Press your keyboard Tab key once. Then, type $ 578.32 and then press your keyboard Enter key four times. Press your keyboard Enter key two times. Type the word Number and immediately after this word type a colon. Tab once and type 103131A. Press your keyboard Enter key eight times. Then press your keyboard upward arrow cursor key two times to go back up. Click Format on the menu bar and select Borders and Shading... . As shown in STEP 5 , click the Borders tab, change the width to 2 pt and, in the Preview section, click to apply the bottom border button Borders and Shading window by clicking OK . . Close the STEP 9 STEP 10 STEP 11 Click View on the main Word menu bar and select Header and Footer . In the Header portion, press Enter once and type the text Higgins Tax Services. On the Header and Footer toolbar, click the icon that switches you between the Header and the Footer and type your name and course number in the Footer portion of your document. Click the Close button on the Header and Footer toolbar. STEP 12 STEP 13 STEP 14 Spell check your document and make any necessary spelling and / or grammar corrections. Perform a print preview to ensure that your document fits on one page. Print your document and submit the hardcopy of this lab project for credit. Copyright 2005 by P.E.P. Page 15 of 15

Find millions of documents here - Study Guides, Homework Solutions, Papers, Exam Answer Keys and more. Course Hero has millions of course related materials that will enable you to learn better, faster and get an A in all your courses.
Below is a small sample set of documents: