PART 1: Using Excel, create a 5-year 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 5-year 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 "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. (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 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 21% of taxable income. 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 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 5-year 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 "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. (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 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 21% of taxable income. 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 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)
Based on the information provided I will help you create a 5year annual pro forma income statement f... View the full answer
Related Book For
Business Analytics Methods Models and Decisions
ISBN: 978-0321997821
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
-
According to an article in the Economist, "China's GDP grew by 7% in the second quarter, which was higher than had been expected." Is it likely that the article is referring to the change in nominal...
-
For a large supermarket chain in Florida, a womens group claimed that female employees were passed over for management training in favor of their male colleagues. The company denied this claim,...
-
The number of weekly breakdowns of a computer is a random variable having a Poisson distribution with \(\lambda=0.2\). What is the probability that the computer will operate without a breakdown for 3...
-
You are in charge of a new product launch. This will be a formal press launch, where the product is introduced by your managing director and the press and major customers have the opportunity to see...
-
(1) A borrower finds that the incremental cost of borrowing an extra $10,000 is 14%. If the borrower can earn 12% on alternative investments of comparable risk should he borrow the extra $10,000 at...
-
Pulsar Optics produces medical lasers for use in hospitals. The accounts and their balances appear in the ledger of Pulsar Optics on April 30 of the current year as follows: Preferred 1% Stock, $120...
-
There are some situations when a regression line is expected to go through the origin. The Linear regression model through the origin can be used in these situations which is defined as: Y = xi + i E...
-
A. Record the adjustments in the adjustments columns on the worksheet; then complete the worksheet. [26 points] Record the adjustments in the formatted journal entries at the bottom of the worksheet....
-
1. 2. Does compression steel yield at Mn (nominal moment)? Find Mn (design moment strength.
-
Explain: What does the picture tell? What is the motif? How does it affect you and the viewers? 10 AFP
-
For a two-tailed test with a sample size of 18 and a 0.20 levelof significance, the t value is _____.a. 1.333b. 0.863c. 1.328d. 1.7292 answers
-
A company's product requires 3 direct labor hours per unit at a standard rate of $40 per direct labor hour. During the period, the company made 8,010 units, using 23,825 direct labor hours at an...
-
Hello, I am beginner at GaBi software, I have the GaBi 9.2.1 Education software. I am doing life cycle assessment for the waste and I watch the video on GaBi page but still I need a-tutor to help me...
-
For the following exercises, find the inverse of the function and graph both the function and its inverse. f(x) = 4 x 2 , x 0
-
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...
-
Consider two alternatives, each of which will accomplish the same EPA-mandated pollution control. Using an incremental IRR analysis, determine the preferred alternative assuming MARR is 20...
-
The production department of Zunni's Manufacturing is considering two numerically controlled drill presses; one must be selected. Comparison data is shown in the table below. MARR is 10 percent/year....
-
Determine whether each of the following is true or false. In each case, assume \(P\) is located at \(t=0\) and \(F\) is located at \(t=n\), and the \(A\) s are spread uniformly over the planning...
Study smarter with the SolutionInn App