In the Module, download the Excel File called Pro Forma Cash Flow Template, which contains a template to complete the assignment. Using the PowerPoint example as a guide, complete the spreadsheet for 2017 based on the assumptions outlined below.

The theme park in our original PowerPoint example generated EBITDA of $40 million and did not incur any cash flow issues in 2016. In the assignment, the theme park fell on hard times and is projecting EBITDA of only $26 million for 2017. Based on the cash flow data presented below, develop a monthly pro forma income statement and cash flow budget for 2016 using the template provided. Students should identify the months where the cumulative deficit falls below $7 million, which is all the cash the theme park has.

Income Statement Data:

$26 million in EBITDA is distributed as follows:

January through April: 5% each month

May and June: 10% each month

July and August: 15% each month

September and October: 10% each month

November and December: 5% each month

Depreciation is $1 million per month

Interest Expense is $6 million per year

The combined state and federal tax rate is 35%

Timing Adjustments:

Annual interest expense consists of $3 million bond interest payments due on March 15 and September 15.

Annual licensing fees of $2.4 million are due on July 1

Annual liability insurance premium of $6 million is due on September 1

Quarterly real estate tax payments of $1.8 million are due on February 1, May 1, August 1, and November 1 ($7.2 million in total).

Investing and Financing Cash Flows:

$5 million bond principal repayments are due on February 1 and August 1 ($10 million for year).

$6 million in restaurant renovations are paid in equal monthly installments

Maintenance capital expenditures are $750,000 per month ($9 million for year).

The theme park would like to pay dividends of $2 million per quarter on March 15, June 15, September 15, and December 15 ($8 million in total).

THEME PARK

2016 PROJECTED MONTHLY CONDENSED CASH FLOW

($ in Millions)

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Total

Condensed Income Statement

EBITDA

-

Depreciation Expense

-

Interest Expense

-

Pre-Tax Income

-

-

-

-

-

-

-

-

-

-

-

-

-

Income Tax (Provision) Benefit

-

-

-

-

-

-

-

-

-

-

-

-

-

Net Income

-

-

-

-

-

-

-

-

-

-

-

-

-

Timing/Non-Cash Adjustments

Add Back Depreciation Expense

-

Interest Expense (Semi-Annual)

-

Licensing Fee (Annual)

-

Insurance (Annual)

-

Real Estate Taxes (Quarterly)

-

-

Operating Cash Flow

-

-

-

-

-

-

-

-

-

-

-

-

-

Investing/Financing Cash Flows

Bond Principal Repayment

-

Restaurant Renovations

-

Maintenance Capital Expenditures

-

Dividends

-

Monthly Cash Flow Surplus (Deficit)

-

-

-

-

-

-

-

-

-

-

-

-

-

Cumulative Surplus (Deficit)

-

-

-

-

-

-

-

-

-

-

-

-