The required input values for use in forecasting values is provided at the top of the Excel
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 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:
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill