Question: The required input values for use in forecasting values is provided at the top of the Excel file. Be sure to reference cells and show

The required input values for use in forecasting values is provided at the top of the Excel file. Be sure to reference cells and show all calculations and not simply type values into the yellow boxes. Note that all values are in millions of dollars.

Part A: Sales Revenues

Assume that net sales in 2021 equal to $5,000 for MicroDrive. Forecast the net sales in years 2022 to 2028 using the growth rates provided. Use the Excel FV function to calculate the net sales. For example, type "=FV(Growth rate cell reference, 1,0,-Net sales cell reference)".

Part B: Operating Assets and Operating Liabilities

Fill in the table for operating assets and operating liabilities by referencing the required ratio and net sales.

Part C: Operating Income

Fill in the table for COGS, Depreciation, and other operating expenses by referencing the required ratios. Calculate the following by referencing cells:

EBIT = Net Sales - COGS - Depreciation - Other Operating Expenses

NOPAT = EBIT * (1-Tax Rate)

Part D: Free Cash Flows

Calculate the following by referencing cells:

Operating Current Assets = Cash + Accounts Receivables + Inventories

Operating Current Liabilities = Accounts Payable + Accruals

Net Operating Working Capital = Operating Current Assets - Operating Current Liabilities

Total Net Operating Capital = Net PP&E + Net Operating Working Capital

? Net Operating Capital = Total Net Operating Capital this year - Total Net Operating Capital last year

Free Cash Flow = NOPAT - ? Net Operating Capital

Part E: Value of Operations

Calculate the following by referencing cells:

A 1 Project 2 - Corporate Valuation 2 3 Year 4 Sales

Growth Rate 5 (COGS)/Sales 6 Depreciation/(Net PP&E) 7 (Other Operating Expenses)/Sales 8

Cash/Sales 9 (Accounts Receivables)/Sales 10 Inventory/Sales 11 (Net PP&E)/Sales 12 (Accounts Payable)/Sales

13 Accruals/Sales 14 Tax Rate 15 16 Part A: Sales Revenues 17

18 Year 19 Net sales 20 B 2021 $5,000.00 Name 2022 10.0%

A 1 Project 2 - Corporate Valuation 2 3 Year 4 Sales Growth Rate 5 (COGS)/Sales 6 Depreciation/(Net PP&E) 7 (Other Operating Expenses)/Sales 8 Cash/Sales 9 (Accounts Receivables)/Sales 10 Inventory/Sales 11 (Net PP&E)/Sales 12 (Accounts Payable)/Sales 13 Accruals/Sales 14 Tax Rate 15 16 Part A: Sales Revenues 17 18 Year 19 Net sales 20 B 2021 $5,000.00 Name 2022 10.0% 78.0% 10.0% 10.0% 2.0% 10.0% 20.0% 40.0% 4.0% 8.0% 25.0% 2022 D E 2023 F 2023 2024 2025 2026 8.0% 7.0% 6.0% 5.0% 78.0% 78.0% 78.0% 78.0% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% 10.0% 2.0% 2.0% 2.0% 2.0% 10.0% 10.0% 10.0% 10.0% 20.0% 20.0% 20.0% 20.0% 40.0% 40.0% 40.0% 40.0% 4.0% 4.0% 4.0% 4.0% 8.0% 8.0% 8.0% 8.0% 25.0% 25.0% 25.0% 25.0% 2024 G 2025 2026 H 2027 4.0% 78.0% 78.0% 10.0% 10.0% 10.0% 2.0% 10.0% 10.0% 20.0% 20.0% 40.0% 40.0% 4.0% 4.0% 8.0% 8.0% 25.0% 25.0% 10.0% 2.0% 2028 4.0% 2027 2028 J K

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

Here are the calculations for Parts A through D with details ... View full answer

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!

BACKGROUND You are an information analyst working for NEE. The company president has asked you to prepare a Quantitative analysis of financial, sales, and operations data to help determine which...

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

The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-5. Ivan's grandfather died and left a portfolio of municipal bonds. In 2012, they pay Ivan...

Can standing waves be formed of transverse waves, longitudinal waves, or both?

Explain one possible advantage of using a paired sample instead of independent samples.

In a stock acquisition accounted for by the equity method, a portion of the purchase price often is attributed to goodwill or to specific assets or liabilities. How are these amounts determined at...

Prove Lemma 9.3. Hint: Let x = y+. Note that f (t) = t is a concave function when 0 1, and use the fact that for a concave function f (t + h) f (t) is decreasing in t.

Prepare a statement of cash flows for Abrahams Manufacturing Company for the year ended December 31, 2012. Interpret your results. Abrahams Manufacturing Company Income Statement for the Year Ended...

Journalize the following merchandise transactions: a. Sold merchandise on account, $9,100 with terms 1/10,n/30. The cost of the merchandise sold was $5,460. b. Recelved payment less the discount....

An engineer has a DAQ with a 14-bit A/D converter and a voltage range of 0 to 10 V. They are measuring vibrations around 20 Hz, so they sample at f= 100 Hz to avoid aliasing. They add a DC offset of...

1. Marcos: Si te gusta la pesca, enter answer (mudarse) al campo. enter answer (Encontrar) un rancho cerca de un lago y enter answer (disfrutar) de tu pasatiempo. 2. Maricela: Si prefieres la ciudad,...

TravThe travel agency is planning a trip to England for 1 0 0 members of a college faculty. The plane fare is $ 5 0 0 within an additional charge of $ 1 0 for each cancellation. What is the number of...

Name a manager that works in the government or public sector: the management issues they face, how they deal with those issues, what theory and tools they use to help them in dealing with them

The lesson begins by saying how work conflict is different than personal conflict. Why do you think conflict at work is often more difficult to resolve? Have you ever had a conflict with someone at...

6. Maintaining inventory accuracy must be an integral part of the attitude of the organization. They must be detected so inventory accuracy is maintained. 8 points in total a) What are the two basic...

Derek was the director of the Southside Athletic Ciub when the COVID-19 pandemic hit. At the time the club had approximately Boo dues-paying members, The state ordered the club shut down, and the...

2 major changes to the Superannuation system announced in the 2021 Budget

Consider a game of poker being played with a standard 52-card deck (four suits, each of which has 13 different denominations of cards). At a certain point in the game, six cards have been exposed. Of...

Yolanda earns $112,000 in 2012. Calculate the FICA tax that must be paid by: Yolanda:.....................Soc.Sec..................$__________...

Lisa Sizemore, a taxpayer in the 10-15 percent tax bracket, purchased stock as an investment on July 11, 2011. She sold the stock on July 9, 2012, 2 days before qualifying for the long-term holding...

Sophie is a single taxpayer. For the first payroll period in October 2012, she is paid wages of $3,250 monthly. Sophie claims three allowances on her Form W-4. a. Use the percentage method to...

1.2 Define culture and explain the origins of human cultures.

3. How frequent are racial microaggressions? How would you conduct a survey to examine how frequently they occur in real life?

2. What kind of training would you provide to a person who was going to be moved to another country for work? Would your training program be limited to the employee, or include the employees family...