The management of Blue Mountain Mining Ltd have requested that you provide a detailed financial analysis of each train option and prepare a report that recommends a preferred option. 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 train type 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 analytical tools.
- The report sections to include: Summary, Methodology, Recommendations and Limitations.
o Summary: outlines the task and advises of the recommendation.
o Methodology: must explain and justify the methodology employed.
o Recommendations: recommends a preferred option. In addition, includes analysis on the sensitivity of the decision to the discount rate (e.g. try between 5% and 15%).
o Limitations: must identify key limitations with the analysis.
EFB210: FINANCE 1, Semester 2 2018 Capital Budgeting Report and Analysis ___________________________________________________________________________________ General Information Marks:25 Weight:25% Format:Report and Analysis Due:8 October 2018 (end of day, 11:59pm) Submission: Submit report and Excel analysis files by uploading your files to the Finance 1 Assignment portal in Blackboard. Note that when saving and submitting the report and analysis files, please ensure that: · You upload the correct files and that the files are not corrupted in any way. As markers, we can only mark what is provided to us at the time of submission. · Each filename must include your name and student number, and the file extensions must be either .docx (report) or .xlsx (analysis). · For example, if Brad Pitt was studying Finance 1, he would save his report and analysis as: · Brad Pitt 1234567 Report.docx · Brad Pitt 1234567 Analysis.xlsx · Please note that file types that cannot be opened in Word or Excel, e.g. .numbers files, are not accepted. · Part of the assignment’s CRA relates to correct filenames. __________________________________________________________________________________ Background Information[footnoteRef:1] [1: Disclaimer: This case study is stylised example designed to provide a realistic capital budgeting setting. Please note that information presented here is fictitious and may not necessarily reflect the actual reality of the problem.] Blue Mountain Mining Ltd is evaluating its proposed iron ore mine for the Pilbara region of West Australia. The mine’s primary operations are the extraction of iron ore and its transportation to port. Given that the mine is 240 kilometres to the nearest port, ore will be transported using three (3) heavy haul trains where each train comprises two (2) locomotives and 208 ore carriages. It is anticipated that it will take one day for each train to complete the return trip from the mine to the port; including loading and unloading. As part of the investment proposal, Blue Mountain Mining is considering whether it should invest in autonomous trains (do not require drivers). The key advantages of autonomous trains over standard non-autonomous trains (require drivers) include: · Reduced wages expense as there are no train drivers · Reduced fuel and maintenance costs as the autonomous system operates trains more efficiently. However, disadvantages include: · Additional capital outlay on rail line and trains · Requires additional technicians trained in autonomous technologies for rail line and train maintenance. The company has provided detailed information related to the autonomous and non-autonomous options in the ‘Train Information’ spreadsheet. Task The management of Blue Mountain Mining Ltd have requested that you provide a detailed financial analysis of each train option and prepare a report that recommends a preferred option. 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 train type 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 analytical tools. · The report sections to include: Summary, Methodology, Recommendations and Limitations. · Summary: outlines the task and advises of the recommendation. · Methodology: must explain and justify the methodology employed. · Recommendations: recommends a preferred option. In addition, includes analysis on the sensitivity of the decision to the discount rate (e.g. try between 5% and 15%). · Limitations: must identify key limitations with the analysis. EFB210 Finance 1 (Sem. 2, 2018) Assignment Criteria Sheet, Report (25%) Student Name: _______________________________________Student No: ______________________ Criteria 7 6 5 4<4 knowledge [ks 1.1: demonstrate and apply integrated discipline knowledge across the broad field of business with depth in one or more core business disciplines] select and describe your choice of methodology appropriate for the task methodology chosen is appropriate to task and described in full detail; full justification of choice is provided methodology chosen is appropriate to task and described in adequate detail; good justification of choice is provided methodology chosen is appropriate to task though description lacks detail in parts; adequate justification of choice is provided methodology chosen is appropriate to task but description is incorrect or generally lacks detail; attempt made to justify choice chosen methodology is not appropriate for task; limited and incorrect description of methodology or justification of choice is provided technical and technological skills [ks 1.2: apply technical and technological skills appropriate and effective for real world business purposes and contexts] correct implementation of methodology all steps of chosen methodology correctly applied; correct answer derived from the data or only a minor calculation error methodology applied but containing an error; an error in calculations may be present methodology applied but two errors in calculation and/or methodology are present numerous errors are present in methodology and/or calculations methodology not implemented or is applied incorrectly; major calculation errors are present demonstrated ability to use excel effectively all references are correct; layout and formatting of spreadsheet are logical and professional. uses correct filename. references, layout, formatting and filename are largely correct and professional, though a minor error may be present references, layout, formatting and filename are largely correct and professional, though a few minor errors may be present references, layout, formatting and filename are largely correct and professional, though several errors may be present spreadsheet not provided or contains major errors; spreadsheet layout and formatting contain major errors or are not appropriate higher order thinking skills [ho 2.2: exercise independent judgement in applying knowledge and skills for effective planning, problem solving and decision making in diverse contexts] provide sound conclusion based on correct interpretation of analysis results conclusions address all aspects of task, are coherent, correctly derived from analysis, and are explained in detail in the context of relevant key limitations conclusions address all aspects of task, are coherent, correctly derived from analysis, and explained in detail; most key limitations have been identified and adequately explained conclusions address most aspects of task, correctly derived from analysis, though may lack some detail; most key limitations have been identified but lack some detail in parts. conclusions address most aspects of task, although link to underlying analysis may be unclear in parts; identifies limitations that are not relevant and may not adequately explain these limitations or key relevant limitations. report fails to include conclusions addressing the task, or conclusions not supported by analysis; identifies largely irrelevant limitations. professional communication [pc 3.1: use information literacy skills, and communicate effectively and professionally in written forms for diverse purposes and contexts] communicate clearly, concisely and professionally report is comprehensive and succinctly written with a logical structure and clear expression; grammar, punctuation and syntax consistently correct; writing style is appropriate for audience and task; report is an original piece of work report is comprehensive with a logical structure and clear expression; grammar, punctuation, and syntax are mostly correct; writing style is appropriate for audience and task; report is an original piece of work report addresses most key aspects of the task and has a logical structure; grammar, punctuation and syntax are mostly correct, though expression may lack some clarity; writing style is appropriate for audience and task; report is an original piece of work report addresses some aspects of the task adequately and has logical structure; errors in grammar, punctuation, and syntax are present and expression lacks clarity in parts; writing style is appropriate for audience and/or task; report is an original piece of work report fails to communicate an effective response to the task; significant errors in grammar, punctuation, syntax or clarity; style not appropriate for audience or task; report is not an original piece of work total: efb210 finance 1: excel formatting guide purpose: outline excel formatting standards for the finance 1 assignment. set by:mark doolan guiding principles: be consistent and keep it simple font: · calibri 11 point font (excel default) preferred input cells: · these are the cells where we input values. given that the inputs drive the spreadsheets output, we want to highlight them for ease of reference. as such, highlight with yellow fill. one exception is that you may choose ‘no fill’ for an input cells that are not variable. for example, all input cells (primary data entry cells) in figure 1 have been highlighted yellow except the year references that form the column headings. output cells: · do not contain any hard inputs (e.g. written in variable values – number such as 365 that don’t change are not considered inputs), which means they are made up entirely of cell references [e.g. = a2 * (a3 + a4)]. · use standard font with no fill. can use red for negative values (occurs automatically for some formats, e.g. dollar or accounting number formats). · if you want to highlight a particular value, use bold. figure 1: excel table formatting example table formatting: · top row of table headings 'bold' and 'centred', may left justify left most cell of column headings - refer figure above · try to only use horizontal lines – in design less is more, but don’t confuse this with the basic economic premise that more is more.... · for column headings, use a single line on top and double line at the bottom · last row ends with single line on the bottom · where appropriate use other horizontal lines · may use double lines to indicate sum graph formatting: · use the right graph for the particular form of analysis · include meaningful headings and axis titles (note i haven’t included y-axis label in figure 2 because values are self explanatory) knowledge="" [ks="" 1.1:="" demonstrate="" and="" apply="" integrated="" discipline="" knowledge="" across="" the="" broad="" field="" of="" business="" with="" depth="" in="" one="" or="" more="" core="" business="" disciplines]="" select="" and="" describe="" your="" choice="" of="" methodology="" appropriate="" for="" the="" task="" methodology="" chosen="" is="" appropriate="" to="" task="" and="" described="" in="" full="" detail;="" full="" justification="" of="" choice="" is="" provided="" methodology="" chosen="" is="" appropriate="" to="" task="" and="" described="" in="" adequate="" detail;="" good="" justification="" of="" choice="" is="" provided="" methodology="" chosen="" is="" appropriate="" to="" task="" though="" description="" lacks="" detail="" in="" parts;="" adequate="" justification="" of="" choice="" is="" provided="" methodology="" chosen="" is="" appropriate="" to="" task="" but="" description="" is="" incorrect="" or="" generally="" lacks="" detail;="" attempt="" made="" to="" justify="" choice="" chosen="" methodology="" is="" not="" appropriate="" for="" task;="" limited="" and="" incorrect="" description="" of="" methodology="" or="" justification="" of="" choice="" is="" provided="" technical="" and="" technological="" skills="" [ks="" 1.2:="" apply="" technical="" and="" technological="" skills="" appropriate="" and="" effective="" for="" real="" world="" business="" purposes="" and="" contexts]="" correct="" implementation="" of="" methodology="" all="" steps="" of="" chosen="" methodology="" correctly="" applied;="" correct="" answer="" derived="" from="" the="" data="" or="" only="" a="" minor="" calculation="" error="" methodology="" applied="" but="" containing="" an="" error;="" an="" error="" in="" calculations="" may="" be="" present="" methodology="" applied="" but="" two="" errors="" in="" calculation="" and/or="" methodology="" are="" present="" numerous="" errors="" are="" present="" in="" methodology="" and/or="" calculations="" methodology="" not="" implemented="" or="" is="" applied="" incorrectly;="" major="" calculation="" errors="" are="" present="" demonstrated="" ability="" to="" use="" excel="" effectively="" all="" references="" are="" correct;="" layout="" and="" formatting="" of="" spreadsheet="" are="" logical="" and="" professional.="" uses="" correct="" filename.="" references,="" layout,="" formatting="" and="" filename="" are="" largely="" correct="" and="" professional,="" though="" a="" minor="" error="" may="" be="" present="" references,="" layout,="" formatting="" and="" filename="" are="" largely="" correct="" and="" professional,="" though="" a="" few="" minor="" errors="" may="" be="" present="" references,="" layout,="" formatting="" and="" filename="" are="" largely="" correct="" and="" professional,="" though="" several="" errors="" may="" be="" present="" spreadsheet="" not="" provided="" or="" contains="" major="" errors;="" spreadsheet="" layout="" and="" formatting="" contain="" major="" errors="" or="" are="" not="" appropriate="" higher="" order="" thinking="" skills="" [ho="" 2.2:="" exercise="" independent="" judgement="" in="" applying="" knowledge="" and="" skills="" for="" effective="" planning,="" problem="" solving="" and="" decision="" making="" in="" diverse="" contexts]="" provide="" sound="" conclusion="" based="" on="" correct="" interpretation="" of="" analysis="" results="" conclusions="" address="" all="" aspects="" of="" task,="" are="" coherent,="" correctly="" derived="" from="" analysis,="" and="" are="" explained="" in="" detail="" in="" the="" context="" of="" relevant="" key="" limitations="" conclusions="" address="" all="" aspects="" of="" task,="" are="" coherent,="" correctly="" derived="" from="" analysis,="" and="" explained="" in="" detail;="" most="" key="" limitations="" have="" been="" identified="" and="" adequately="" explained="" conclusions="" address="" most="" aspects="" of="" task,="" correctly="" derived="" from="" analysis,="" though="" may="" lack="" some="" detail;="" most="" key="" limitations="" have="" been="" identified="" but="" lack="" some="" detail="" in="" parts.="" conclusions="" address="" most="" aspects="" of="" task,="" although="" link="" to="" underlying="" analysis="" may="" be="" unclear="" in="" parts;="" identifies="" limitations="" that="" are="" not="" relevant="" and="" may="" not="" adequately="" explain="" these="" limitations="" or="" key="" relevant="" limitations.="" report="" fails="" to="" include="" conclusions="" addressing="" the="" task,="" or="" conclusions="" not="" supported="" by="" analysis;="" identifies="" largely="" irrelevant="" limitations.="" professional="" communication="" [pc="" 3.1:="" use="" information="" literacy="" skills,="" and="" communicate="" effectively="" and="" professionally="" in="" written="" forms="" for="" diverse="" purposes="" and="" contexts]="" communicate="" clearly,="" concisely="" and="" professionally="" report="" is="" comprehensive="" and="" succinctly="" written="" with="" a="" logical="" structure="" and="" clear="" expression;="" grammar,="" punctuation="" and="" syntax="" consistently="" correct;="" writing="" style="" is="" appropriate="" for="" audience="" and="" task;="" report="" is="" an="" original="" piece="" of="" work="" report="" is="" comprehensive="" with="" a="" logical="" structure="" and="" clear="" expression;="" grammar,="" punctuation,="" and="" syntax="" are="" mostly="" correct;="" writing="" style="" is="" appropriate="" for="" audience="" and="" task;="" report="" is="" an="" original="" piece="" of="" work="" report="" addresses="" most="" key="" aspects="" of="" the="" task="" and="" has="" a="" logical="" structure;="" grammar,="" punctuation="" and="" syntax="" are="" mostly="" correct,="" though="" expression="" may="" lack="" some="" clarity;="" writing="" style="" is="" appropriate="" for="" audience="" and="" task;="" report="" is="" an="" original="" piece="" of="" work="" report="" addresses="" some="" aspects="" of="" the="" task="" adequately="" and="" has="" logical="" structure;="" errors="" in="" grammar,="" punctuation,="" and="" syntax="" are="" present="" and="" expression="" lacks="" clarity="" in="" parts;="" writing="" style="" is="" appropriate="" for="" audience="" and/or="" task;="" report="" is="" an="" original="" piece="" of="" work="" report="" fails="" to="" communicate="" an="" effective="" response="" to="" the="" task;="" significant="" errors="" in="" grammar,="" punctuation,="" syntax="" or="" clarity;="" style="" not="" appropriate="" for="" audience="" or="" task;="" report="" is="" not="" an="" original="" piece="" of="" work="" total:="" efb210="" finance="" 1:="" excel="" formatting="" guide="" purpose:="" outline="" excel="" formatting="" standards="" for="" the="" finance="" 1="" assignment.="" set="" by:="" mark="" doolan="" guiding="" principles:="" be="" consistent="" and="" keep="" it="" simple="" font:="" ·="" calibri="" 11="" point="" font="" (excel="" default)="" preferred="" input="" cells:="" ·="" these="" are="" the="" cells="" where="" we="" input="" values.="" given="" that="" the="" inputs="" drive="" the="" spreadsheets="" output,="" we="" want="" to="" highlight="" them="" for="" ease="" of="" reference.="" as="" such,="" highlight="" with="" yellow="" fill.="" one="" exception="" is="" that="" you="" may="" choose="" ‘no="" fill’="" for="" an="" input="" cells="" that="" are="" not="" variable.="" for="" example,="" all="" input="" cells="" (primary="" data="" entry="" cells)="" in="" figure="" 1="" have="" been="" highlighted="" yellow="" except="" the="" year="" references="" that="" form="" the="" column="" headings.="" output="" cells:="" ·="" do="" not="" contain="" any="" hard="" inputs="" (e.g.="" written="" in="" variable="" values="" –="" number="" such="" as="" 365="" that="" don’t="" change="" are="" not="" considered="" inputs),="" which="" means="" they="" are="" made="" up="" entirely="" of="" cell="" references="" [e.g.="A2" *="" (a3="" +="" a4)].="" ·="" use="" standard="" font="" with="" no="" fill.="" can="" use="" red="" for="" negative="" values="" (occurs="" automatically="" for="" some="" formats,="" e.g.="" dollar="" or="" accounting="" number="" formats).="" ·="" if="" you="" want="" to="" highlight="" a="" particular="" value,="" use="" bold.="" figure="" 1:="" excel="" table="" formatting="" example="" table="" formatting:="" ·="" top="" row="" of="" table="" headings="" 'bold'="" and="" 'centred',="" may="" left="" justify="" left="" most="" cell="" of="" column="" headings="" -="" refer="" figure="" above="" ·="" try="" to="" only="" use="" horizontal="" lines="" –="" in="" design="" less="" is="" more,="" but="" don’t="" confuse="" this="" with="" the="" basic="" economic="" premise="" that="" more="" is="" more....="" ·="" for="" column="" headings,="" use="" a="" single="" line="" on="" top="" and="" double="" line="" at="" the="" bottom="" ·="" last="" row="" ends="" with="" single="" line="" on="" the="" bottom="" ·="" where="" appropriate="" use="" other="" horizontal="" lines="" ·="" may="" use="" double="" lines="" to="" indicate="" sum="" graph="" formatting:="" ·="" use="" the="" right="" graph="" for="" the="" particular="" form="" of="" analysis="" ·="" include="" meaningful="" headings="" and="" axis="" titles="" (note="" i="" haven’t="" included="" y-axis="" label="" in="" figure="" 2="" because="" values="" are="" self="">