PART 1: Using Excel, create a 5year annual pro forma income statement for the newly formed...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
PART 1: Using Excel, create a 5year annual pro forma income statement for the newly formed Ranger Corporation (sorry  not very creativell) 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 "whatif" 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 multistep 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. (85 POINTS) Key information to be used for developing the budget: Revenues for the first year of operation are projected to be $3,500,000. These revenues are expected to increase by 9% each year thereafter. The Cost of Goods sold is estimated to be 45% of the revenue figure per year. Operating Expenses for the first year include the following Administrative Costs: $50,000 Rent: $150,000 $75,000 $24,000 $468,000 Repairs/Maintenance: Utility Expenses: Wage Expense: After the first year, all of the operating expenses are expected to increase by 5% annually Depreciation expense is fixed for the entire fiveyear 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 interestonly loan (no principal will be paid for the entire fiveyear 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 21% of taxable income. PART 2: Now it is time to test the "whatif capabilities of your pro forma spreadsheet. Assume now that your revenues will grow by 12% annually but your first year revenues will only be $2,500,000. Additionally, assume that your Cost of Goods is 49% of revenues and your tax rate is 35%. (20 9 POINTS) 0 1 32 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 all five years. You can present your calculation of these Free Cash Flows on either a separate spreadsheet or directly on your Part 1 Pro forma Income Statement spreadsheet. Once you have calculated these cash flows, determine the 33 present value of these cash flows if the company's cost of capital is 7.5% (25 POINTS) 34 PART 1: Using Excel, create a 5year annual pro forma income statement for the newly formed Ranger Corporation (sorry  not very creativell) 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 "whatif" 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 multistep 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. (85 POINTS) Key information to be used for developing the budget: Revenues for the first year of operation are projected to be $3,500,000. These revenues are expected to increase by 9% each year thereafter. The Cost of Goods sold is estimated to be 45% of the revenue figure per year. Operating Expenses for the first year include the following Administrative Costs: $50,000 Rent: $150,000 $75,000 $24,000 $468,000 Repairs/Maintenance: Utility Expenses: Wage Expense: After the first year, all of the operating expenses are expected to increase by 5% annually Depreciation expense is fixed for the entire fiveyear 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 interestonly loan (no principal will be paid for the entire fiveyear 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 21% of taxable income. PART 2: Now it is time to test the "whatif capabilities of your pro forma spreadsheet. Assume now that your revenues will grow by 12% annually but your first year revenues will only be $2,500,000. Additionally, assume that your Cost of Goods is 49% of revenues and your tax rate is 35%. (20 9 POINTS) 0 1 32 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 all five years. You can present your calculation of these Free Cash Flows on either a separate spreadsheet or directly on your Part 1 Pro forma Income Statement spreadsheet. Once you have calculated these cash flows, determine the 33 present value of these cash flows if the company's cost of capital is 7.5% (25 POINTS) 34
Expert Answer:
Answer rating: 100% (QA)
Here are the detailed workings for Parts 13 Part 1 Base Pro Forma Income Statement Year 1 Year 2 Year 3 Year 4 Year 5 Total Revenues 3500000 3815000 4... View the full answer
Related Book For
Business Analytics Methods Models and Decisions
ISBN: 9780321997821
2nd edition
Authors: James R. Evans
Posted Date:
Students also viewed these accounting questions

Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...

List three specific parts of the Case Guide, Objectives and Strategy Section (See below) that you had the most difficulty understanding. Describe your current understanding of these parts. Provide...

1. Based on the forecasts above, use the spreadsheet below to construct a pro forma income statement for Idexo over the next five years. What is the annual growth rate of the firm's net income over...

Use the graphs of f and g to solve Exercises 8390. y = g(x) HH y .y = f(x) # X

Use a graphing utility to graph f and g in the same viewing window and determine which is increasing at the greater rate as x approaches + . What can you conclude about the rate of growth of the...

Propose a structure for a compound with molecular formula C 4 H 8 O that exhibits the following 13 C NMR and FTIR spectra. Carbon NMR 67.7 25.4 80 30 20 100 90 70 60 40 10 Chemical shift (ppm) 100...

Consider the following cash flow profile, and assume MARR is 10 percent/year. a. What does Descartes' rule of signs tell us about the IRR(s) of this project? b. What does Norstrom's criterion tell us...

The cash register tape for Bluestem Industries reported sales of $6,871.50. Record the journal entry that would be necessary for each of the following situations. (a) Cash to be accounted for exceeds...

Please write the SQL query in Oracle as followed: The database can be downloaded via: https://www.oracletutorial.com/wpcontent/uploads/2019/01/oraclesampledatabase.zip For more information,...

Suppose you take out a margin loan for $75,000. You pay a 6.4 percent effective rate. If you repay the loan in two months, how much interest will you pay?

Design a combinational circuit that has three inputs x, y, and z. The circuit has two outputs F1 and F2 where: a The output F1 is one when the input is less than or equal to 2, The output F1 is zero...

The following financial information is available for Hero Sports Ltd.: Sales : $45,000 Costs : $25,000 Addition to retained earnings : $5,500 Dividends paid : $900 Interest expense : $1,450 Tax rate...

eBook Assume that the riskfree rate is 5% and the required return on the market is 13 %. What is the required rate of return on a stock with a beta of 27 Round your answer to two decimal places.

answer the following questions based on the article in complete sentences,...

Your company has arranged a revolving credit agreement for up to $78 million at an interest rate of 1.47 percent per quarter. The agreement also requires your company to maintain a compensating...

Defined Contribution (D.C) Pension Plans require: the employer contribute a specific amount to the DC plan each year an actuary complete a valuation of the plan every 3 years the employer guarantee...

What is the name of the following structure in the figure?

Create an appropriate display of the navel data collected in Exercise 25 of Section 3.1. Discuss any special properties of this distribution. Exercise 25 The navel ratio is defined to be a persons...

Create a PivotTable for the data in the Excel file Weddings to analyze the wedding cost by type of payor and value rating. What conclusions do you reach?

Malloy Milling grinds calcined alumina to a standard granular size. The mill produces two different size products from the same raw material. Regular Grind can be produced at a rate of 10,000 pounds...

"Use the Excel file Banking Data. a. Construct a box plot for the Median Income, Median Home Value, Median Household Wealth, and Average Bank Balance. b. What observations can you make about these...

Over what ranges of taxable income in 2017 will the total income tax liability for two persons with equal incomes who file as single individuals equal their income tax liability if they file jointly...

If a taxpayer has $40,000 of employee salary in 2017, how much will be withheld for the Social Security and Medicare taxes?

Sally and Jim are married and have taxable income in 2017 of $160,000. If they could file their income tax as single individuals, each of them would have taxable income of $80,000. Do they have a...
Study smarter with the SolutionInn App