Question

PART 1: Using Excel, create a 5-year annual pro forma income statement for the newly formed Ranger Corporation based on the information provided below. Be sure to place all key assumptions at the top of your spreadsheet (not within the statement), so that you can perform a "what-if" analysis based on changing the base assumptions. In the event that you choose not to use the template included in this workbook (I have provided a "template you can use for this problem if you so wish - it is the next spreadsheet in this workbook), be sure that your income statement is properly formatted as a multi-step income statement (include line entries for Gross Profit, EBITDA, NOI, EBIT, EBT, and Net Income). Include a 6th column in your spreadsheet that shows the totals for all line items in your income statement. (65 POINTS) Key information to be used for developing the budget: Revenues for the first year of operation are projected to be $2,500,000. These revenues are expected to increase by 7% each year thereafter. The Cost of Goods sold is estimated to be 54% of the revenue figure per year. Operating Expenses for the first year include the following: Administrative Costs: $50,000 Rent: $150,000 Repairs/Maintenance: $75,000 Utility Expenses: $24,000 Wage Expense: $468,000 After the first year, all of the operating expenses are expected to increase by 4% annually Depreciation expense is fixed for the entire five-year projected operating period at $100,000 per year. Other income includes Interest Income totaling $5,000 per year for all five years. Other expenses include inventory loss estimated at 1% of revenues. The company will have a $2,000,000 loan to help finance its operations. This loan is an interest-only loan (no principal will be paid for the entire five-year operating projection period). The loan carries an annual interest rate of 8.00%. This loan will represent the only source of interest expense for the company. Tax expense is estimated at 28% of taxable income. After you have completed this pro forma Income Statement, save it for submission to me for grading and then make a copy of this spreadsheet as another sheet in this workbook - call this new worksheet "Pro Forma Modifications". It is now time to test how well you set your spreadsheet up by changing a few assumptions (see Part 2 below).

PART 2: Now it is time to test the "what-if" capabilities of your pro forma spreadsheet. Assume now that your revenues will grow by 10% annually but your first year revenues will only be $2,100,000. Additionally, assume that your Cost of Goods is 49% of revenues and your tax rate is 34%. (15 POINTS) After you have modified these assumptions above for Part 2, save your "Pro Forma Modifications" spreadsheet.

PART 3: Now go back to the original Pro Forma Income Statement you developed in Part 1. From the information you have assembled and presented in your Pro Forma statement, please calculate the estimated Free Cash Flow for Ranger Corporation for Year 3. You can present your calculation of this Free Cash Flow for Year 3 on either a separate spreadsheet or directly on your Part 1 Pro forma Income Statement spreadsheet. (20 POINTS)

Answer 1

Pls find below the steps, workings on Proforma Income Statement, then further with modifications and the year 3 free cash flow; Year 3 free cash flow as highlighted in yellow;