Instructions: 1. Create 1 Excel Spreadsheet with 3 different tabs/worksheets. 2. On each worksheet, copy the Buy-Lease Analysis template to the tab. 3. Run a buy-lease analysis for each question on each individual worksheet tab (ie; 1 analysis per tab ). 4. At the end of each analysis (below the analysis), clearly state which financing option is the most economically attractive. Case 2: Baldwin Equipment Inc. (Bergeron, pg. 370) Management of Baldwin Equipment is considering increasing the productivity of its plant. Management heard from suppliers that a certain piece of equipment could entail an after-tax cash flow savings of more than $35,000 a year if it was installed in Baldwin's plant. However, Jim Henderson, the controller of the company, is unsure whether the company should buy or lease the equipment. If the asset is leased for a 10 year period, it would cost the company $45,000 a year (before tax). The company's income tax rate is 50%. If the company buys the asset, it would cost $300,000 and be financed entirely through debt for a 10 year period at a cost of 10%. The asset's capital cost allowance is 25%. Qn the basis.of this information, Jim is now considering whether to purchase or lease the equipment. Questions: BO 1. QA the basis. of the information provided above, which method of financing would be the most economically attractive (ie; lowest total annual payments)? Justify your answer by running a Buy-Lease analysis. /10 2. Would Jim's decision to lease or buy the asset remain the same if the capital cost allowance increased to 40% ? Run a 2pd Buy-Lease analysis to determine the most economically attractive option. 110 3. If Jim is.able to negotiate a better interest rate on the loan down to 6% and assuming the CCA amount is still the original 25% ), which method of financing would now be considered the most economically attractive (or does it remain to same)? Run a 3rd Buy-Lease analysis to determine your answer (HINT: remember to change your PVIFA factor using the new interest rate and reset your CCA to 25% ). *Make sure to clearly state at the bottom of each Buy-Lease Analysis which option is the preferred method of financing for each of the above questions. Lease Info: payment Term 50 Lease Cost: \begin{tabular}{|c|c|c|} \hline Annal Payment & Tax Savings: & After Tax Cost \\ \hline 50 & 0 & \\ \hline \end{tabular} toan Info: 0 Loan Amount $0 Interest Rate 0% CCA or 1211109 Straight tine Term (years) Values: PVIFA Factor Payment 0% Asset Info: Value/Cost 0 Residual 50 Tax Rate 30 CCA $0 0% 0% \begin{tabular}{|r|l|l|} \hline Year & \begin{tabular}{l} CCA or \\ Straight tine \end{tabular} & \begin{tabular}{l} Value o year \\ end \end{tabular} \\ \hline 1 & & \\ \hline 2 & & \\ \hline 3 & & \\ \hline 4 & & \\ \hline 5 & & \\ \hline 6 & & \\ \hline 7 & & \\ \hline 8 & & \\ \hline 9 & & \\ \hline 10 & & \\ \hline \end{tabular} 2221 2423 2525 2726 2928 3130 3332 3434 3635 377 Lease Layment Payme (atter tax) Beginning Principal Loan CCA/ Tax dedcutible Expenses. Tax Shield Net Cost of Owning Instructions: 1. Create 1 Excel Spreadsheet with 3 different tabs/worksheets. 2. On each worksheet, copy the Buy-Lease Analysis template to the tab. 3. Run a buy-lease analysis for each question on each individual worksheet tab (ie; 1 analysis per tab ). 4. At the end of each analysis (below the analysis), clearly state which financing option is the most economically attractive. Case 2: Baldwin Equipment Inc. (Bergeron, pg. 370) Management of Baldwin Equipment is considering increasing the productivity of its plant. Management heard from suppliers that a certain piece of equipment could entail an after-tax cash flow savings of more than $35,000 a year if it was installed in Baldwin's plant. However, Jim Henderson, the controller of the company, is unsure whether the company should buy or lease the equipment. If the asset is leased for a 10 year period, it would cost the company $45,000 a year (before tax). The company's income tax rate is 50%. If the company buys the asset, it would cost $300,000 and be financed entirely through debt for a 10 year period at a cost of 10%. The asset's capital cost allowance is 25%. Qn the basis.of this information, Jim is now considering whether to purchase or lease the equipment. Questions: BO 1. QA the basis. of the information provided above, which method of financing would be the most economically attractive (ie; lowest total annual payments)? Justify your answer by running a Buy-Lease analysis. /10 2. Would Jim's decision to lease or buy the asset remain the same if the capital cost allowance increased to 40% ? Run a 2pd Buy-Lease analysis to determine the most economically attractive option. 110 3. If Jim is.able to negotiate a better interest rate on the loan down to 6% and assuming the CCA amount is still the original 25% ), which method of financing would now be considered the most economically attractive (or does it remain to same)? Run a 3rd Buy-Lease analysis to determine your answer (HINT: remember to change your PVIFA factor using the new interest rate and reset your CCA to 25% ). *Make sure to clearly state at the bottom of each Buy-Lease Analysis which option is the preferred method of financing for each of the above questions. Lease Info: payment Term 50 Lease Cost: \begin{tabular}{|c|c|c|} \hline Annal Payment & Tax Savings: & After Tax Cost \\ \hline 50 & 0 & \\ \hline \end{tabular} toan Info: 0 Loan Amount $0 Interest Rate 0% CCA or 1211109 Straight tine Term (years) Values: PVIFA Factor Payment 0% Asset Info: Value/Cost 0 Residual 50 Tax Rate 30 CCA $0 0% 0% \begin{tabular}{|r|l|l|} \hline Year & \begin{tabular}{l} CCA or \\ Straight tine \end{tabular} & \begin{tabular}{l} Value o year \\ end \end{tabular} \\ \hline 1 & & \\ \hline 2 & & \\ \hline 3 & & \\ \hline 4 & & \\ \hline 5 & & \\ \hline 6 & & \\ \hline 7 & & \\ \hline 8 & & \\ \hline 9 & & \\ \hline 10 & & \\ \hline \end{tabular} 2221 2423 2525 2726 2928 3130 3332 3434 3635 377 Lease Layment Payme (atter tax) Beginning Principal Loan CCA/ Tax dedcutible Expenses. Tax Shield Net Cost of Owning