Question

1. Go to the Profit & Loss worksheet. Elena has entered most of the income and expense data on the worksheet. She estimates revenue will be $825,000 in Year 1 and $1,400,000 in Year 5 of the shuttle service. She needs to calculate revenue for Years 2–4. Revenue should increase at a constant amount from year to year. Project the revenue for Years 2–4 (cells C7:E7) using a Linear Trend interpolation.
2. Elena also needs to calculate expenses for payroll and rent for Years 2–5. She knows the starting amount for each expense, and estimates the rent in Year 5 will be $64,000. She expects the payroll expenses to increase by at least 6 percent per year and the rent to increase by a constant rate. Project the expenses for Payroll in Years 2–5 (cells C13:F13) using a Growth Trend extrapolation. Use 1.06 (a 6 percent increase) as the step value. Project the expenses for Rent in Years 2–4 (cells C14:E14) using a Growth Trend interpolation.

Answer 1

In the Linear trend, the items that we are projecting grow by a fixed amount each year

1. Select cell B7 to F7
2. Go to Editing in the Home tab and click on the down arrow of the Fill button
3. Click on series
4. Make sure "Series" in is selected as "Rows"
5. Type is selected as "Linear"
6. check the "Trend" box
7. Click OK

In the Growth trend, the items that we are projecting grows by a fixed percentage each year

For Payroll Row

1. Select cell B13 to F13
2. Go to Editing in the Home tab and click on the down arrow of the Fill button
3. Click on series
4. Make sure "Series" in is selected as "Rows"
5. Type is selected as "Growth"
6. DO NOT CHECK the "Trend" box
7. Enter 1.06 in "Step Value"
8. Click OK

For Rent Row

1. select cell B14 to F14
2. Go to Editing in the Home tab and click on the down arrow of the Fill button
3. Click on series
4. Make sure "Series" in is selected as "Rows"
5. Type is selected as "Growth"
6. check the "Trend" box
7. Click OK

The Final Table is as follows :

Profit & Loss Statement
Percent cost of marketing	25%
Percent cost of R&D	7.25%
Tax rate	33%
Income	Year 1	Year 2	Year 3	Year 4	Year 5
Revenue	825000	968750	1112500	1256250	1400000
Cost of marketing	206250				350000
Cost of R&D	59813				101500
Gross profit	558937	968750	1112500	1256250	948500
Expenses	Year 1	Year 2	Year 3	Year 4	Year 5
Payroll	588000	623280	660676.8	700317.4	742336.5
Rent	60000	60975.93	61967.73	62975.67	64000
Insurance	30000	30000	32000	32000	34000
Miscellaneous	15000	15000	15000	15000	15000
Total expenses	693000	729255.9	769644.5	810293.1	855336.5