3. Use your full income statement from Part A and adjust it to determine the after-tax operating cash flow for the project for 5 years.
3. Use your full income statement from Part A and adjust it to determine the after-tax operating cash flow for the project for 5 years. You must use Excel to answer this question and show your formulas.
Net working capital is needed for any hospitality operation. If you are unsure of what net working capital is, feel free to peruse Chapter 3 in our 420 text. The following table indicates the net working capital needs under each option.
| Year | Year 1 | Year 2 |
| Net Working Capital Needed | $100,000 | $200,000 |
You are to assume that working capital is to remain the same from year 2 onward. Assume that working capital needs are for all "other current assets," not including cash. Current liabilities will be $100,000 in year 1 and $200,000 from year two onward for the project.
Financing Conditions
| Long-term Debt to fixed asset ratio | Interest Rate | Dividend Payout Ratio |
| 70% | 7% | 10% |
It is important to note that after the initial investment of capital, no new equity capital or long-term debt will be raised. Management also assumes that the total amount of long-term debt will be in the form of bonds so that the book value of the debt will be constant over the 5-year period (i.e., no principal repayments will be made). All remaining earnings will be put back into the business. No additional investment will be made and all excess funds will be held as cash.
Income State ment for the year of 2019-2023 2019 2020 2022 2023 Revenue Room revenue Food and Beverage revenue other income 10,347,750.00 $ 10,894,520.00 11,606,649.60 $ 12,070,915.58 $12,554,959.30 $ 2,069,550.00 $ 2,178,904.00$ 2,321,329.92$2,414,183.12 2,510,991.86 10,000.00 10,300.00 $ 10,609.00 $10,927.27 11,255.09 12,427,300.00 $ 13,083,724.00 13,938,588.52 $ 14,496,025.97 $15,077,206.25 all Revenues Expenses Department Expen Rooms Food and Beverage Undistributed Operating Expenses ses $ 2,276,505.00 $ 2,396,794.40$ 2,553,462.91 $2,655,601.43 2,762,091.05 $1,448,685.00 $1,525,232.80 1,624,930.94 $ 1,689,928.18 $ 1,757,694.30 $ 869,911.00$ 785,023.44 $836,315.31 $869,761.56 904,632.37 min & General Marketing Franchise Fee Energy Expense Property Maintenance Total Expenses Income Before Fixed Charges $5,678,467.00 $ 5,992,514.21 6,406,548.39 6,669,313.27 $ 6,943,517.75 621,365.00 640,005.95 $ 659,206.13 $ 678,982.31 699,351.78 413,910.00 435,780.80 464,265.98 $ 482,836.62 502,198.37 621,365.00 $ 654,186.20 $ 696,929.43 $724,801.30 $753,860.31 497,092.00 654,186.20 696,929.43 $ 724,801.30 753,860.31 $6,748,833.00 $ 7,091,209.79 $ 7,532,040.13 7,826,712.70 $ 8,133,688.50 Fixed Charges Real Estate Taxes Property Insurance Interest Expense Management Fees Depreciation Expense Income before Income Taxes$ 4,790,232.44 $ 5,076,758.83 $ 5,456,204.36 $ 5,694,765.80 Income Taxes Net Income $ 248,546.00 $ 253,516.92 258,587.26 $ 263,759.00$269,034.18 248,546.00 $ 261,674.48 278,771.77$ 289,920.52 301,544.12 161,927.30 161,927.30 161,927.30 $ 161,927.30 161,927.30 $ 170,354.01 $ 179,775.43 192,196.45 $ 200,079.40$ 208,305.53 58,861.25 $ 58,861.25 $ 58,861.25 $ 58,861.25 58,861.25 5,943,845.36 $ 958,046.49 $ 1,015,351.77 $ 1,091,240.87 $ 1,138,953.16 $ 1,188,769.07 $3,832,185.95 $ 4,061,407.07 $ 4,364,963.49 4,555,812.64 $ 4,755,076.29 Income State ment for the year of 2019-2023 2019 2020 2022 2023 Revenue Room revenue Food and Beverage revenue other income 10,347,750.00 $ 10,894,520.00 11,606,649.60 $ 12,070,915.58 $12,554,959.30 $ 2,069,550.00 $ 2,178,904.00$ 2,321,329.92$2,414,183.12 2,510,991.86 10,000.00 10,300.00 $ 10,609.00 $10,927.27 11,255.09 12,427,300.00 $ 13,083,724.00 13,938,588.52 $ 14,496,025.97 $15,077,206.25 all Revenues Expenses Department Expen Rooms Food and Beverage Undistributed Operating Expenses ses $ 2,276,505.00 $ 2,396,794.40$ 2,553,462.91 $2,655,601.43 2,762,091.05 $1,448,685.00 $1,525,232.80 1,624,930.94 $ 1,689,928.18 $ 1,757,694.30 $ 869,911.00$ 785,023.44 $836,315.31 $869,761.56 904,632.37 min & General Marketing Franchise Fee Energy Expense Property Maintenance Total Expenses Income Before Fixed Charges $5,678,467.00 $ 5,992,514.21 6,406,548.39 6,669,313.27 $ 6,943,517.75 621,365.00 640,005.95 $ 659,206.13 $ 678,982.31 699,351.78 413,910.00 435,780.80 464,265.98 $ 482,836.62 502,198.37 621,365.00 $ 654,186.20 $ 696,929.43 $724,801.30 $753,860.31 497,092.00 654,186.20 696,929.43 $ 724,801.30 753,860.31 $6,748,833.00 $ 7,091,209.79 $ 7,532,040.13 7,826,712.70 $ 8,133,688.50 Fixed Charges Real Estate Taxes Property Insurance Interest Expense Management Fees Depreciation Expense Income before Income Taxes$ 4,790,232.44 $ 5,076,758.83 $ 5,456,204.36 $ 5,694,765.80 Income Taxes Net Income $ 248,546.00 $ 253,516.92 258,587.26 $ 263,759.00$269,034.18 248,546.00 $ 261,674.48 278,771.77$ 289,920.52 301,544.12 161,927.30 161,927.30 161,927.30 $ 161,927.30 161,927.30 $ 170,354.01 $ 179,775.43 192,196.45 $ 200,079.40$ 208,305.53 58,861.25 $ 58,861.25 $ 58,861.25 $ 58,861.25 58,861.25 5,943,845.36 $ 958,046.49 $ 1,015,351.77 $ 1,091,240.87 $ 1,138,953.16 $ 1,188,769.07 $3,832,185.95 $ 4,061,407.07 $ 4,364,963.49 4,555,812.64 $ 4,755,076.29
