named ranges

[Carol Ann]

Good Afternoon:

I am taking an Excel course and this is one of my assignments. I have used
Excel for years but never used named ranges. I have checked online and know
how to select a range and name it, but in this assignment I don't know which
cells to select to name. Can someone help?

Assignment ... Create a workbook consisting of 2 worksheets. Name the first
worksheet tab “Assumptions†and the second worksheet “P&L.â€

The “Assumptions†worksheet ...

A B C
D E F
1 2002 2003 2004 2005 2006
2 Revenue Growth (%) 2% 4% 3% 5%
3 Expense Growth (%) 3% 3% 2% 5%
4 Tax Rate (%) 16% 15% 12% 13%


The “P&L†worksheet ...

A B C D
E F
1 2002 2003 2004 2005 2006
2 Revenue $1,000
3 Expenses $550
4 Gross Profit
5 Taxes $180
6 Net Profit

• Create names for data ranges in both worksheets, using row the labels
(hint: select rows and their labels, then Insert  Name  Create…).
• In the Assumptions worksheet, add values: values for 2002 can be left
blank; assumptions for other years can be any reasonable values you want
(e.g. 2%, 10%, 40%).
• In the P&L worksheet, use numeric values for year 2002 as shown.
• Then, enter formulas as follows:
• Revenue: previous year’s revenue multiplied by current year’s revenue
growth.
• Expenses: previous year’s expenses multiplied by current year’s expense
growth (analogous to Revenue formula).
• Gross Profit: Revenue minus Expenses.
• Taxes: Gross Profit multiplied by Tax Rate.
• Net Profit: Gross Profit minus Taxes.
Hints:
ï¶Check that Excel is set to “Accept labels in formulas†– found in Tools 
Options
ï¶Try writing the formula for Revenue in the year 2003 as “= Revenue
2002*(1+Revenue_Growth)â€, leaving a space between Revenue and 2002 and Excel
should do the right thing. You can then copy this formula by clicking the
fill handle and dragging to remaining cells in the row and Excel will
automatically adjust the formula for the correct year.

THANK YOU!

 

[Alan Beban]

Good Afternoon:

I am taking an Excel course and this is one of my assignments. I have used
Excel for years but never used named ranges. I have checked online and know
how to select a range and name it, but in this assignment I don't know which
cells to select to name. Can someone help?

Assignment ... Create a workbook consisting of 2 worksheets. Name the first
worksheet tab “Assumptions†and the second worksheet “P&L.â€

The “Assumptions†worksheet ...

A B C
D E F
1 2002 2003 2004 2005 2006
2 Revenue Growth (%) 2% 4% 3% 5%
3 Expense Growth (%) 3% 3% 2% 5%
4 Tax Rate (%) 16% 15% 12% 13%
. . .
• Create names for data ranges in both worksheets, using row the labels
(hint: select rows and their labels, then Insert  Name  Create…).

E.g., Select $A$2:$F$2, then Insert|Name|Create and pick a name. Do you
need more?

Alan Beban

 

[Carol Ann]

Afraid that I already knew to do that. I'm supposed to use the formula
"=Revenues 2002*(1+Revenue_Growth)" to show the increase from 2002 to 2003.
Each time I try I get "the formula you typed contains an error".

 

[Alan Beban]

Afraid that I already knew to do that. I'm supposed to use the formula
"=Revenues 2002*(1+Revenue_Growth)" to show the increase from 2002 to 2003.
Each time I try I get "the formula you typed contains an error".

Sorry; I thought I was responding to
"but in this assignment I don't know which
cells to select to name. Can someone help?"

Alan Beban

 

[Pete_UK]

You can't have a space in a name, so Excel has probably named the
range "Revenues_2002" (depends on how you defined it), and this does
not tie in with what you have typed in your formula.

Hope this helps.

Pete