Calculate the following using excel (show all work):
Income statement and cash flow statement for Project
Smith Machine Company uses a MARR of 10% and depreciates its assets using a seven-year MACRS. Smith Machine Company’s effective income tax rate is 25%.
Data:
Equipment cost- $35,000 including trade in allowance and sales tax. The total cost of the shipping and handling is $7,500. The total cost to install the coolant system is $68,000. Setup cost of the coolant system will be $13,500. The useful life of the coolant system is 8 years. The coolant system are to be sold at the end of the project for $8,000
Each coolant system will require 1 operator at a rate of $24/hr. The Company shop operates 4080 hr/yr. The total maintenance labor cost is 20% of operating hours at $19/hr. Total annual direct materials are $275,000. Manufacturing overhead excluding depreciation is presumed to be an additional $165,00/yr.
Revenues are expected to be $625,000 for the first year as a result of the project and is expected to increase by 3% each year throughout the project. The project will run for 8 years
To calculate the income statement and cash flow statement for the project using Excel, you can follow the steps below:
1. Set up the necessary columns and rows in Excel. You will need columns for the various components of the income statement and cash flow statement, such as revenues, expenses, depreciation, taxes, and net cash flow. You will also need rows for each year of the project's duration.
2. Calculate the annual depreciation expense for each year using the MACRS method. The depreciation method assigns a specific percentage of the asset's cost as depreciation expense each year. In this case, since the useful life of the coolant system is 8 years, you will use the MACRS 8-year depreciation table to determine the annual depreciation expense.
3. Calculate the annual operating expenses. This includes the labor cost for the coolant system operator, maintenance labor cost, direct materials cost, and manufacturing overhead excluding depreciation. Multiply the rate per hour for each type of labor by the number of hours per year to calculate the annual labor cost. Sum up all the operating expenses for each year.
4. Calculate the annual net income before taxes by subtracting the total operating expenses from the revenues for each year.
5. Calculate the annual income tax expense by multiplying the net income before taxes by the effective income tax rate of 25%.
6. Calculate the annual net income after taxes by subtracting the income tax expense from the net income before taxes.
7. Calculate the annual cash flow from operations by adding back the depreciation expense to the net income after taxes. This is because depreciation is a non-cash expense.
8. Calculate the annual net cash flow by subtracting the annual cash flow from operations from the annual upfront revenue for each year.
9. Calculate the cumulative net cash flow by summing up the annual net cash flows from year 1 to the current year for each year.
10. Finally, calculate the project's net present value (NPV) by discounting the annual net cash flows back to the present using the MARR (minimum acceptable rate of return) of 10%. Subtract the initial investment costs from the sum of the discounted cash flows to get the NPV.
By following these steps, you can create an Excel spreadsheet that calculates the income statement and cash flow statement for the project.