1. Enter the relevant values for the variables that you use in solving this problem in...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
1. Enter the relevant values for the variables that you use in solving this problem in Column K. Name each cell in Column K that contains a variable. Use the following names: Income, Quantity, Life, Price, Machinery, Maintenance Cost, DepLife, Salvage, Inventory, CashBalance, VariableCost, WACC, and TaxRate (e.g. define the name of cell K4 to be Quantity). When linking cells or writing functions, use these names to reference the cells in your calculations. This makes it easier for others to know what the functions are doing. (10 points) 2. Calculate revenues in Row 3. Always refer to the named cells. (5 points) 3. Calculate variable costs in Row 4. Always refer to the named cells. (5 points) 4. Calculate fixed costs in Row 5. Always refer to the named cells. (5 points) 5. Use the information in the previous steps to calculate EBITDA in Row 6. (5 points) Capital Budgeting Problem Explained (100 points total) Garca and Martinez manufacture widgets and currently have $3 million in taxable income. The company is considering an expansion, and they've asked you to evaluate the project. The expansion requires the firm to produce 80,000 widgets a year for 6 years, and the company estimates they can sell them for $28 per widget. Garca and Martinez estimate they will need an additional $4,000,000 worth of machinery. The machinery costs $200,000 a year to operate and maintain. The machinery's depreciable life is 8-years, and the company expects to salvage the machinery for $1,000,000 at the end of Year 6. If the project is accepted, the company will immediately increase inventory by $500,000 and maintain the new inventory level over the project's life. Similarly, the company will immediately add $75,000 to their cash balance at start-up and maintain that higher cash balance over the project's life. The investments in cash and inventory will be recovered when the project is completed. The marginal cost of producing a widget is $6.00 and the cost of capital is 12%. Calculate the project's NPV by linking to the named variables in Column K. 6 B Revenues Variable Costs Fixed Costs/Expenses EBITDA Depreciation Year 0 D Year 1 E Year 2 F Year 3 G Year 4 Year 5 Year 6 J 8 10 11 EBIT Taxes OCF Change NWC 12 Net Capital Spending 13 CFFA 14 15 16 17 IRR NPV Decision 18 Units Sold 19 65,000 20 70,000 21 75,000 22 80,000 23 85,000 24 90,000 25 95,000 26 100,000 Taxable Income Tax Rate $0 $50,000 15% $50,000 $75,000 25% $75,000 $100,000 34% $100,000 $335,000 39% $335,000 $10,000,000 34% $10,000,000 $15,000,000 35% $15,000,000 $18,333,333 38% $18,333,333 Above $18,333,333 35% 27 105,000 28 29 30 Note: Corporate taxes are now flat at 21%. I have kept the old corporate tax table in this example to illustrate the XLOOKUP function. In addition, personal taxes are still based on similar tables, and most states also have tax tables like the one above. 31 32 33 34 Depreciable Life 35 4 5 6 8 36 Year 1 100.00% 100.00% 66.67% 50.00% 40.00% 33.33% 28.57% 25.00% 37 Year 2 0.00% 22.22% 25.00% 24.00% 22.22% 20.41% 18.75% 38 Year 3 11.11% 12.50% 14.40% 14.81% 14.58% 14.06% 39 Year 4 12.50% 10.80% 9.88% 10.41% 10.55% 40 Year 5 10.80% 9.88% 8.68% 7.91% 41 Year 6 9.88% 8.68% 7.91% 42 Year 7 8.68% 7.91% 43 Year 8 7.91% 44 45 L Information/Variables $3,000,000 Company's Other Taxable Income 80,000 Units Sold 6 Project Life (years) $28.00 Price per unit $4,000,000 Machinery $200,000 Machinery maintenance costs 8 Depreciable Life $1,000,000 Salvage Value $500,000 Increase in Inventory $75,000 Increase in Cash Balance $6.00 Marginal Cost (per unit) 12% Cost of Capital Marginal Tax Rate M 1. Enter the relevant values for the variables that you use in solving this problem in Column K. Name each cell in Column K that contains a variable. Use the following names: Income, Quantity, Life, Price, Machinery, Maintenance Cost, DepLife, Salvage, Inventory, CashBalance, VariableCost, WACC, and TaxRate (e.g. define the name of cell K4 to be Quantity). When linking cells or writing functions, use these names to reference the cells in your calculations. This makes it easier for others to know what the functions are doing. (10 points) 2. Calculate revenues in Row 3. Always refer to the named cells. (5 points) 3. Calculate variable costs in Row 4. Always refer to the named cells. (5 points) 4. Calculate fixed costs in Row 5. Always refer to the named cells. (5 points) 5. Use the information in the previous steps to calculate EBITDA in Row 6. (5 points) Capital Budgeting Problem Explained (100 points total) Garca and Martinez manufacture widgets and currently have $3 million in taxable income. The company is considering an expansion, and they've asked you to evaluate the project. The expansion requires the firm to produce 80,000 widgets a year for 6 years, and the company estimates they can sell them for $28 per widget. Garca and Martinez estimate they will need an additional $4,000,000 worth of machinery. The machinery costs $200,000 a year to operate and maintain. The machinery's depreciable life is 8-years, and the company expects to salvage the machinery for $1,000,000 at the end of Year 6. If the project is accepted, the company will immediately increase inventory by $500,000 and maintain the new inventory level over the project's life. Similarly, the company will immediately add $75,000 to their cash balance at start-up and maintain that higher cash balance over the project's life. The investments in cash and inventory will be recovered when the project is completed. The marginal cost of producing a widget is $6.00 and the cost of capital is 12%. Calculate the project's NPV by linking to the named variables in Column K. 6 B Revenues Variable Costs Fixed Costs/Expenses EBITDA Depreciation Year 0 D Year 1 E Year 2 F Year 3 G Year 4 Year 5 Year 6 J 8 10 11 EBIT Taxes OCF Change NWC 12 Net Capital Spending 13 CFFA 14 15 16 17 IRR NPV Decision 18 Units Sold 19 65,000 20 70,000 21 75,000 22 80,000 23 85,000 24 90,000 25 95,000 26 100,000 Taxable Income Tax Rate $0 $50,000 15% $50,000 $75,000 25% $75,000 $100,000 34% $100,000 $335,000 39% $335,000 $10,000,000 34% $10,000,000 $15,000,000 35% $15,000,000 $18,333,333 38% $18,333,333 Above $18,333,333 35% 27 105,000 28 29 30 Note: Corporate taxes are now flat at 21%. I have kept the old corporate tax table in this example to illustrate the XLOOKUP function. In addition, personal taxes are still based on similar tables, and most states also have tax tables like the one above. 31 32 33 34 Depreciable Life 35 4 5 6 8 36 Year 1 100.00% 100.00% 66.67% 50.00% 40.00% 33.33% 28.57% 25.00% 37 Year 2 0.00% 22.22% 25.00% 24.00% 22.22% 20.41% 18.75% 38 Year 3 11.11% 12.50% 14.40% 14.81% 14.58% 14.06% 39 Year 4 12.50% 10.80% 9.88% 10.41% 10.55% 40 Year 5 10.80% 9.88% 8.68% 7.91% 41 Year 6 9.88% 8.68% 7.91% 42 Year 7 8.68% 7.91% 43 Year 8 7.91% 44 45 L Information/Variables $3,000,000 Company's Other Taxable Income 80,000 Units Sold 6 Project Life (years) $28.00 Price per unit $4,000,000 Machinery $200,000 Machinery maintenance costs 8 Depreciable Life $1,000,000 Salvage Value $500,000 Increase in Inventory $75,000 Increase in Cash Balance $6.00 Marginal Cost (per unit) 12% Cost of Capital Marginal Tax Rate M
Expert Answer:
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these finance questions
-
re Regular Languages and Finite Automata (a) Let L be the set of all strings over the alphabet {a, b} that end in a and do not contain the substring bb. Describe a deterministic finite automaton...
-
CANMNMM January of this year. (a) Each item will be held in a record. Describe all the data structures that must refer to these records to implement the required functionality. Describe all the...
-
Smile is a social media site that is growing in popularity. The firm has been around for a few years and has created a nice market niche for itself. Now the company has a possible buyer who has made...
-
Use the updating function in Exercise 23 but start from the initial condition x0 = 2.
-
Two coins are flipped 1,000 times with the following frequencies: (A) Compute the empirical probability for each outcome. (B) Compute the theoretical probability for each outcome. (C) Using the...
-
Show that $\operatorname{Var}(\hat{\mathbf{y}})=\sigma^{2} \mathbf{H}$.
-
A construction contractor is responsible for a project with seven key tasks. Some of the tasks can begin at any time, but others have predecessor tasks that must be completed previously. The...
-
Explain clearly the pricing method of Zero-Growth Stock and Growth Stock, the impact of reinvestment or growth opportunities on Price-Earnings Ratio (PER), and the meaning of PER in making investment...
-
Choose just one or two types of analysis and explain what made you choose this type: If you choose to use experimental design: Describe a relevant experiment. Report and reflect on possible...
-
Level Curves B Level Curves and Contour Map One of the most useful and common methods for visualizing functions (or surfaces) of two varibles is a Contour Map in which points of constant elevation...
-
Participating Students INSTRUCTIONS: One of the key elements in selling is asking key questions and having a clear sales methodology that helps you gather information and help the customer realize he...
-
Question 3 (1 point) The comparative statements of financial position for Golden Co. is shown below. Express them in common-size percents. Golden Company Comparative Statements of Financial Position...
-
Based on the financial statistics below, identify the two (2) Key Financial statistics impacting Company B's performance Which two teams are likely to be the industry leaders? How does Team B compare...
-
D Data Inputs: Unit sales Selling price per unit Variable expense per unit Total fixed expenses 2 Sales Variable expenses Contribution margin Fixed expenses Net operating income Net profit margin...
-
Explain the newest trends in mission components and also differentiate between company philosophy and public image and why organizations should consider either or both in its overall mission.
-
What are the four types of poultry production systems? Explain each type.
-
For each of the following sets of numbers, calculate a \(95 \%\) confidence interval for the mean ( \(\sigma\) known); before going through the steps in calculating the confidence interval, the...
-
Consider a 3 -year \(10 \%\) coupon bond. The underlying short rate of interest follows a lattice with initial value of \(R=1.15\) and then has an factor of 1.02 , a down factor of .99 , and...
-
Using the density function of the stopping time probability for a fixed \(\lambda\), find the average time to the first event over the entire interval \([0, \infty)\).
Study smarter with the SolutionInn App