Solved Question 2. Looking for help with Question 3 and 4 in Excel.

Commercial Building for question 3

2. Monty Corporation predicts the following revenues and cost of goods sold: Year 1 2 3 Revenues $180,000 $210,000 $240,000 Cost of goods sold $ 68,000 $ 79.000 $ 88,000 Using these revenues and costs as drivers, Monty predicts the following relationships : | 4 percent of revenues as a cash balance, 1 11 percent of the cost of goods sold as an inventory balance, 6 percent of the cost of goods sold as an accounts payable balance, 1 5 percent of revenues as accrued expenses balance. All these balances would be needed at the beginning of each year and are estimated from the year-end annual estimates of revenues and cost of goods sold given above. The business will end at the end of year 3 and all working capital balances will be collected (or realized) at their face value. Please calculate the working capital balance needed and the incremental investment in working capital needed for years 0,1,2 and 3.9 1 Working capital needed Year 0 S-27,760.00 Year 1 S-32,330.00 Year 2_5-36,560.00. Year 3 1 Incremental investment in working capital Year O_S-27,760.00_Year 1_$-4,570.00_Year 2_3-4,230.0_Year 3_$36,560.00 1 3. You are thinking of opening an internet coffee shop and estimate the following cash flows. The cost of the establishment is $2,500,000 for the building and $700,000 for equipment (tax life of 7 years) and both are placed it into service on January 1, year 0. The business will eam $2,200,000 in revenue the first year and have cash expenses of 79% of revenues during its twenty years of operation. After the first year, sales are expected to grow at 6% per year, expenses will continue at 79% of sales. You will need to calculate depreciation on the building and the equipment using the methods discussed in the chapter. At the end of 20 years, the building and equipment will be sold for an after-tax cash disposition value of $600,000. No other cash flows will occur during the 20 years of operation. Using a 26 percent tax rate, and a 7.75 percent cost of money, calculate the following: The first year's total cash flow 1 The second year's total cash flow The net present value of the project 4. You place a commercial building into service on April 15, 2021 costing $40,000,000. What is the depreciation expense allowed by the IRS for this building for tax years 2021? And 2022?||| Depreciation expense for 2021 2. Monty Corporation predicts the following revenues and cost of goods sold: Year 1 2 3 Revenues $180,000 $210,000 $240,000 Cost of goods sold $ 68,000 $ 79.000 $ 88,000 Using these revenues and costs as drivers, Monty predicts the following relationships : | 4 percent of revenues as a cash balance, 1 11 percent of the cost of goods sold as an inventory balance, 6 percent of the cost of goods sold as an accounts payable balance, 1 5 percent of revenues as accrued expenses balance. All these balances would be needed at the beginning of each year and are estimated from the year-end annual estimates of revenues and cost of goods sold given above. The business will end at the end of year 3 and all working capital balances will be collected (or realized) at their face value. Please calculate the working capital balance needed and the incremental investment in working capital needed for years 0,1,2 and 3.9 1 Working capital needed Year 0 S-27,760.00 Year 1 S-32,330.00 Year 2_5-36,560.00. Year 3 1 Incremental investment in working capital Year O_S-27,760.00_Year 1_$-4,570.00_Year 2_3-4,230.0_Year 3_$36,560.00 1 3. You are thinking of opening an internet coffee shop and estimate the following cash flows. The cost of the establishment is $2,500,000 for the building and $700,000 for equipment (tax life of 7 years) and both are placed it into service on January 1, year 0. The business will eam $2,200,000 in revenue the first year and have cash expenses of 79% of revenues during its twenty years of operation. After the first year, sales are expected to grow at 6% per year, expenses will continue at 79% of sales. You will need to calculate depreciation on the building and the equipment using the methods discussed in the chapter. At the end of 20 years, the building and equipment will be sold for an after-tax cash disposition value of $600,000. No other cash flows will occur during the 20 years of operation. Using a 26 percent tax rate, and a 7.75 percent cost of money, calculate the following: The first year's total cash flow 1 The second year's total cash flow The net present value of the project 4. You place a commercial building into service on April 15, 2021 costing $40,000,000. What is the depreciation expense allowed by the IRS for this building for tax years 2021? And 2022?||| Depreciation expense for 2021