EFB210 Finance 1 Capital Budgeting Report and Analysis

efb210_finance_1_questions.zip

Home >Business & Finance homework help >EFB210 Finance 1 Capital Budgeting Report and Analysis

Analysis of Well Types.xlsx

Information

Analysis of Well Types: prepared by finance department
General Information on Gas Reserve and Extraction Process
Size of Gas Reserve (gigajoules)	770,000,000
Extraction Process	Drill multiple wells around site and extract as much gas as drilling technique permits. That is, while there might be a certain reserve size, the total amount of gas that can be extracted depends on the drilling technique employed. Further, contractual constraints mean that only one type of well can be operated at any point in time, i.e. you cannot operate A and B wells together.
Sale of Gas	Primitive has entered into an agreement where all gas extracted is sold at the wellhead (once it makes the surface). The contracted price schedule is described below.
Information for Option 1: A-Type Wells
Extraction Process	Extract gas over 12 years by drilling 3 A-Type wells per year up to and including year 8. Capital outlays for each well occur at the start of the period in which the well is drilled. Revenues and expenses for the wells are assumed to occur at the end of each year.
Life of each well (years)	4
Production per well (gigajoules per year)	7,000,000
Capital investment per well	$ 10,000,000.00
Maintenance cost per well in operation (per year)	$ 500,000.00
Variable cost per gigajoule of production	$ 1.00
Depreciation Method	Straight Line
Salvage Value per well	$ - 0
Shut down costs per well	$ 1,000,000.00
Information for Option 2: B-Type Wells
Extraction Process	Extract gas over 12 years by drilling 3 B-Type wells per year up to and including year 9. Capital outlays for each well occur at the start of the period in which the well is drilled. Revenue and expenses for the wells are assumed to occur at the end of each year.
Life of each well (years)	3
Production per well (gigajoules per year)	8,500,000
Capital investment per well	$ 12,000,000.00
Maintenance cost per well in operation (per year)	$ 750,000.00
Variable cost per gigajoule of production	$ 0.80
Depreciation Method	Straight Line
Salvage Value per well	$ - 0
Shut down costs per well	$ 1,000,000.00
Information Pertinent to Both Options
Price Information
Description	Gas is priced at the wellhead because all gas has been forward sold to a gas processor. The agreement is for price to increase annually by 2.50% per year (an inflation adjustment). The price for the first year of gas production is set at $3.50 per gigajoule.
Price (for 1st Year)	$ 3.50
Price Inflator (to be compounded)	2.50%
Tax Information
State Royalties (Note: Royalties are 10% of sales revenue and are tax deductible.)	10.00%
Company Tax	30.00%
Information for Discounting Cash Flows
Discount Rate	10.00%

A well

A-Type Wells
Price	3.50	3.59	3.68	3.77
Year	- 0	1	2	3	4
Revenue	24,500,000	25,112,500	25,740,312	26,383,820
Expenses	- 7,000,000	- 7,000,000	- 7,000,000	- 7,000,000
Maintainence	- 500,000	- 500,000	- 500,000	- 500,000
Dismantling Costs	- 1,000,000
State Royalties	- 2,450,000	- 2,511,250	- 2,574,031	- 2,638,382
EBITDA	14,550,000	15,101,250	15,666,281	15,245,438
Depr	- 2,500,000	- 2,500,000	- 2,500,000	- 2,500,000
G/L	- 0
EBIT	12,050,000	12,601,250	13,166,281	12,745,438
Tax	- 3,615,000	- 3,780,375	- 3,949,884	- 3,823,631
NOPAT	8,435,000	8,820,875	9,216,397	8,921,807
add back Depr.	2,500,000	2,500,000	2,500,000	2,500,000
add back G/L	- 0	- 0	- 0	- 0
Cash Flow from Ops	10,935,000	11,320,875	11,716,397	11,421,807
Cap Ex	- 10,000,000
Salvage	- 0
Working Capital	- 0	- 0
CFt	- 10,000,000	10,935,000	11,320,875	11,716,397	11,421,807
Disc. Fact	1.0000	0.9091	0.8264	0.7513	0.6830
PV of CFt	- 10,000,000	9,940,909	9,356,095	8,802,702	7,801,248
NPV	25,900,954
AE	8,170,995

B well

B-Type Wells
Price	3.50	3.59	3.68
Year	- 0	1	2	3
Revenue	29,750,000	30,493,750	31,256,094
Expenses	- 6,800,000	- 6,800,000	- 6,800,000
Maintainence	- 750,000	- 750,000	- 750,000
Dismantling Costs	- 1,000,000
State Royalties	- 2,975,000	- 3,049,375	- 3,125,609
EBITDA	19,225,000	19,894,375	19,580,484
Depr	- 4,000,000	- 4,000,000	- 4,000,000
G/L	- 0
EBIT	15,225,000	15,894,375	15,580,484
Tax	- 4,567,500	- 4,768,312	- 4,674,145
NOPAT	10,657,500	11,126,062	10,906,339
add back Depr.	4,000,000	4,000,000	4,000,000
add back G/L	- 0	- 0	- 0
Cash Flow from Ops	14,657,500	15,126,062	14,906,339
Cap Ex	- 12,000,000
Salvage
Working Capital	- 0
CFt	- 12,000,000	14,657,500	15,126,062	14,906,339
Disc. Fact	1.0000	0.9091	0.8264	0.7513
PV of CFt	- 12,000,000	13,325,000	12,500,878	11,199,353
NPV	25,025,231
AE	10,063,016

EFB210 Finance 1, Assignment 14S2.docx

Capital Budgeting Report and Analysis

___________________________________________________________________________________

General information

Format: Report and Analysis

Submission: Submit report with CRA matrix to Assignment Minder. Note that you need to attach the Assignment Minder ‘assignment cover sheet’ to the front of the document wallet.

Submit Excel analysis file by either:

· Submitting a USB or CD containing the Excel file with your report to Assignment Minder. If using this method, you must ensure that the USB or CD is securely attached inside the document wallet.

When saving the Excel file, please ensure that:

· the file name includes your name and student number.

· the file is a .xls or .xlsx file (mac files, such as .numbers are not accepted).

__________________________________________________________________________________

Outline

Primitive Energy owns several coal seam gas reserves in south-west Queensland. As a relatively minor player in the Queensland Liquefied Natural Gas (LNG) market, Primitive does not have the capacity to transfer and process the gas for sale to international buyers. Instead, Primitive simply extracts the gas and then sells it immediately (at the well-head, which is at the surface) to one of the major gas companies operating in the area. Recently, Primitive entered into a contract to sell gas from one of its reserves for the next 12 years. The contract stipulates that the price is set at $3.50 per gigajoule in the first year and that the price will increase by 2.50% per year to adjust for inflation.

With this contract in place, Primitive’s management are currently trying to determine the optimal well type for extracting the gas. The choice has been narrowed to two types:

A-Type Wells: Drill 3 A-Type wells per year up to and including the beginning of the 9th year. Wells have a 4 year life. The project will operate for 12 years.

B-Type Wells: Drill 3 B-Type wells per year up to and including the beginning of the 10th year. Wells have a 3 year life. The project will operate for 12 years.

Primitive’s finance department conducted a preliminary discounted cash flow analysis of the wells (found in Analysis of Well Types.xlsx). Based on the annual equivalent (AE) figure for each type of well they have recommended that the B-Type well be selected because it generates the highest AE.

A member of the management team, who studied EFB210 Finance 1, believes that the analysis is flawed (wrong) and should be re-done. They have asked that you complete the following task.

Task

Provide a detailed financial analysis that reports the net present value (NPV) that each well type generates over the full life of the project. In addition, you are to write a detailed but concise report. In completing this task, the manager has requested the following:

· 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 well conducted on separate sheets.

· 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. It should have the following sections:

· Summary

· Methodology

· Recommendations

· Limitations

The ‘Methodology’ section must explain how the NPV was calculated over the project’s total life and must justify why this methodology is preferred over that used by the finance department.

· In making recommendations, the analysis and report must determine at what level of variable costs do B-Type wells become equivalent to A-type wells. Note: in doing this hold the variable costs of the A-type well fixed.

In order to prepare your analysis and report, your can refer to the information provided in the Analysis of Well Types xlsx file provided.

EFB210 Finance 1, Excel Formatting for Assignment.docx

Excel Formatting Guide

Purpose: Outline Excel formatting standards for the Finance Assignment.

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 cell that is also a column heading for a table. 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)

· Ensure that headings, plots and other information don’t overlap each other.

· Choose an appropriate font

Figure 2: Comparative Graph of Share Price

Assumptions Sheet for Capital Budgeting Assignment

· All inputs for the capital budgeting assignment must be on the one separate sheet.

P017.39

g4.00%

Ke15.00%

Table 1: Share Price Calculations with Standard Model

Year01234567

Dividend- - - 1.00 2.00 3.00 3.12

Pn28.36

CF- - - 1.00 2.00 31.36

Disc. Factor1.00000.86960.75610.65750.57180.4972

PV of CF- - - 0.6575 1.1435 15.5933

Lesson 1 - Intro

What is a spreadsheet?
it's a repository of information
analytical tool
How does it Work?
input information into cells
35.81
cell A8 has the number 35.81 recorded
other than a number, doesn't really mean a lot
So let's think of the spreadsheet as a table
include headings
input information
perform analysis
Shares	Price	Number	Value
BHP	$ 35.81	1,000	$ 35,810
CBA	$ 47.45	2,000	$ 94,900
$ 130,710
What about graphs?
Lots
Select the right graph (column, line, pie, etc...)
Label (Heading, axes titles, axes values and format consistently)
Should I format my spreadsheets?
Yes
In Finance 1 we have set guidelines on which we are marked
Input Cells - generally yellow - but where they form a column or row heading within a table, you may prefer to use 'no fill'.
Outputs - Generally leave black, but may use red for negative values
Be consistent with formatting of values and apply common sense
Top row of table headings 'Bold' and 'Centred', may left justify left most cell of column headings - refer table above
Try to only use horizontal lines in table - in design less is more, but in finance more is more
^ Column heading use single line on top and double line on bottom
^ Last row ends with single line on the bottom
^ where appropriate use other horizontal lines
^ may use double lines to indicate sum
Do we have a guiding principle?
Kept it Simple

BHP and CBA Share Price

Price BHP CBA 35.81 47.45

Share Ticker

Portfolio Allocations by Market Value

BHP CBA 35810 94900

BHP and CBA Share Price

Price BHP CBA 35.81 47.45

Share Ticker

Portfolio Allocations by Market Value

BHP CBA 35810 94900

Lesson 2 - Formulas & Functions

More on functionality of Excel
What's a Workbook?
The xlsx file you're working in.
What's a Sheet?
Within a workbook you have sheets, which is what I'm working in now. Each sheet is indicated by tabs at the bottom (Lesson 1, Lesson 2, ...)
Sheets provide lots of power in terms of manage information, and we really don't use this in Finance 1, but it's good for future reference
Formulas
Performs calculations, start with an = sign
e.g.
10	2
Add	12	Note how we reference to input cells
Subtract	8	By linking to input cells, formulas will update automatically to any changes
Multiply	20
Divide	5
Power	100
Negative Power	0.01
Somewhat Complex	14	Use brackets when order of operation does not follow BOMDAS
Functions
2	2	2	2	2	2	2	2
Add	16	Tedious!
Sum	16
Prod	256
Count	8
Average	2
Stdev	0
Logical Function
if	YES
Some Finance Examples
90-day bank bill, FV = 100,000, i = 9%
P0	97,829.00	P0 = FV/(1+it)
FV	100,000.00
i	9.00%
t	0.25
10-year bond, FV = 100, C = 10%, Kd = 9%
P0	88.700	88.700
FV	100.00
C	10.00
T	10.00
Kd	12.00%
2 ways to calculate price
1. Long Way P0 = ∑CFt*(1+Kd)^-t
Year	0	1	2	3	4	5	6	7	8	9	10
Coupon	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00
FV	100.00
CF	- 0	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	110.00
Disc. Factor	1.0000	0.8929	0.7972	0.7118	0.6355	0.5674	0.5066	0.4523	0.4039	0.3606	0.3220
PV of CF	- 0	8.93	7.97	7.12	6.36	5.67	5.07	4.52	4.04	3.61	35.42
2. Easier Way, P0 = CxPVIFA(T,Kd) + FV(1+Kd)^-T
Stock, dividends for years 3, 4 and 5 = $1, $2 and $3 respectively.
All dividends after year 5 are expected to grow at 3% p.a. Indefinitely. Ke = 15%
P0	17.39
g	4.00%
Ke	15.00%
Table 1: Share Price Calculations with Standard Model
Year	0	1	2	3	4	5	6	7
Dividend	- 0	- 0	- 0	1.00	2.00	3.00	3.12
Pn	28.36	Pn = Dn+1/(Ke-g)
CF	- 0	- 0	- 0	1.00	2.00	31.36
Disc. Factor	1.0000	0.8696	0.7561	0.6575	0.5718	0.4972
PV of CF	- 0	- 0	- 0	0.6575	1.1435	15.5933

Lesson 3 - Some Skills

Some skills to improve your Excel experience
Hot Keys
Makes life easier, usually involves Ctrl Key
Example
Bold	Ctrl B	10
Copy & Paste	Ctrl C & Ctrl V	10	Copies value or formula and formatting
Move Around	Ctrl + Arrow
Highlight	Shift + Arrow	10
Highlight Section	Ctrl + Shift + Arrow	10
Copy Down or Right	Highlight then Ctrl D or Ctrl R	10
View Formula	Select cell then F2	50	There are degrees of absoluting
Absolutes	View Formula, select reference then F4	100	Note still starts at C7 but now ends at C12
Example
10-year bond, FV = 100, C = 10%, Kd = 9%
P0	88.700
FV	100.00
C	10.00
T	10.00
Kd	12.00%
2 ways to calculate price
1. Long Way P0 = ∑CFt*(1+Kd)^-t
Year	0	1	2	3	4	5	6	7	8	9	10
Coupons	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00
FV	100.00
CF	- 0	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	10.00	110.00
Disc. Fact.	1.0000	0.8929	0.7972	0.7118	0.6355	0.5674	0.5066	0.4523	0.4039	0.3606	0.3220
PV of CF	- 0	8.93	7.97	7.12	6.36	5.67	5.07	4.52	4.04	3.61	35.42
Printing Tips
Set print areas
1. highlight the part of the spreadsheet you want to print
2. select Page Layout ribbon
3. select Print Area icon and Set Print Area
4. if you want, select Orientation and change page layout to landscape
5. what about fitting tables within a certain page
5.1 Select View Ribbon
5.2 Select Page Break View (Looks different, but just highlights the pages that will be printed, their orientation and location of page breaks)
5.3 Clicking and grabbing dotted blue line allows you to move page breaks
5.4 You can also add page breaks

$-$5.00 $10.00 $15.00 $20.00 $25.00 $30.00 $35.00 $40.00 $45.00 $50.00 BHPCBAShare Ticker

BHP and CBA Share Price