Project details are attached. Thanks!!! FINC 302: Excel assignment

Upload an Excel file containing the following assignment to Connect before Wednesday, July 26, 2017. The assignment is worth 25 points and the assessment rubric can be found below.

Cameron Products International (CPI) is a multinational service firm with offices in North America, Europe, and Australia. The firms accounting department provided balances for the following accounts ($000):

Account name

2016

2015

Accounts payable

$175,200

$145,600

Accounts receivable

$402,000

$351,200

Accumulated

depreciation

$166,200

$146,200

Selling, G&A expenses

$330,300

$240,000

Cash & cash equivalents

$52,000

$57,600

Common stock

$460,000

$460,000

Cost of goods sold

$3,250,000

$2,864,000

Depreciation expense

$20,000

$18,900

Gross plant & equipment

$527,000

$491,000

Fixed expenses

$100,000

$100,000

Interest expense

$76,000

$62,500

Inventory

$836,000

$715,200

Long term debt

$424,612

$323,432

Notes payable

$225,000

$200,000

Other current liabilities

$140,000

$136,000

Retained earnings

$225,988

$203,768

Sales

$3,850,000

$3,432,000

Tax rate

40%

40%

Use one workbook, but different worksheets for each of the following:

Worksheet 1

Construct income statements for the years ended December 31, 2016 AND December 31, 2015, using formulas wherever possible.

Worksheet 2

Construct balance sheets dated December 31, 2016 AND December 31, 2015, using formulas wherever possible

Worksheet 3

Set up a ratio worksheet to calculate the following for 2016 and 2015: Current ratio

Quick ratio Debt ratio

Times interest earned Inventory turnover Total asset turnover Fixed asset turnover Profit margin

Return on assets Return on equity

Link all formulas to the source on previous worksheets.

Worksheet 4

Use financial functions for the following scenarios:

a.The capital budgeting committee at CPI is interested in a piece of

equipment currently priced at $400,000.However, the firms controller and CFO decide that now is not the right time to buy and the firm will wait six years. Historically, this type of equipment has appreciated in price each year at 3.5%. Assuming this trend continues, the decision makers at CPI want to know what this equipment will sell for in six years.

b.A CPI employee wants to buy a savings bond. The face value of the savings bond is $500, the amount the owner will receive in twenty years. The government is currently paying 4% per year on savings bonds. How much will it cost to buy this savings bond today?

c.Another employee would like to borrow $5,000 from his retirement fund to pay for his daughters tuition bill. He agrees to pay back the loan in a lump sum five years from now. The firms benefits coordinator states the payment will be $7,012.76. If he borrows the $5,000, what interest rate is he paying on his loan?

d.CPI just sold a product to a customer with the following payment plan:

$50,000 today, $25,000 next year, and $10,000 the following year. If CPA places the payments into an account earning 10% per year, how much money will be in the account after collecting the last payment?

e.CPI issued thirty-year semiannual bonds with a face value of $1,000. If the annual coupon rate is 14% and the current yield to maturity is 8%, what is the firms current price per bond?

Worksheet 5

CPI has the opportunity to invest in the following mutually exclusive projects:

Time of cash flow

Project A

Project B

Year 0

-$11,000

-$11,000

Year 1

$2,000

$4,000

Year 2

$3,000

$4,000

Year 3

$4,000

$4,000

Year 4

$5,000

$4,000

Year 5

$7,000

$4,000

Find the NPV and IRR for these projects using CPAs WACC of 9%. Construct NPV profiles (see page 287 of your textbook for a complete explanation) for both projects using discount rates of 1% through 15% at intervals of one percentage point.