CS&E 1113 assignment 2

Download from Carmen: Piedmont.xlsx, Salary Assessment.xlsx

Credit Card Analysis.xlsx,Crystal Park Lake Expansion.xlsx

ALL BEGINNING FILES MUST BE DOWNLOADED FROM CARMEN OR THEY WILL NOT BE ACCEPTED.

(Unless otherwise noted.)

Part 1: PIEDMONT

Step 1: Open the file, Piedmont.xlsx and complete the spreadsheet rows 1-25 (Note: Rows 1-25 will not be populated in step 1.)

Set up the initial spreadsheet values for the Economic Feasibility Summary worksheet.

  • The assumed Discount rate (cell B5) will be 14%
  • The Recurring value of benefits for year 0 (cell B9) will be $0.
  • The Recurring value of benefits for years 1-5 will be the same and will be referenced from the Tangible Benefits worksheet.
  • Therefore, in cell C9 you will enter or use the point and click method to input the following: ='Tangible Benefit'!B13. Remember you will need a $ somewhere in this cell reference because you will copy it to cells C9:G9.
  • The Present value factor will start at 1; therefore you must input the number 1 in cell B10 and B17.
  • The One-Time Costs (cell B15) will be accessed from the One-Time Cost worksheet and will only occur at the beginning of the project. You will not need a $ in the reference because you will not be copying that reference.
  • The Recurring costs for year 0 (cell B16) will be $0. Make sure it is formatted as currency.
  • The Recurring Costs (cells C16:G16) should be referenced from the Recurring Cost worksheet.
  • Cell B12 will be the sum of B9 and B11.
  • The total for the Overall Net Present value is the total from column H.

Step 2: Complete from the book, Spreadsheet Programming for Business CSE 1113:

Case 1 (Piedmont Trailer Manufacturing Company)pages 367-370

Step 3: Continue working on the spreadsheet by completing the Cash Flow Analysis section on the top of page (371).(Stop before the “Information Specifications” section.)Ignore the instructions telling you to protect the worksheet.

  • Yearly NPV cash flow =Total present value of benefits + Present value of recurring cost
  • Overall NPV cash flow = Total Net value of all benefits + Net present value of all costs

Step 4: Read the Information Specifications Section and complete the following steps to complete the worksheet. (Stop before the “Test Your Design” section.)

  • The Internal Rate of Return in dollars (cell B27) for year 0 is the Net Present Value of All Benefits for year 0 + Net Present Value of All Costs.
  • The Internal Rate of Return in dollars (cells C27:G27) for years 1-5 will be the Recurring Value of Benefits + the Recurring Costs.
  • Use the following formula in cell H27 to calculate the Internal Rate of Return as a percentage
  • =IRR(B27:G27)
  • Make sure there are not green triangles displaying on your worksheet.

Step 5: Create a chart to display the break-even point of the project.

  • Create a new tab named Break Even Point
  • You will be using this worksheet to create a summation of the NPV of all benefits and costs so you can create your break even analysis chart.
  • Type in the following information in the Break Even Point Worksheet
  • Make sure to Center and merge cells A1:G1
  • Copy the NPV values from the Economic Feasibility Summary Worksheet to cells A3:G3
  • Notice the values in cells B4:G4 are multiplied by -1 in order to make both numbers positive. We need to do this so the chart will plot correctly. Be sure to format the cell as currency.
  • This worksheet doesn’t need much formatting because we are using it only to create our chart.


  • Highlight cells ‘Break Even Point’!A2:G4, click insertLine Click on the Line icon to create the following chart.

You can format each individual part of the chart by clicking on that object to select it and then right clicking to display the format options. Try to make the chart look as close to the one displayed at the end of this lab, then copy the chart from this worksheet and paste it to the bottom of the Economic Feasibility Summary worksheet.

  • In cell A29 and B29 type the projected break-even point. (Shown in the figure of the completed spreadsheet below.)


Step 6: Run Scenario Manager for the following: (Make sure to format as follows.)

Discount rate of 8%

Discount rate of 10%


Part 2: integrating complex functions into excel analysis

Complete from the book, Spreadsheet Programming for Business CSE 1113:

Chapter 4Statistical Functions, Pages 143-156

Do not use the starting file indicated in the book, instead use the file, Salary Assessment.xlsx you downloaded from Carmen. This will be the name of your file, so do not rename it as the book instructs. This will be true for all of your assignments.

Read: Pages 143-151
Do: Pages 152-156

Part 3: credit card Problem

The Credit Card Analysis spreadsheet lists various credit cards and basic information about these cards.

You will be filling in columns H through L. In addition Rows 10 & 11will have summary questions which you will be asked to solve.

In cell H2, write a formula which can be copied down the column to cells H3:H8, to determine (T/F) if the Credit card limit is greater than $5,000.

We don’t need to use AND/OR/NOT because we are only comparing two things. Instead you willuse a relational operator alone (>, <, >=, <=, =).

Basically we are asking: Is the Credit Card Limit > 5000 (T/F)

Now, convert this expression to excel syntax: = ____ > ____. Note: the number 5000 does not appear in any cell that can be used as comparison criteria so we must hard code the value 5000 in the relational expression.

Notice we do not need to use and IF statement because the cell will display a true/false already. The use of the IF statement would be redundant.

DON’T DO THIS =IF(Credit Card Limit > 5000, True, False)

In cell I2, write a formula which can be copied down the column to cells I3:I8, to determine (T/F) if the card has an acceptable APR. An acceptable APR is one that is between 11% and 20% or one that has an Annual Fee of $0.00. Note: Please use the "Highest APR" value for this calculation.

Because we are checking more than one thing we will need to use a Boolean function.

Here we are asking: Is the Credit Card between 11%-20% OR does it have an annual fee of $0.00.

The basic syntax will be: = OR(AND( ____ >= ____, ____ <= ____),APR = 0)

You plug in the arguments.

In cell J2, write a formula which can be copied down the column to cells J3:J8, to determine (T/F) if the card has the lowest APR of all the cards listed.Note: Pleaseuse the "Lowest APR" value.

Syntax: = ______<= MIN(____)

In cell K2, write a formula which can be copied down to cells K3:K8, to determine if the card’s credit limit is within 20% (inclusive) of the Average credit card limit.Display the word, “ACCEPTABLE” if it is, otherwise display the word, “NA”.

Now we need to use an IF statement because we are not displaying the words true or false in the cells.

=IF(AND( ___ >= AVERAGE(___:___) * 0.8 , ___ <= AVERAGE(___:___) * 1.2 ) , ___ , ___ )

In cell L2, write a formula which can be copied down the column to cells L3:L8, to determine (T/F) if the card is not a visa card. (You must use the not function to receive credit for this question.)

I know this is a redundant question, but I want you to practice using the not function.

In cell M2, write a formula which can be copied down the column to cells M3:M8, to determine (T/F) if the card does not have an introductory rate of 0%. (You must use the not function to receive credit for this question.)

Again: Redundant question but good practice.

Remember, never use = true or = false. This is already implied when testing cells for true or false values.

In cell E10, write a formula to determine (T/F) if all the cards have an introductory rate of 0%.

In cell E11, write a formula to determine (T/F) if any of the cards have an introductory rate of 0%

Part 4: Crystal park lake expansion

Complete from the book, Problem Solving Cases in Microsoft Access and Excel, Case 6, Crystal Park Lake Expansion pages 124-132.

  • Stop before Assignment 2.

Do not use the starting file indicated in the book, instead use the file, Crystal Park Lake Expansion.xlsx you downloaded from Carmen.

CarmenSubmissions

  1. Piedmont.xlsx
  2. Salary Assessment.xlsx
  3. Credit Card Analysis.xlsx
  4. Crystal Park Lake Expansion.xlsx

1