PROJECT 2

profilePuncho
project2.odt
Home>Architecture and Design homework help>PROJECT 2

Your Profit Analysis worksheet should look something like this:

 

Step 15 says to copy three headings. You should have something like:

You may want to double click the line shown in the red circle to set the column widths.  There are other ways to set the column width, but I’ve covered those in a recent posting.

 

Now add the two labels: Hybrid Expense per Car and Non-Hybrid Expense per Car.  Do not reset the column width for these labels.

 

Add the Expense values for Hybrid and Non-Hybrid cars.  We will be using these in our formula.

 We need a formula in order to calculate the expense for each car. The expense is different for Hybrid cars than it is for all other cars, so if the row is for a “Hybrid” car, we use $100, otherwise we use $50 and multiple that expense times the number of cars.  This is a perfect place to use the “IF” formula.  If CarClass in the current row equals “Hybrid” then use $100, otherwise use $50 and multiple that times the NumCars value.  So, in Excel we would have:

=IF([@CarClass]="Hybrid",100,50)*[@NumCars]

But what if the value for the expense changes?  We will need to update each formula. Excel allows us to reference another cell and use the value in that cell in our formula.  In our example we can use the values we just entered in Cells O1 and O2.  Our formula would look like this:

=IF([@CarClass]="Hybrid",O1,O2)*[@NumCars]

However, if we use this formula, as we copy the formula down to the next row, it will change the referenced cells relative to the way the formula was copied.  It is called Relative Reference. If you copy down two rows, the copied formula will be:

=IF([@CarClass]="Hybrid",O3,O4)*[@NumCars]

Since there is nothing in cells O3 of O4 you will calculate the wrong answer.

Similarly, if you were to copy this formula two columns over, the formula would look like this:

=IF([@CarClass]="Hybrid",R1,R2)*[@NumCars]

and again, you would calculate the wrong answer.  Even if there was data in these cells, it would not be the values you expected. 

So how do we fixt that?  The answer is to use Absolute Reference, which is Excel lingo to mean the cell reverenced doesn’t change even when copied to a different location.  To use Absolute reverence, put a $ in front of either the Column indicator, the Row indicator or both. For example, If I had the following data in cells A1, A2, B1 and B2 and a formula =A2*B2 as shown:

 

If I copy the formula with Relative Reference I would have:

However, if I use Absolute Reference I will have:

 

I can also only fix either the column or the row.  In the example below, I fixed the column for the first cell in the formula and the row for the second:

In this way you can control what references move when you copy a formula.

Back to your Profit Analysis worksheet.  We want to use absolute reverence for the Expense values. Our Formula would look like this:

=IF([@CarClass]="Hybrid",$O$1,$O$2)*[@NumCars]

If you enter the cell coordinates and press F4 it will automatically add the dollar signs.  Pressing F4 twice removes the column dollar sign, pressing it three times removes the row dollar sign and pressing four times removes all the dollar signs.  It will continue to cycle through adding and deleting dollar signs as you continue to press F4.

Your worksheet should have the formula looking like this:

 And your data should be like this:

Don’t forget to format the expenses as “Currency” with on decimal places.

 

Now that we have the expenses and we were given the revenue (what people paid) for each row, we can calculate the profit: (Revenue minus Expense):

 =[@Revenue]-[@Expenses]

Again, don’t forget to adjust the column width and format as “Currency” and no decimal places.

Profit Per Car is also an easy calculation:

=[@Profit]/[@NumCars]

Adjust the column width and format as “Currency” and no decimal places.

 

 

This should help give you a better understanding of how relative and absolute referencing work.