Financial Manager - PIQUI This almost al financial analysis project requires all knowled ad in this course. It also teaches us how to use Excel to perform financial anal a professional way. Project - Capital Budgeting, Breakeren Analysis, and Seenario Analysis Annie De CFO of Dummont Inc. opened the company confidential e contains a draft of a competitive bid for a contract to supply duffel canvasnih Canadian Air Forces. The cover memo from Dummont's CEO asked Mrs. Du review the bid before it was submitted contains a draft of a The hide and its documents had been prepared by Dummont's sales sall alla Dummont to supply 1000 yards of duffel canvas per year for years. The proposed selling price was fixed at S30 per yard. The variable costs were estimated to be sure vand and the fixed costs (excluding depreciation) to be $300,000 in the first Moreover, both variable costs and fixed costs are expected to increase at the inflation rate of 1% per year after the first year), but the yards sold and the price per yard would be fixed by contract. 110,372 Mrs. Duncan was not usually involved in sales, but this bid was unusual in at least two aspects. First, if accepted by the client, it would commit Dummont to a fixed price, long- term contract. Second, producing the duffel canvas would require an investment of million to purchase machinery and to refurbish Dummont's plant in Halifax, Nova Scotia right now at the beginning of the first year. Mrs. Duncan started working on the proposal and after a week she had collected the following information about the project: The plant in Halifax was old since it had been built in the 1920s. It is currently idle and fully depreciated on Dummont's books, except for the purchase cost of the land of $10,000. So, for the base case analysis, Dummont can just ignore the current market value of the land. . Now the land is a valuable shorefront property, and Mrs. Duncan estimates it can be sold for $600,000 at the end of year 5. Refurbishing the plant would cost $500,000. This investment would be depreciated in an asset class that has a CCA rate of 5% The new machinery would cost $900,000. This investment could be depreciated in an asset class that has a CCA rate of 30% The refurbished plant and new machinery would last for many years. However, the remaining market for duffel canvas was small and it was not clear that additional orders could be obtained once the Forces contract was finished. The machinery was custom built and could be used only for duffel canvas. Its second-hand value at the end of five years was probably zero Sales her - Sundvar: CA Finally, Mrs. Duncan estimated that the investment in working capital would average Tons of sales. The working capital remains unchanged over the whole life of the project and will be fully recovered at the end of project life The cost of capital (ie, discount rate) for Dummois and ther e is 3 The 50 of capital gain is taxable. In other words, the half of tax capital gains ved. For example, the original and cost is $10.000, now its market value is $50,000. then the cash flow received by the seller is $43,000 - 50.000 - (50.000 - 10.000) * 0.5 * 35%) With all this information on hand, Mrs. Duncan constructed a spreadsheet to calculate the NPV of the duffel canvas project, assuming that Dummont's bid would be accepted by the Forces. Should Mrs. Duncan recommend submitting the bid to the Forces at the proposed price of $30 per yard? Requirements 1. Using an Excel Spreadsheet: Find the NPV, IRR, MIRR, PI, Payback, and Discounted Payback of Dummont's project using the pro forma financial statement method to determine cash flows. Enter the input variables in cells of their own at the top of the spreadsheet (so it is easier to do calculations). Set up the necessary equations by referencing to the input variable cells. The spreadsheet must be formula driven: do not put my numbers in equations, only cell references Use Excel's built-in functions wherever possible (e.g. PV and IRR functions). 2. Breakeven Analysis Find the NPV breakeven points in the number of units, the sales price per unit, and the cost of capital (i.e., discount rate) for the base case. (Hint: use the Goal Seek tool built in Excel) NEGATIVE # 3. Scenario Analysis: Repeat the analysis for two different scenarios. (Use a separate spreadsheet) Consider the following independent scenarios for the company. For each scenario, calculate the NPV, IRR, MIRR, PI, Payback, and Discounted Payback, and include these analyses in your final recommendation. Find the NPV breakeven points in the number of units sold, the sales price per unit, and the cost of capital (i.e., discount rate) for the following two cases. (a) Mrs. Duncan had just finished reviewing the spreadsheet with her financial analysis of the project when another confidential envelope arrived from Dummont's CEO. It contained a firm offer from a Halifax real estate developer to purchase Dummont's land and plant TODAY for $1.5 million in cash (consider that the plant and land can still be sold at the end of year 5 for only $600,000). Should Mrs. Duncan recommend submitting the bid to the Forces at the proposed price of $30 per yard anyway? (Hint: The opportunity cost of $1.5 million should be accounted for at the beginning of the project.) upper lower 3:08 PM Wed Nov 27 61% Final_Project_CAPITAL BUDGETING_Revised 1 of 4 Financial Management - Fall 2019 Purpose: This almost real financial analysis project requires all knowledge we have learned in this course. It also teaches us how to use Excel to perform financial analysis in a professional way. Project - Capital Budgeting, Breakeven Analysis, and Scenario Analysis Annie Duncan, CFO of Dummont Inc., opened the company confidential envelope. It contains a draft of a competitive bid for a contract to supply duffel canvas to the Canadian Air Forces. The cover memo from Dummont's CEO asked Mrs. Duncan to review the bid before it was submitted. The bid and its documents had been prepared by Dummont's sales staff. It called for Dummont to supply 100,000 yards of duffel canvas per year for 5 years. The proposed selling price was fixed at S30 per yard. The variable costs were estimated to be S18 per yard and the fixed costs (excluding depreciation) to be $300,000 in the first year. Moreover, both variable costs and fixed costs are expected to increase at the inflation rate of 4% per year after the first year), but the yards sold and the price per yard would be fixed by contract. Mrs. Duncan was not usually involved in sales, but this bid was unusual in at least two aspects. First, if accepted by the client, it would commit Dummont to a fixed price, long- term contract. Second, producing the duffel canvas would require an investment of $1.4 million to purchase machinery and to refurbish Dummont's plant in Halifax, Nova Scotia right now at the beginning of the first year. Mrs. Duncan started working on the proposal and after a week she had collected the following information about the project: The plant in Halifax was old since it had been built in the 1920s. It is currently idle and fully depreciated on Dummont's books, except for the purchase cost of the land of $10,000. So, for the base case analysis, Dummont can just ignore the current market value of the land, Now the land is a valuable shorefront property, and Mrs. Duncan estimates it can be sold for $600,000 at the end of year S. Refurbishing the plant would cost $500,000. This investment would be depreciated in an asset class that has a CCA rate of 5%. The new machinery would cost $900,000. This investment could be depreciated in an asset class that has a CCA rate of 30%. The refurbished plant and new machinery would last for many years. However, the remaining market for duffel canvas was small and it was not clear that additional orders could be obtained once the Forces contract was finished. The machinery was custom built and could be used only for duffel canvas. Its second-hand value at the end of five years was probably zero. 3:08 PM Wed Nov 27 61% Final_Project_CAPITAL BUDGETING_Revised 2 of 4 . Finally, Mrs. Duncan estimated that the investment in working capital would average 10% of sales. The working capital remains unchanged over the whole life of the project and will be fully recovered at the end of project life. The cost of capital (i.c., discount rate) for Dummont is 12% and the tax rate is 35% The 50% of capital gain is taxable. In other words, the half of tax on capital gain is waived. For example, the original land cost is $10,000, now its market value is $50.000, then the cash flow received by the seller is $43,000 = 50,000 - (50.000 - 10.000) x 0.5 * 35%) With all this information on hand, Mrs. Duncan constructed a spreadsheet to calculate the NPV of the duffel canvas project, assuming that Dummont's bid would be accepted by the Forces. Should Mrs. Duncan recommend submitting the bid to the Forces at the proposed price of $30 per yard? Requirements 1. Using an Excel Spreadsheet: Find the NPV, IRR, MIRR, PI, Payback, and Discounted Payback of Dummont's project using the pro forma financial statement method to determine cash flows. Enter the input variables in cells of their own at the top of the spreadsheet (so it is easier to do calculations). Set up the necessary equations by referencing to the input variable cells. The spreadsheet must be formula driven do not put any numbers in equations, only cell references Use Excel's built-in functions wherever possible (e.g. PV and IRR functions). 2. Breakeven Analysis Find the NPV breakeven points in the number of units, the sales price per unit, and the cost of capital (i.e., discount rate) for the base case. (Hint: use the Goal Seek tool built in Excel) 3. Scenario Analysis: Repeat the analysis for two different scenarios. (Use a separate spreadsheet) Consider the following independent scenarios for the company. For cach scenario, calculate the NPV, IRR, MIRR, PL, Payback, and Discounted Payback, and include these analyses in your final recommendation. Find the NPV breakeven points in the number of units sold, the sales price per unit, and the cost of capital (ie, discount rate) for the following two cases. (a) Mrs. Duncan had just finished reviewing the spreadsheet with her financial analysis of the project when another confidential envelope arrived from Dummont's CEO. It contained a firm offer from a Halifax real estate developer to purchase Dummont's land and plant TODAY for S1.5 million in cash (consider that the plant and land can still be sold at the end of years for only $600,000). Should Mrs. Duncan recommend submitting the bid to the Forces at the proposed price of $30 per yard anyway? (Hint: The opportunity cost of S1.million should be accounted for at the beginning of the project.) Financial Manager - PIQUI This almost al financial analysis project requires all knowled ad in this course. It also teaches us how to use Excel to perform financial anal a professional way. Project - Capital Budgeting, Breakeren Analysis, and Seenario Analysis Annie De CFO of Dummont Inc. opened the company confidential e contains a draft of a competitive bid for a contract to supply duffel canvasnih Canadian Air Forces. The cover memo from Dummont's CEO asked Mrs. Du review the bid before it was submitted contains a draft of a The hide and its documents had been prepared by Dummont's sales sall alla Dummont to supply 1000 yards of duffel canvas per year for years. The proposed selling price was fixed at S30 per yard. The variable costs were estimated to be sure vand and the fixed costs (excluding depreciation) to be $300,000 in the first Moreover, both variable costs and fixed costs are expected to increase at the inflation rate of 1% per year after the first year), but the yards sold and the price per yard would be fixed by contract. 110,372 Mrs. Duncan was not usually involved in sales, but this bid was unusual in at least two aspects. First, if accepted by the client, it would commit Dummont to a fixed price, long- term contract. Second, producing the duffel canvas would require an investment of million to purchase machinery and to refurbish Dummont's plant in Halifax, Nova Scotia right now at the beginning of the first year. Mrs. Duncan started working on the proposal and after a week she had collected the following information about the project: The plant in Halifax was old since it had been built in the 1920s. It is currently idle and fully depreciated on Dummont's books, except for the purchase cost of the land of $10,000. So, for the base case analysis, Dummont can just ignore the current market value of the land. . Now the land is a valuable shorefront property, and Mrs. Duncan estimates it can be sold for $600,000 at the end of year 5. Refurbishing the plant would cost $500,000. This investment would be depreciated in an asset class that has a CCA rate of 5% The new machinery would cost $900,000. This investment could be depreciated in an asset class that has a CCA rate of 30% The refurbished plant and new machinery would last for many years. However, the remaining market for duffel canvas was small and it was not clear that additional orders could be obtained once the Forces contract was finished. The machinery was custom built and could be used only for duffel canvas. Its second-hand value at the end of five years was probably zero Sales her - Sundvar: CA Finally, Mrs. Duncan estimated that the investment in working capital would average Tons of sales. The working capital remains unchanged over the whole life of the project and will be fully recovered at the end of project life The cost of capital (ie, discount rate) for Dummois and ther e is 3 The 50 of capital gain is taxable. In other words, the half of tax capital gains ved. For example, the original and cost is $10.000, now its market value is $50,000. then the cash flow received by the seller is $43,000 - 50.000 - (50.000 - 10.000) * 0.5 * 35%) With all this information on hand, Mrs. Duncan constructed a spreadsheet to calculate the NPV of the duffel canvas project, assuming that Dummont's bid would be accepted by the Forces. Should Mrs. Duncan recommend submitting the bid to the Forces at the proposed price of $30 per yard? Requirements 1. Using an Excel Spreadsheet: Find the NPV, IRR, MIRR, PI, Payback, and Discounted Payback of Dummont's project using the pro forma financial statement method to determine cash flows. Enter the input variables in cells of their own at the top of the spreadsheet (so it is easier to do calculations). Set up the necessary equations by referencing to the input variable cells. The spreadsheet must be formula driven: do not put my numbers in equations, only cell references Use Excel's built-in functions wherever possible (e.g. PV and IRR functions). 2. Breakeven Analysis Find the NPV breakeven points in the number of units, the sales price per unit, and the cost of capital (i.e., discount rate) for the base case. (Hint: use the Goal Seek tool built in Excel) NEGATIVE # 3. Scenario Analysis: Repeat the analysis for two different scenarios. (Use a separate spreadsheet) Consider the following independent scenarios for the company. For each scenario, calculate the NPV, IRR, MIRR, PI, Payback, and Discounted Payback, and include these analyses in your final recommendation. Find the NPV breakeven points in the number of units sold, the sales price per unit, and the cost of capital (i.e., discount rate) for the following two cases. (a) Mrs. Duncan had just finished reviewing the spreadsheet with her financial analysis of the project when another confidential envelope arrived from Dummont's CEO. It contained a firm offer from a Halifax real estate developer to purchase Dummont's land and plant TODAY for $1.5 million in cash (consider that the plant and land can still be sold at the end of year 5 for only $600,000). Should Mrs. Duncan recommend submitting the bid to the Forces at the proposed price of $30 per yard anyway? (Hint: The opportunity cost of $1.5 million should be accounted for at the beginning of the project.) upper lower 3:08 PM Wed Nov 27 61% Final_Project_CAPITAL BUDGETING_Revised 1 of 4 Financial Management - Fall 2019 Purpose: This almost real financial analysis project requires all knowledge we have learned in this course. It also teaches us how to use Excel to perform financial analysis in a professional way. Project - Capital Budgeting, Breakeven Analysis, and Scenario Analysis Annie Duncan, CFO of Dummont Inc., opened the company confidential envelope. It contains a draft of a competitive bid for a contract to supply duffel canvas to the Canadian Air Forces. The cover memo from Dummont's CEO asked Mrs. Duncan to review the bid before it was submitted. The bid and its documents had been prepared by Dummont's sales staff. It called for Dummont to supply 100,000 yards of duffel canvas per year for 5 years. The proposed selling price was fixed at S30 per yard. The variable costs were estimated to be S18 per yard and the fixed costs (excluding depreciation) to be $300,000 in the first year. Moreover, both variable costs and fixed costs are expected to increase at the inflation rate of 4% per year after the first year), but the yards sold and the price per yard would be fixed by contract. Mrs. Duncan was not usually involved in sales, but this bid was unusual in at least two aspects. First, if accepted by the client, it would commit Dummont to a fixed price, long- term contract. Second, producing the duffel canvas would require an investment of $1.4 million to purchase machinery and to refurbish Dummont's plant in Halifax, Nova Scotia right now at the beginning of the first year. Mrs. Duncan started working on the proposal and after a week she had collected the following information about the project: The plant in Halifax was old since it had been built in the 1920s. It is currently idle and fully depreciated on Dummont's books, except for the purchase cost of the land of $10,000. So, for the base case analysis, Dummont can just ignore the current market value of the land, Now the land is a valuable shorefront property, and Mrs. Duncan estimates it can be sold for $600,000 at the end of year S. Refurbishing the plant would cost $500,000. This investment would be depreciated in an asset class that has a CCA rate of 5%. The new machinery would cost $900,000. This investment could be depreciated in an asset class that has a CCA rate of 30%. The refurbished plant and new machinery would last for many years. However, the remaining market for duffel canvas was small and it was not clear that additional orders could be obtained once the Forces contract was finished. The machinery was custom built and could be used only for duffel canvas. Its second-hand value at the end of five years was probably zero. 3:08 PM Wed Nov 27 61% Final_Project_CAPITAL BUDGETING_Revised 2 of 4 . Finally, Mrs. Duncan estimated that the investment in working capital would average 10% of sales. The working capital remains unchanged over the whole life of the project and will be fully recovered at the end of project life. The cost of capital (i.c., discount rate) for Dummont is 12% and the tax rate is 35% The 50% of capital gain is taxable. In other words, the half of tax on capital gain is waived. For example, the original land cost is $10,000, now its market value is $50.000, then the cash flow received by the seller is $43,000 = 50,000 - (50.000 - 10.000) x 0.5 * 35%) With all this information on hand, Mrs. Duncan constructed a spreadsheet to calculate the NPV of the duffel canvas project, assuming that Dummont's bid would be accepted by the Forces. Should Mrs. Duncan recommend submitting the bid to the Forces at the proposed price of $30 per yard? Requirements 1. Using an Excel Spreadsheet: Find the NPV, IRR, MIRR, PI, Payback, and Discounted Payback of Dummont's project using the pro forma financial statement method to determine cash flows. Enter the input variables in cells of their own at the top of the spreadsheet (so it is easier to do calculations). Set up the necessary equations by referencing to the input variable cells. The spreadsheet must be formula driven do not put any numbers in equations, only cell references Use Excel's built-in functions wherever possible (e.g. PV and IRR functions). 2. Breakeven Analysis Find the NPV breakeven points in the number of units, the sales price per unit, and the cost of capital (i.e., discount rate) for the base case. (Hint: use the Goal Seek tool built in Excel) 3. Scenario Analysis: Repeat the analysis for two different scenarios. (Use a separate spreadsheet) Consider the following independent scenarios for the company. For cach scenario, calculate the NPV, IRR, MIRR, PL, Payback, and Discounted Payback, and include these analyses in your final recommendation. Find the NPV breakeven points in the number of units sold, the sales price per unit, and the cost of capital (ie, discount rate) for the following two cases. (a) Mrs. Duncan had just finished reviewing the spreadsheet with her financial analysis of the project when another confidential envelope arrived from Dummont's CEO. It contained a firm offer from a Halifax real estate developer to purchase Dummont's land and plant TODAY for S1.5 million in cash (consider that the plant and land can still be sold at the end of years for only $600,000). Should Mrs. Duncan recommend submitting the bid to the Forces at the proposed price of $30 per yard anyway? (Hint: The opportunity cost of S1.million should be accounted for at the beginning of the project.)