Townsend Mortgage Company Input Area Today's Date: Pmts Per Year: 12 Loan # Down Amount House Cost Payment Financed 452786 453000 453025 452600 452638 $ $ $ $ $ Statistics Total Average Median Lowest Highest # of Mortgages 400,000 350,000 175,500 265,950 329,750 $ $ $ $ $ 80,000 60,000 30,000 58,000 65,000 Summary Statistics Down Amount House Cost Payment Financed Mortgage Rate 3.625% 3.940% 3.550% 2.500% 3.250% Rate Per Period # of Pmt % Date Years Periods Financed Financed 25 30 25 15 30 0.0% 5/1/2016 0.0% 11/3/2016 0.0% 4/10/2017 0.0% 10/14/2017 0.0% 2/4/2018 Payoff Year 2041 2046 2042 2032 2048 Townsend Mortgage Company Input Area Today's Date: # Pmts Per Year: PMI Rate: Down Pmt Rate: Years 9/23/2017 12 0.38% 20.0% Loan # House Cost 392786 393000 393025 392600 392638 $ $ $ $ $ 425,750 335,900 205,500 285,900 329,900 15 25 30 Down Payment $ $ $ $ $ 86,000 75,500 30,000 50,000 70,000 Rate 3.250% 3.625% 3.750% Amount Financed % Down $ $ $ $ $ 339,750 260,400 175,500 235,900 259,900 20.2% 22.5% 14.6% 17.5% 21.2% Years 25 30 25 15 30 APR Monthly Payment Monthly PMI Glossary MAX function A predefined formula that finds the highest value in range MEDIAN function A predefined formula that finds the midpoint value which is the value that one half of the values in a list above below MIN function A predefined formula that finds the lowest value in a Mixed cell reference A designation that combines an absolute coll reference with a relative cell reference Nested function A function that contains another function embedded inside one or more of its arguments NOW function A predefined formula that uses the computer's dock to display the current date and time in a cell Nper The number of payment periods over the life of a loan or investment, the second argument in the PMT function PMT function A predefined formula in Excel that calculates the periodic loan payment Pv The present value of a loan or an annuity, the third argument in the Absolute cell reference A designation that provides a permanent reference to a specific cell When you copy a formula containing an absolute reference, the cell reference in the copied formula does not change, regardless of where you copy the formula An absolute cell reference appears with a dollar sign before both the column letter and the row number, such as 5854 Argument A variable or constant input, such as a cell reference or value needed to complete a function. The entire group of arguments for a function is enclosed in parentheses AVERAGE function A predefined formula that calculates the arithmetic mean or average of values in a range Breakpoint The lowest value for a specific category or series in a lookup table Circular reference A situation that occurs when a formula contains a director an indirect reference to the cell containing the formula Column index number The number of the column in the lookup table that contains the return values COUNT function A predefined formula that tallies the number of cells in a range that contain values you can use in calculations, such as the numerical and date data, but excludes blank cells or text entries from the tally COUNTA function A predefined formula that tallies the number of cells in a range that are not blank, that is, cells that contain data, whether a value, text, or a formula COUNTBLANK function A predefined formula that tallies the number of cells in a range that are blank Formula AutoComplete A feature that displays a list of functions and defined names that match letters as you type a formula. Function A predefined computation that simplifies creating a complex calculation and produces a result based on inputs known as arguments. Function Screen Tip A small pop up description that displays the arguments for a function as you enter it directly in a cell HLOOKUP function A predefined formula that looks up a value in a horizontal lookup table where the first row contains the values to compare with the lookup value PMT function and refers to the original amount of the loan Range name a word or string of characters assigned to one or more colis It can be up to 255 letters, characters, or numbers but must start wie a letter or underscore and have no spaces or special symbols. Rate the periodic interest rate, the percentage of interest paid for each payment period; the first argument in the PMT function Relative cell reference A designation that indicates a cell's relative location within the worksheet using the column letter and row number, such as B5. When a formula containing a relative cell reference is copied, the cell references in the copled formula change relative to the position of the copied formula, Quick Analysis A tool that provides a fast way to analyze a selected range of data by inserting basic calculations, charts, convert the data to a table, or apply conditional formatting or other analytical features SUM function A predefined formula that calculates the total of values contained in two or more cells. IF function A predefined logical formula that evaluates a condition and returns one value if the condition is true and a different condition if the value is false Syntax The rules that dictate the structure and components required to perform the necessary calculations in an equation or to evaluate expressions Table array The range that contains the body of the lookup table, excluding column labels. The first column must be in ascending order to find a value in a range, or it can be in any order to look up an exact alue. It is the second argument within a VLOOKUP Or HLOOKUP function TODAY function A predefined formula that displays the current date in a cell Logical test An expression that evaluates to true or false; the first argument in an IF function Lookup table A range that contains data for the basis of the lookup and data to be retrieved in a vertical lookup table, the first column contains a list of values to compare to the lookup value. In a horizontal lookup table, the first row contains a list of values to compare to the lookup value. Lookup value The cell reference of the cell that contains the value to look up within a lookup table. VLOOKUP function A predefined formula that looks up a value and returns a related result from the lookup table. Glossary 486 HU His il 118 Beyond the Classroom FROM SCRATCH College Sports You want to create a spreadsheet to display data for your favorite college sportent and Scores Conduct an Internet search to identify the game dates, your team's scores, the opponent, and the opponent's score for each game for the last complete season. Enter the data into a new RESEARCH workbook and save the workbook as eo2b2Sports_LastFirst, Games are usually scheduled CASE seven days apart. Enter this value on a second sheet, assign a range name, and then use the range name in a formula to calculate the game dates based on the original game date. In some instances, you may have to enter a date if more or fewer days exist between two game dates. In the fifth column, use an IF function to determine if your team won or lost each game; display either Win or Lose. In the sixth column, use an IF function to calculate by how many points your team won each game or display an empty string by entering " in the value_if_false argument if your team lost. Create a statistics area to calculate the average, median, low, and high scores for your team. Below the won-by points column, use two different count functions to count the num- ber of games won and lost. Use Help to learn about the COUNTIF function and use this function to count the number of games won based on the number of Win entries. Use mixed references in the function's first argument, copy the function, and then edit the second argu- ment of the copied COUNTIF function to calculate the number of games lost. The summary area should have four count functions. Add titles and column labels, format data within the columns, and then include the URL of where you got the data. Include a footer with your name on the left side, the date code in the center, and the file name code on the right side. Save and close the workbook, and submit based on your instructor's directions. Park City You and some friends are planning a Labor Day vacation to Park City, Utah. You have secured Condo Rental a four-day condominium that costs $1,200. Some people will stay all four days; others will stay part of the weekend. One of your friends constructed a worksheet to help calculate each DISASTER person's cost of the rental. The people who stay Thursday night will split the nightly cost RECOVERY evenly. To keep the costs down, everyone agreed to pay $30 per night per person for Friday, Saturday, and/or Sunday nights. Depending on the number of people who stay each night, the group may owe more money, Kyle, lan, Isaac, and Daryl agreed to split the difference in the total rental cost and the amount the group members paid. Open e02b3ParkCity, address the circular reference error message that displays, and save the workbook as e02b3 ParkCity_ Last First Review the worksheet structure, including the assumptions and calculation notes at the bottom of the worksheet. Check the formulas and functions, making necessary corrections. With the existing data, the number of people staying each night is 5, 7, 10, and 10, respec- tively. The total paid given the above assumptions is $1,110, giving a difference of $90 to be divided evenly among the first four people. Kyle's share should be $172.50. In the cells containing errors, insert comments to describe the error and fix the formulas. Verify the accuracy of formulas by entering an IF function in cell li to ensure the totals match. Nick, James, and Body inform you they can't stay Sunday night, and Rob wants to stay Friday night. Change the input accordingly. The updated total paid is now $1,200, and the differ- ence is $150. Include a footer with your name on the left side, the date code in the center, and the file name code on the right side. Save and close the workbook, and submit based on your instructor's directions. Beyond the Classroom • Excel 2013 Lapsune Exercise Myllab Grader trad book named 24interview Last First that its five in the first woma. In the second column, enter a per ample, the first question might count 5% of the total studento rate five interviewees on the questions is high Incude column label with a first name for first intervive column, use the AVERAGE func persons individual cores by their respective weights arrede Copy the formule to the other candidates ehted score of 45. Aign a range Dame to the mes add a row labeled Second Interview Enter an IF ehted score is greater than or equal to 4.5. then dis function for the other candidates. Include a footer code in the center and the file name code on the and submit based on your instructor's directions Jo a sales representative at the locales Center, Health Gym Your manager expects each representative www membership data, so you created a spreadsheet to store Membership costs are based on membership type Clients can locker for an additional annual fee You are required to col na down payment based on membership type, determine the Nance, and then calculate the monthly payment based on a stan and interest rate. In addition, you need to calculate general statis sommarize for your manager Spot check results to make ou created formulas and functions correctly b. Find and correct the circular reference for the balance The balance is the difference between the total due and the down payment C. Copy the two formulas for the rest of the clients. Perform Preliminary Work Sia need to open the starting workbook you created, acknowledge desting circular reference error and assign a range name to the Embership lookup table. You will correct the circular reference Calculate the Monthly Payment Clients pay the remainder by making monthly payments. Monthly payments are based on the number of years specified in the dient contract and a standard interest rate a. Insert the function in cell 5 to calculate the first client's monthly payment, using appropriate relative and absolute cell references b. Copy the formula down the column c. Edit the formula by changing the appropriate cell reference to a mixed cell reference. Copy the formula down errar later Open the e02e1Gym vorkbook, click Help, read about circular references, close the Help window that displays, and then save the workbook as e02c1Gym LastFirst. b. Assign the name Membership to the range A18:C20, Insert function to display the current date in cell B2. Calculate Cost, Annual Total, and Total Due Su are ready to calculate the basic annual membership cost and the wanal cost. The basic annual membership is determined based e cach client's membership type, using the lookup table 2. Insert a lookup function in cell C5 to display the basic annual membership cost for the first client. b. Use an IF function in cell ES to calculate the annual total amount, which is the sum of the basic cost and locker fees for those who rent a locker. For people who do not rent a locker, the annual cost is only the cost shown in column C. The Locker column displays Yes for cients who rent a locker and No for those who don't Calculate the total amount due in cell G5 for the first client based on the annual total and the number of years in the contract d. Copy the three formulas down their respective columns Finalize the Workbook You need to perform some basic statistical calculations and finalize the workbook with formatting and page setup options a. Calculate totals on row 14. b. Insert the appropriate functions in the Summary Statistics section of the worksheet: cells H18:H22. Format the payments with Accounting Number Format and format the number of new members approp c. Format the other column headings on rows 4 and 17 to match the fill color in the range E17:117. Wrap text for the column headings d. Format the monetary values for Andrews and the total row with Accounting Number Format. Use zero decimal places for whole amounts and display two decimal places for the monthly payment. Apply Comma Style to the internal monetary values. Underline the values before the totals and apply Double Accounting Underline (found in the Format Cells dialog box) for the totals. e. Set 0.3" left and right margins and ensure the page prints on only one page f. Insert a footer with your name on the left side, the date code in the center, and the file name code on the right side. g. Save and close the workbook, and submit based on your instructor's directions Determine the Down Payment and Balance To seed to collect a down payment based on the type of member for each new dient. Then you must determine how much each 2. Insert a lookup function in cell 5 to display the amount of down payment for the first client based on the membership type. Capstone Exercise Excel 2013 485 Capstone Exercise Interview After watching the video, create a workbook named e02b4Interview_LastFirst that listas Walkthough to seven common interview questions in the first column. In the second column, enter a per centage weight for each question. For example, the first question might count 5% of the tong SOFT SKILLS The total weights should be 100%. Include columns to rate five interviewees on the question S CASE using a scale of 1-5 where I is low and 5 is high. Incude a column label with a first name se each interviewee. At the bottom of the first interviewee's column, use the AVERAGE fure tion with the argument to multiply that person's individual scores by their respective weighs using relative and absolute references correctly. Copy the formula to the other candidates Add an input area for a minimum weighted score of 4.5. Assign a range name to the score. On the row below the weighted scores, add a row labeled Second Interview? Enter an function for the first candidate: If the weighted score is greater than or equal to 4.5, then dis play Yes; otherwise, display No. Copy the function for the other candidates. Include a focizz with your name on the left side, the date code in the center, and the file name code on the right side. Save and close the workbook, and submit based on your instructor's directions. You are a sales representative at the local fiane ceste Fitness Gym. Your manager expects each representati weekly new membership data, so you created a spread data. Membership costs are based on membership type rent a locker for an additional annual fee. You are te lect a down payment based on membership type, de balance, and then calculate the monthly payment bus dard interest rate. In addition, you need to calculate tics to summarize for your manager. Spor check te sure you created formulas and functions correctly FROM SCRATCH Perform Preliminary Work You need to open the starting workbook you create the existing circular reference error, and assign a ta membership lookup table. You will correct the ca error later a. Open the e02c1Gym workbook, click Heig circular references, dose the Help window and then save the workbook as e02c1Gym b. Assign the name Membership to the ram C. Insert a function to display the current Calculate Cost, Annual Total, an You are ready to calculate the basic annual memi total annual cost. The basic annual membership STEP 4 > INSERT A LIST OF RANGE NAMES Before submitting the completed workbook to Erica, you want to create a documentation worksheet that lists all of e range names in the workbook. Refer to Figure 39 as you complete Step 4. Step Select this option x Step Column labels typed Step List posted in worksheet FIGURE 39 Range Names inserted in a Formula a. Click New sheet to the right of the worksheet tabs and double-dick the default sheet name, Sheet 1. Type Range Names and press Enter. You inserted and renamed the new worksheet to reflect the data you will add to it. b. Type Range Names in cell Al and type Location in cell B1. Bold these headings. These column headings will display above the list of range names. C. Click cell A2, click Use in Formula in the Defined Names group on the FORMULAS tab, and then select Paste Names. The Paste Name dialog box opens, displaying all of the range names in the workbook. d. Click Paste List. Excel pastes an alphabetical list of range names starting in cell A2. The second column displays the locations of the range names. e. Increase the widths of columns A and B to fit the data. f. Save and close the workbook, and submit based on your instructor's directions TIP List of Range Names When you paste range names, the list will overwrite any existing data in a worksheet 50 consider pasting the list in a separate worksheet. If you add, edit, or delete range names, the list does not update automatically. To keep the list current you would need to paste the list again Hands-On Exercise 4 459
Purchase answer to see full attachment