Question

In: Finance

PART 1: Using Excel, create a 5-year annual pro forma income statement for the newly formed...

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 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.

Solutions

Expert Solution

Based on the given data, pls find below workings on the Proforma Income Statement


Related Solutions

PART 1: Using Excel, create a 5-year annual pro forma income statement for the newly formed...
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...
prepare a 3-year pro forma income statement and pro forma balance sheet, including expected cash flows...
prepare a 3-year pro forma income statement and pro forma balance sheet, including expected cash flows and all associated assumptions. Company: Bishrom (Nepali eyewear brand) outsources all the manufacturing in china. Please assume all the data. you can make a fake statement. Subject: Entrepreneurial finance
Pro Forma Income Statement (Version 1) Ranger Company Assumptions: Revenues for the first year of operation...
Pro Forma Income Statement (Version 1) Ranger Company Assumptions: 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...
Prepare a pro-forma statement by using assumptions to calculate the annual operating net cash flow for...
Prepare a pro-forma statement by using assumptions to calculate the annual operating net cash flow for three years to calculate the the NPV and IRR. Assess project feasibility using NPV and IRR method. Company is Z-energy For the data part you can refer to the Yahoo finance or assume any relevant data according to the company
Prepare a pro-forma statement by using assumptions to calculate the annual operating net cash flow for...
Prepare a pro-forma statement by using assumptions to calculate the annual operating net cash flow for three years to calculate the the NPV and IRR. Assess project feasibility using NPV and IRR method. Company is Z-energy For the data part you can refer to the Yahoo finance or assume any relevant data according to the company
Prepare a pro forma income statement and balance sheet for the 'coming year' for Netflix (NFLX)
Prepare a pro forma income statement and balance sheet for the 'coming year' for Netflix (NFLX)
Capital Budgeting Risk Analysis Project Instruction Create the pro forma income statement and estimate the cash...
Capital Budgeting Risk Analysis Project Instruction Create the pro forma income statement and estimate the cash flows for the following project. Decide whether to accept this project based on analysis of the NPV, Profitability Index, and IRR. Project Assumptions (Base Case) Equipment Life 6 Years Initial Equipment Cost $2,500,000 in year 0 Depreciation Straight Line Method Initial Revenue $1,000,000 in year 1 Revenue growth rate year 2 10% Revenue growth rate year 3 15% Revenue growth rate year 4 10%...
Pro forma income statement. Given the income statement in the popup​ window, for California Cement Company...
Pro forma income statement. Given the income statement in the popup​ window, for California Cement Company for 2013 and an expected sales growth rate of 6.61 % for​ 2014, prepare a pro forma income statement for 2014. First, find the percentage of each income statement line from 2013 as a percent of sales. ​(Round to three decimal​ places.) Sales Revenue $22,811,000 ____ % Cost of goods sold $-11,638,000 ___% Selling, general, and administrative expenses $-3,973,000 ____% Depreciation expenses $ -1,369,000...
TRUE or FALSE 1) On a pro forma income statement, the value we enter for sales...
TRUE or FALSE 1) On a pro forma income statement, the value we enter for sales revenue is normally derived from our forecast.       2) One does not need to determine if the operating expenses are in line with industry averages. 3) Start-up expenses are those expenses that will be incurred by the business one time.   
Chapter 4: 3. Fire Corp financial statements: Pro forma income statement Pro forma balance sheet Sales...
Chapter 4: 3. Fire Corp financial statements: Pro forma income statement Pro forma balance sheet Sales $      32,000 Assets $25,300 Debt $        5,800 Costs $        24,400 ________ Equity $        19,500 Net income $        7,600 Total $25,300 Total $      25,300 It expects 15% sales increase. It also predicts every item on the balance sheet will increase by 15% as well. 1.Create the pro forma statements. 2. What’s the plug variable here? 3. If Fire Corp pays half of income as dividend,...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT