C
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!
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!