Fall 2013 Name General test instructions: Put your name in the


Question Description:

35

Problem 1 Problem Score 0 Project Budget Expenditures Balance Remaining % Over/Under Budget 2012.01 $500,000 $275,844 2012.02 $115,000 $125,832 2012.03 $125,000 $79,550 2012.04 $200,000 $111,115 2012.05 $100,000 $131,500 Totals A. Complete the table above using functions whenever possible. Score (out of 10) 0 Balance = Budget – Expenditures Remaining % = (Budget – Expenditures)/Budget Fill in the totals row B. In the Over/Under Budget cells, use an IF function to display “Over Budget” if the project is over budget (i.e. Expenditures > Budget) Score (out of 10) 0 C. Use conditional formatting in the Remaining % column as follows: Score (out of 10) 0 Formatting should match this cell if the Remaining % is negative (i.e. over budget). Formatting should match this cell if the Remaining % between 0 and 25%. Formatting should match this cell if the Remaining %is more that 25%. D. Create a 100% Stacked Column Chart using the values in the Budget, Expenditures, and Balance columns. Include the Headings! Score (out of 10) 0 Problem 2 Problem Score 0 “Insert rows as necessary so you can place the answer for each part directly below the part of the problem you are answering. NOTE: Make sure the Problem Scoring cells still work!” “Ling is considering to open an annuity to save for a down payment on a home. The annuity would be created with an initial deposit of $1,000 (end of year 0). At the end of each of the following ten years, a payment of $2,500 would be made into the annuity. The interest rate is 2.5% compounded annually. Create appropriate What-If values (i.e. the given values in the problem statement and any others you think you might need) and name the cells that contain the What-If values. Use equations which refer to these cell names. ” “A. Compute the balance at the end of 10 years by tabulating the deposits, interest and balance for each year in a spreadsheet. That is, create a spreadsheet with three columns containing 1) the annual deposit, 2) interest earned for the preceding year, and 3) the balance for each year. Use a year 0 for the initial deposit which would be in the balance column. NOTE: The balance in the last row will be the future value of this annuity at the end of ten years.” Score (out of 10) 0 B. Use the Goal seek function to determine what payments (instead of $2,500) will be required for Ling to have $50,000 at the end of the 10 years using the same deposit and interest rate as initially stated. Score (out of 10) 0 C. Use the scenario manager to save the Original Values and the Goal Seek as two separate scenarios. Score (out of 10) 0 Problem 3 Problem Score 0 “Insert rows as necessary so you can place the answer for each part directly below the part of the problem you are answering. NOTE: Make sure the Problem Scoring cells still work!” “Ling is considering to open an annuity to save for a down payment on a home. The annuity would be created with an initial deposit of $1,000 (end of year 0). At the end of each of the following ten years, a payment of $2,500 would be made into the annuity. The interest rate is 2.5% compounded annually. Do not create new What-If values – instead, use the What-Ifs you already created for Problem 2. Hint: Use the names of the cells in your formulas. NOTE: You may have to reset the values back to their original values.” A. Use the Excel FV function to calculate the future value in ten years for this situation. You should get the same answer as the tabulation of Problem 2. Score (out of 10) 0 B. Use the Excel PMT function to determine the payment required each year (instead of $2,500) to achieve $50,000 in ten years using the same deposit and interest rate as initially stated. Score (out of 10) 0 C. Use the Excel PV function to determine the upfront deposit (instead of $1,000) that Ling would need to achieve $50,000 in 10 years. Use the original payment and interest rate. Score (out of 10) 0 Excel for Accounting.xlsx Fall 2013 Name General te

Answer

35