Using the capital budgeting framework below, pick out relevant assumptions from the excel above and provide a discounted cash flow analysis to find out the NPV's of both options.

Capital Budgeting Framework

Structure	Notes
Revenue
Operating Expenses	Can be fixed and/or variable
EBITDA	Earnings Before Interest, Tax, Depreciation and Amortisation
Depreciation	Reduces Taxable Income, is a deduction companies are entitled to due to loss in value of their assets (not applied to all assets)
Gain or Loss on Sale	= SV BV where Book Value is the value of the asset on the books (Capital expenditure minus depreciation claimed up to and including the time of sale of the asset) if SV>BV its a gain, if SV
EBIT	Earnings Before Interest and Tax
Tax	Tax always has the opposite sign of EBIT: if EBIT is positive you must pay tax, so tax is a negative CF; if EBIT is negative it will reduce total tax payable by the firm in that year, so tax is a positive CF (if it makes it easier, think of it as receiving a refund, not technically correct, but it can help)
NOPAT	Net Operating Profit after Tax
Add Back Depreciation	Cancelling the effect of depreciation as it isnt a cash flow.
Add Back G/L on sale	Cancelling the effect of G/L on sale as it isnt a cash flow.
Cash Flow from Operations
Capital Expenditure	The cost of purchasing a new asset/undertaking new investment
Changes in Working Capital	Capital to meet short-term funding needs (provides liquidity)
Salvage Value	The cash flow you receive from selling the asset (not the same as G/L on sale)
Free Cash Flows
Discount Factor	Simply (1+r)-t
PV of Cash Flows	Simply FCF x DF
NPV	Net Present Value (simply sum of all PV of CFs)

Overview Following the recent state and federal government approvals of a coal mine in Queensland, a representative of the mining company has approached you to undertake some preliminary financial analysis on their behalf. The company, which plans to remain anonymous at this stage, wants you to evaluate the relative feasibility of two options for transporting coal from their mine pit to the railway line (approximately 80km) that will service the coal mine. With most mine-to-port/rail type analysis, the company understands that it can transport the coal by road (truck), conveyor or rail. However, given the distance to travel and limitations associated with the electricity infrastructure in the area, the company has limited its choices to either road or rail. Information on the road and rail transportation options is available in the Information' excel file provided. Task Based on the information provided, the representative has asked that you provide a detailed financial analysis of each transportation option and prepare a report that recommends a preferred option from those available. The following requests have been made about the analysis and report: The financial analysis is to be completed in Excel. The file is to be easily adjustable for different scenarios and all inputs must be in the one sheet called 'Assumptions' with the analysis of each option conducted on a separate sheet. The report is to be short (600 words +/- 20% tolerance) and written in a manner that can be understood by a person with a basic understanding of financial terminologies and analytical tools (keep it simple!). The report sections that must be included: o Summary: outlines the task and advises of the recommendation (this is not an introduction) o Methodology: must explain and justify the methodology employed. o Recommendations, recommends a preferred option from those available. In addition, includes some further analysis on whether an assumption of so 'wages' would affect the recommendation. This additional analysis is requested given recent developments with automated trucks and trains in the mining sector. NOTE: Your spreadsheet should be dynamic (all output cells are linked to input cells), therefore you don't need a separate spreadsheet showing the effect of 50 wages, just report your findings when you alter the inputs - remember to set it back to original inputs before submission. o Limitations: must identify key limitations with the analysis (this does not refer to possible limitations relating to the case). Data Notes Haulage Information Spreadsheet Mine Information Life (years) Production (tons p.a.). 32 7,200,000 Road Haulage Road Information Distance (kms) 80 It is assumed that all construction costs occur at the start of the project 1,000,000 200,000 Cost of Construction (per km) Annual Maintenance (per km) Life (years) Depreciation Salvage Value Straight Line Truck Information Number of Trucks Required Truck Type Outlay; Prime Mover (per truck) Outlay; Powered Dolly + 4 Haulage Trailers (per truck) Annual Fuel Costs (per truck) Annual Maintenance (per truck) Additional Maintenance Year 4 (per truck) Life (years) Depreciation Method Prime Mover + Powered Dolly + 4 Haulage Trailers 1,500,000 1,500,000 725,760 100,000 500,000 Straight Line Includes loading and unloading times Salvage Value (at end of life) Average Speed from and to mine (kmph) Travel Time; Mine to Rail and back (hours) Round Trips per day Total Distance Travelled (per day) Fuel Usage (litres per km) Fuel Cost (per litre) Truck Haulage Capacity (tons) Total Annual Haulage all Trucks 55 2.91 6.00 960.00 1.50 1.40 400 7,776,000 Driver Information Drivers Required (per truck, per day) Work Hours (per driver, per day) To esnure safety, drivers must accumulate 2 hours of break within every 12 hour work day. Driver Breaks (per driver, per day) Working Days 360 Driver's Weekly Working Hours Driver' Yearly Working Weeks (net of leave) Total Drivers Required Driver's Average Annual Salary (per driver) Total Driving Hours (per year, all trucks) 150,000 77,760 Additional Information It is assumed that the Working Capital is returned when the investment ends. Working Capital Tax Rate 2,000,000 30.00% Required Rate of Return 10.00% Rail Haulage Track Information Distance (kms) 80 It is assumed that all construction costs occur at the start of the project Cost of Construction (per km) Annual Maintenance (per km) Life (years) Depreciation Salvage Value 3,000,000 50,000 32 Straight Line 2 x Locomotive + 50 x Coal Carriages 2 50 Train Information Number of Trains Required Train Type Locomotives required (per train) Carriages required (per train) Outlay; Locomotive (per locomotive) Outlay; Carriages (per carriage) Annual Fuel Costs (per train) Annual Maintenance (per train) Year 8 Additional Maintenance (per train) Life (years) Depreciation Method Salvage Value (at end of life) Average Speed from and to mine (kmph) Travel Time; Mine to Rail and back (hours) 3,000,000 200,000 1,612,800 1,000,000 4,000,000 16 Straight Line 30.00 5.33 Includes loading and unloading times Round Trips per day Total Distance Travelled (per day) Fuel Usage (litres per km) Fuel Cost (per litre) Train Haulage Capacity (tons) Total Annual Haulage all Trains 4.00 640.00 5.00 1.40 5,000 7,200,000 Driver Information Two drivers to operate each train Drivers Required (per train, per day) Work Hours (pre driver, per day) 12 Driver's get breaks but this does not affect trains running given use of two drivers 360 40 Driver Breaks (per driver, per day) Working Days Driver's Weekly Working Hours Driver' Yearly Working Weeks (net of leave) Total Drivers Required Driver's Average Annual Salary Total Driving Hours (per year, all trains) 200,000 17,280 Additional Information It is assumed that the Working Capital is returned when the investment ends. Working Capital Tax Rate Required Rate of Return 1,000,000 30.00% 10.00% Overview Following the recent state and federal government approvals of a coal mine in Queensland, a representative of the mining company has approached you to undertake some preliminary financial analysis on their behalf. The company, which plans to remain anonymous at this stage, wants you to evaluate the relative feasibility of two options for transporting coal from their mine pit to the railway line (approximately 80km) that will service the coal mine. With most mine-to-port/rail type analysis, the company understands that it can transport the coal by road (truck), conveyor or rail. However, given the distance to travel and limitations associated with the electricity infrastructure in the area, the company has limited its choices to either road or rail. Information on the road and rail transportation options is available in the Information' excel file provided. Task Based on the information provided, the representative has asked that you provide a detailed financial analysis of each transportation option and prepare a report that recommends a preferred option from those available. The following requests have been made about the analysis and report: The financial analysis is to be completed in Excel. The file is to be easily adjustable for different scenarios and all inputs must be in the one sheet called 'Assumptions' with the analysis of each option conducted on a separate sheet. The report is to be short (600 words +/- 20% tolerance) and written in a manner that can be understood by a person with a basic understanding of financial terminologies and analytical tools (keep it simple!). The report sections that must be included: o Summary: outlines the task and advises of the recommendation (this is not an introduction) o Methodology: must explain and justify the methodology employed. o Recommendations, recommends a preferred option from those available. In addition, includes some further analysis on whether an assumption of so 'wages' would affect the recommendation. This additional analysis is requested given recent developments with automated trucks and trains in the mining sector. NOTE: Your spreadsheet should be dynamic (all output cells are linked to input cells), therefore you don't need a separate spreadsheet showing the effect of 50 wages, just report your findings when you alter the inputs - remember to set it back to original inputs before submission. o Limitations: must identify key limitations with the analysis (this does not refer to possible limitations relating to the case). Data Notes Haulage Information Spreadsheet Mine Information Life (years) Production (tons p.a.). 32 7,200,000 Road Haulage Road Information Distance (kms) 80 It is assumed that all construction costs occur at the start of the project 1,000,000 200,000 Cost of Construction (per km) Annual Maintenance (per km) Life (years) Depreciation Salvage Value Straight Line Truck Information Number of Trucks Required Truck Type Outlay; Prime Mover (per truck) Outlay; Powered Dolly + 4 Haulage Trailers (per truck) Annual Fuel Costs (per truck) Annual Maintenance (per truck) Additional Maintenance Year 4 (per truck) Life (years) Depreciation Method Prime Mover + Powered Dolly + 4 Haulage Trailers 1,500,000 1,500,000 725,760 100,000 500,000 Straight Line Includes loading and unloading times Salvage Value (at end of life) Average Speed from and to mine (kmph) Travel Time; Mine to Rail and back (hours) Round Trips per day Total Distance Travelled (per day) Fuel Usage (litres per km) Fuel Cost (per litre) Truck Haulage Capacity (tons) Total Annual Haulage all Trucks 55 2.91 6.00 960.00 1.50 1.40 400 7,776,000 Driver Information Drivers Required (per truck, per day) Work Hours (per driver, per day) To esnure safety, drivers must accumulate 2 hours of break within every 12 hour work day. Driver Breaks (per driver, per day) Working Days 360 Driver's Weekly Working Hours Driver' Yearly Working Weeks (net of leave) Total Drivers Required Driver's Average Annual Salary (per driver) Total Driving Hours (per year, all trucks) 150,000 77,760 Additional Information It is assumed that the Working Capital is returned when the investment ends. Working Capital Tax Rate 2,000,000 30.00% Required Rate of Return 10.00% Rail Haulage Track Information Distance (kms) 80 It is assumed that all construction costs occur at the start of the project Cost of Construction (per km) Annual Maintenance (per km) Life (years) Depreciation Salvage Value 3,000,000 50,000 32 Straight Line 2 x Locomotive + 50 x Coal Carriages 2 50 Train Information Number of Trains Required Train Type Locomotives required (per train) Carriages required (per train) Outlay; Locomotive (per locomotive) Outlay; Carriages (per carriage) Annual Fuel Costs (per train) Annual Maintenance (per train) Year 8 Additional Maintenance (per train) Life (years) Depreciation Method Salvage Value (at end of life) Average Speed from and to mine (kmph) Travel Time; Mine to Rail and back (hours) 3,000,000 200,000 1,612,800 1,000,000 4,000,000 16 Straight Line 30.00 5.33 Includes loading and unloading times Round Trips per day Total Distance Travelled (per day) Fuel Usage (litres per km) Fuel Cost (per litre) Train Haulage Capacity (tons) Total Annual Haulage all Trains 4.00 640.00 5.00 1.40 5,000 7,200,000 Driver Information Two drivers to operate each train Drivers Required (per train, per day) Work Hours (pre driver, per day) 12 Driver's get breaks but this does not affect trains running given use of two drivers 360 40 Driver Breaks (per driver, per day) Working Days Driver's Weekly Working Hours Driver' Yearly Working Weeks (net of leave) Total Drivers Required Driver's Average Annual Salary Total Driving Hours (per year, all trains) 200,000 17,280 Additional Information It is assumed that the Working Capital is returned when the investment ends. Working Capital Tax Rate Required Rate of Return 1,000,000 30.00% 10.00%