DUE DATE: 03/22/2023Comparing Transportation CostsCOMPLETE...

In the Lab Comparing Transportation Costs Grading Rubric Part 1: Consider This: Plan Ahead Guidelines Grading Notes Percent Score 1. Determine the worksheet structure · Worksheet should be organized in a manner that is readable and makes sense 25 2. Create the worksheet · Worksheet contains proper title, subtitle, and headings · Formulas are correct and reference proper cells · Worksheet properly calculates fuel costs as instructed 25 3. Format the worksheet · Worksheet title and subtitle formatted appropriately (title may be slightly larger than subtitle) · Column headings formatted so that they stand out · Numbers formatted appropriately as currency style or comma style · Text formatted so that it is readable and fits in with other worksheet contents 25 4. Create and use charts · Chart accurately depicts the cost comparisons 25 Part 2: Critical Thinking Decisions This assignment requires students to explain why they made these decisions in creating their worksheet: · How to lay out the data in the worksheet · Which chart types to use Module 4: Financial Functions, Data Tables, and Amortization Schedules Shelly Cashman: Microsoft Excel 2019 Module 4: Financial Functions, Data Tables, and Amortization Schedules © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 1 Objectives (1 of 2) Assign a name to a cell and refer to the cell in a formula using the assigned name Determine the monthly payment of a loan using the financial function PMT Use the financial functions PV (present value) and FV (future value) Create a data table to analyze data in a worksheet Create an amortization schedule Control the color and thickness of outlines and borders © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 2 Objectives (2 of 2) Add a pointer to a data table Analyze worksheet data by changing values Use names and print sections of a worksheet Set print options Protect and unprotect cells in a worksheet Hide and unhide worksheets and workbooks Use the formula checking features of Excel © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 3 Project: Mortgage Payment Calculator with Data Table and Amortization Schedule (1 of 3) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 4-1: Excel worksheet using proper design guidelines 4 Project—Mortgage Payment Calculator with Data Table and Amortization Schedule (2 of 3) To Apply a Theme to the Worksheet Start Excel and create a blank worksheet in the Excel workbook Apply a theme to the workbook To Enter the Section and Row Title and System Date Select a cell and type a section title Select a range and then click “Merge & Center.” Click the Cell Styles button and select a style Click Middle Align button to vertically align the text Right-click a cell to open the shortcut menu and then click Format Cells. Click Number tab, click Date in the Category, scroll down and select a date format, click OK © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 5 Project—Mortgage Payment Calculator with Data Table and Amortization Schedule (3 of 3) To Adjust the Column Widths and Row Height Click column heading A and then drag through column B to select both columns. Position the pointer on the right boundary of column B and then drag to the left until ScreenTip indicates desired width Click row heading 1 to select it and then drag through row heading 2 to select both. Position the pointer on the bottom of row heading 2 and then drag until the ScreenTip indicates the desired height To Change the Sheet Tab Name Double-click the Sheet tab and enter a new name © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 6 Creating Cell Names (1 of 8) To Format Cells before Entering Values Select the desired range and while holding down CTRL, select the nonadjacent range Right-click one of the ranges to display the shortcut menu and then click Format Cells to display the Format Cells dialog box Click the NUMBER tab, select the desired category from the list Click OK © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 7 Creating Cell Names (2 of 8) To Enter the Loan Data © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 4-5: display of Increase Decimal button and Align Right button 8 Creating Cell Names (3 of 8) To Create Names Based on Row Titles Select the desired range Click the “Create from Selection” button on the FORMULAS tab to display the Create Names from Selection dialog box Click the OK button to name the cells selected in the right column of the selection © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 4-6: display of Formulas tab with “Create from Selection” button 9 Creating Cell Names (4 of 8) To Enter the Loan Amount Formula Using Names Select the cell to contain the formula While entering the formula, double-click the cells to include in the formula to use the names of the cells rather than the cell references Click the Enter box to assign the formula to the selected cell © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 4-9: display of formula created using names 10 Creating Cell Names (5 of 8) To Enter the PMT Function Select the cell to contain the formula Enter the PMT function ex: –pmt (Rate/12, 12*Term, Loan_Amount) © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 4-12: display of Enter button with PMT function assigned to active cell F6 11 Creating Cell Names (6 of 8) Table 4-1 Frequently Used financial Functions FunctionDescription FV (rate, periods, payment)Returns the future value of an investment based on periodic, constant payments and a constant interest rate. PMT (rate, periods, loan amount)Calculates the payment for a loan based on the loan amount, constant payments, and a constant interest rate. PV (rate, periods, payment)Returns the present value of an investment. The present value is the total amount that a series of future payments now is worth. © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Table 4-1: Frequently Used Financial Functions 12 Creating Cell Names (7 of 8) To Determine the Total Interest and Total Cost Select a cell and enter a formula © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 4-13: display of results of formulas in cells F7 and F8 13 Creating Cell Names (8 of 8) To Enter New Loan Data © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 4-14: display of new loan data entered 14 Using a Data Table to Analyze Worksheet Data (1 of 4) To Enter the Data Table Title and Column Titles Select a cell and type the desired data table section title Elect a cell and then click the Format Painter button on the HOME tab to copy the format of the cell and then click another cell and apply the copied format Position the pointer on the bottom boundary of a row heading and drag up or down until the ScreenTip indicates the desired height © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 15 Using a Data Table to Analyze Worksheet Data (2 of 4) To Create a Percentage Series Using the Fill Handle Type the first two percentages in the series, each in an individual cell Select the two cells containing the percentages Drag the fill handle through the last cell in the desired series—do not lift your finger or release the mouse button Lift your finger or release the mouse button to generate the percent series Click the Increase decimal button on the HOME tab to increase the number of decimal places © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 16 Using a Data Table to Analyze Worksheet Data (3 of 4) To Enter the Formulas in the Data Table Choose a cell and make it active, then press the RIGHT ARROW key to enter the first parameter of the function to be used in the data table Type the function and then press the RIGHT ARROW key © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 4-19: display of formulas as they are placed in rows of data table 17 Using a Data Table to Analyze Worksheet Data (4 of 4) To Define a Range as a Data Table Select the range in which to create the data table Click Data Table on the What-If Analysis menu to display the Data Table dialog box Click the “Column input cell” box and then click the cell to select as the desired input cell for the data table Click the OK button to create the data table © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› 18 Creating an Amortization Schedule (1 of 12) To Change the Column Widths and Enter Titles © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 4-24: display of column widths changed and column titles entered and centered 19 Creating an Amortization Schedule (2 of 12) To Create a Series of Integers Using the Fill Handle © 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. ‹#› Figure 4-25: display of range selected for fill series to complete 20 Creating an Amortization Schedule (3 of 12) Table 4-2 Formulas for the Amortization Schedule CellColumn HeadingFormulaExample I5Beginning Balance=D8The beginning balance (the balance at the end of a year) is the initial loan amount in cell D8. J5Ending Balance=IF(H5<=$f$5, pv($f$4/12, 12*($f$5–h5), –$f$6), 0)the ending balance (the balance at the end of a year) is equal to the present value of the payments paid over the remaining life of the loan. (this formula is fully explained in the following text.) k5paid on principal=i5–j5the amount paid on the principal at the end of pv($f$4/12,="" 12*($f$5–h5),="" –$f$6),="" 0)="" the="" ending="" balance="" (the="" balance="" at="" the="" end="" of="" a="" year)="" is="" equal="" to="" the="" present="" value="" of="" the="" payments="" paid="" over="" the="" remaining="" life="" of="" the="" loan.="" (this="" formula="" is="" fully="" explained="" in="" the="" following="" text.)="" k5="" paid="" on="" principal="I5–J5" the="" amount="" paid="" on="" the="" principal="" at="" the="" end="">