I need help with a balance sheet and verify work that I have already done with a financial analysis. My main concern is balance sheet.

Berry EVALUATES FAST You are considering the purchase of a new fast fabricating machine for your firm, Berry Enterprises, Inc. BEI makes novelty items for wholesale to retail outlets located in tourist traps. The purchase of the fast will allow BEI to market a new product, a walking stick with built-in bear repellant. In order to analyze the decisions to manufacture and sell the walking sticks and to lease the fast, you have collected the following data, some of which may be relevant (all dollars in 2017 prices): Cost of fast = $5,000,000 Useful (and depreciable) life of fast = 10 years Salvage value (market and depreciation) of fast = $0 Expected walking stick sales volume (in lots of 1000) = 2000/year Wholesale price per 1000 lot of walking sticks = $899 Annual fast maintenance contract costs = $110,000 (the only fixed cash cost) Average variable cost per lot of walking sticks = $500 Additional working capital (supplies) required at beginning of year 1 only = $300,000 Current equity = $60,000,000 Current debt = $40,000,000 Marginal tax rate for CEI = 30% Inflation rate = .03 Fast can be financed in several ways. The following data pertain to financing options: Required return on equity (real) = .05 Bank loan interest rate available, given current capital structure = .055 Lease contract with payments made in 10 equal annual installments, beginning at start of lease period. Lease contract includes maintenance costs. Payment = $700,000 Evaluate the fast as a business opportunity for BEI. Evaluate the lease financing option for the fast as well. Use the \"easy way\" to value a financial lease that is presented in BMA p 648-650. Although BEI is a tax-paying entity and could benefit from the use of MACRS depreciation, for simplicity here (i.e., my grading ease), please use straight-line depreciation expense recognition. A fairly standard investment decision analysis. Have you learned how to conduct such analysis, which required determination of net cash flows and the discount rate. 2. A lease financing analysis. This is new material, but it relies on your having learned how to set up a financing decision analysis. 2 I'll be looking for a correct investment decision analysis, with correct net cash flows and discount rate. Also, a correct lease financing analysis, with correct cash flows and discount rate. Finally, you will need to make a recommendation as to whether to acquire the fast and if so how to finance it. you do not need the betas, as you are given the rE. Note that the 5% rate is real, so you will need to make it nominal. . The working capital investment is meant to be pretty simple here. It is a balance sheet adjustment to cash flows, not part of the income statement cash flows. There is a cash out at year end 0, and a cash in at year end 10. No additional WC investments are are required. Learning Points: 1. Investment decision analysis (again) 2. Lease financing analysis 3. Discount rate for lease analysis v. discount rate for investment analysis 4. Separation of investment and financing analyses 5. Real v. nominal interest rates and prices (again) Lease Financing Valuation asset cost term sal val lease pmt debt int rate mtr op cost differential net int rate depreciation exp yr end init fin 0 1 2 3 4 5 6 7 8 9 10 npv used this template and edited 5000000 10 0 700000 0.055 0.3 0 0.0385 500000 lease pmt lease ts depn ts lost -700000 -700000 210000 -700000 210000 -700000 210000 -700000 210000 -700000 210000 -700000 210000 -700000 210000 -700000 210000 -700000 210000 210000 ncf -700,000 -490,000 -490,000 -490,000 -490,000 -490,000 -490,000 -490,000 -490,000 -490,000 210,000 ($4,224,413) Conlusion Since the NPV of lease is higher than that of buying, the zippy should be leased. so this was my second attempt after reading the general discussion threads. I origianlly followed the book and the sheet c Notes: 1. Initial financing provided is the cost of the asset, because leasing provides financing for the asset. 2. There are two cash flow effects of taxes. You can deduct lease expense (so you get a tax shield, depending on marginal tax rate). You can't deduct depreciation expense (so you lose the depreciation tax shield), because you don't ow 3. Discount rate is the net (after tax) interest rate on the loan that is the alternative financing source. 4. The organization has already done the investment analysis and determined that the asset is worth purchasing. BUYING no loan cash outflow depreciaiton MAINT COST tax shield Cost of equity (Nominal) (0.05+0.03) Cost of debt WACC YR END 0 1 2 3 4 5 6 7 8 9 10 5000000 10 110000 0.3 8.00% 3.850% 6.34% cash out DEPR MAIN TAX net cash -5000000 -5000000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 npv ($4,471,263) uld be leased. y followed the book and the sheet called "lease financing" for the asset. on marginal tax rate). n tax shield), because you don't own the asset. ncing source. asset is worth purchasing. Lease Financing Valuation used this template and edited asset cost 5000000 term 10 sal val 0 lease pmt 700000 debt int rate 0.055 mtr 0.3 op cost differential 0 net int rate 0.0385 depreciation exp 500000 yr end init fin lease pmt lease ts depn ts lost ncf 0 5,000,000 -700000 210000 1 -700000 210000 -150000 2 -700000 210000 -150000 3 -700000 210000 -150000 4 -700000 210000 -150000 5 -700000 210000 -150000 6 -700000 210000 -150000 7 -700000 210000 -150000 8 -700000 210000 -150000 9 -700000 210000 -150000 10 210000 -150000 npv 4,510,000 -640,000 -640,000 -640,000 -640,000 -640,000 -640,000 -640,000 -640,000 -640,000 60,000 ($240,183) Conlusion Since the NPV of lease is higher than that of buying, the zippy should be leased. so this was my third attempt after reading the general discussion threads. I origianlly followed the book and the sheet calle Notes: 1. Initial financing provided is the cost of the asset, because leasing provides financing for the asset. 2. There are two cash flow effects of taxes. You can deduct lease expense (so you get a tax shield, depending on marginal tax rate). You can't deduct depreciation expense (so you lose the depreciation tax shield), because you don't ow 3. Discount rate is the net (after tax) interest rate on the loan that is the alternative financing source. 4. The organization has already done the investment analysis and determined that the asset is worth purchasing. looked at excel is fun https://www.youtube.com/watch?v=QP-xAqP0tII cost of asset 5000000 term 10 annual deprecaition 500000 salvage is 0 ann lease pmt 700000 mtr 30% bank loan int rate 0.055 yr cost of asset dep ts total after tax lease paym savings from lease irr of lease alt cost 0 -5000000 -5000000 -490000 4510000 7% 0.0385 LEASE 1 2 3 4 5 150000 150000 -490000 -640000 150000 150000 -490000 -640000 150000 150000 -490000 -640000 150000 150000 -490000 -640000 150000 150000 -490000 -640000 if you borrow from bank BUYING no loan cash outflow depreciaiton MAINT COST tax shield Cost of equity (Nominal) (0.05+0.03) Cost of debt WACC YR END 0 1 2 3 4 5 6 7 8 9 10 5000000 10 110000 0.3 8.00% 3.850% 6.34% cash out DEPR MAIN TAX net cash -5000000 -5000000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 npv ($4,471,263) uld be leased. llowed the book and the sheet called "lease financing". This like the video it has init fin as a cash in for the asset. on marginal tax rate). n tax shield), because you don't own the asset. ncing source. asset is worth purchasing. 6 7 8 9 10 150000 150000 -490000 -640000 150000 150000 -490000 -640000 150000 150000 -490000 -640000 150000 150000 -490000 -640000 150000 150000 -490000 -640000 Lease Financing Valuation used this template and edited asset cost 5000000 term 10 sal val 0 lease pmt 700000 debt int rate 0.055 mtr 0.3 op cost differential 0 net int rate 0.0385 depreciation exp 500000 yr end init fin lease pmt lease ts depn ts lost ncf 0 5,000,000 -700000 210000 4,510,000 1 -700000 210000 -150000 -640,000 2 -700000 210000 -150000 -640,000 3 -700000 210000 -150000 -640,000 4 -700000 210000 -150000 -640,000 5 -700000 210000 -150000 -640,000 6 -700000 210000 -150000 -640,000 7 -700000 210000 -150000 -640,000 8 -700000 210000 -150000 -640,000 9 -700000 210000 -150000 -640,000 10 210000 -150000 60,000 npv ($240,183) ($4,750,183.31) Notes: 1. Initial financing provided is the cost of the asset, because leasing provides financing for the asset. 2. There are two cash flow effects of taxes. You can deduct lease expense (so you get a tax shield, depending on marginal tax rate). You can't deduct depreciation expense (so you lose the depreciation tax shield), because you don't ow 3. Discount rate is the net (after tax) interest rate on the loan that is the alternative financing source. 4. The organization has already done the investment analysis and determined that the asset is worth purchasing. looked at excel is fun https://www.youtube.com/watch?v=QP-xAqP0tII cost of asset 5000000 term 10 annual deprecaition 500000 salvage is 0 ann lease pmt 700000 mtr 30% bank loan int rate 0.055 yr 0 cost of asset -5000000 dep ts total -5000000 after lease paymt -490000 savings from lease 4510000 irr of lease 7% alt cost 0.0385 LEASE 1 2 3 4 5 150000 150000 -490000 -640000 150000 150000 -490000 -640000 150000 150000 -490000 -640000 150000 150000 -490000 -640000 150000 150000 -490000 -640000 BUYING no loan cash outflow depreciaiton MAINT COST tax shield 5000000 10 110000 0.3 YR END 0 1 2 3 4 5 6 7 8 9 10 cash out DEPR MAIN TAX net cash -5000000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 500000 110000 183000 73000 npv ($4,403,458.46) ng for the asset. ng on marginal tax rate). tion tax shield), because you don't own the asset. nancing source. he asset is worth purchasing. 6 7 8 150000 150000 -490000 -640000 150000 150000 -490000 -640000 150000 150000 -490000 -640000 9 10 150000 150000 150000 150000 -490000 -49000 -640000 -640000 Learning Points: 1. Investment decision analysis (again) 2. Lease financing analysis 3. Discount rate for lease analysis v. discount rate for investment analysis 4. Separation of investment and financing analyses 5. Real v. nominal interest rates and prices (again) 1. investment analysis is just comparing. I did that in my 3 examples 2. Lease finance what I did on the left side only analysis the lease 3. if you are just buying you do not need the int rate ( no borrowing loan ) 4.created tables on the left and the right 5. this would be a real int real int rate = nominal rate - inf rate .0385 - .03 = 0.355