Explore BrainMass
Share

Spreadsheet problem

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

See attached files.

Using the spreadsheet and the data therein, calculate the values for the 30 cells in the three boxes at the bottom of the sheet.

First calculate the FCFs, starting with Operating Income After Tax (sometimes called NOPAT, the acronym for Net Operating Profit After Tax) and make the necessary calculations to arrive at the Free Cash Flows (FCFs) for the years 2001-2006. Assume that Free Cash Flows beyond 2006 grow at a rate of 6%, using the FCF in 2006 as the base.

Then calculate a terminal value two ways - using a constant growth rate DCF model and a TEV/EBITDA multiple. For the latter, use a multiple of 7.3X, which should tie back to the Comparable Company data. Next, use the data provided in the left-hand column at the bottom of the page to calculate the estimated WACC for ABC. Finally, calculate the PV of the two sets of FCFs ( FCFs for 2001-2006 and the two terminal values). How do these values compare to one another and to the valuations obtained from the Comparable Company Valuation Analysis?

© BrainMass Inc. brainmass.com October 24, 2018, 5:25 pm ad1c9bdddf
https://brainmass.com/business/mergers-and-acquisitions/spreadsheet-problem-9894

Attachments

$2.19
See Also This Related BrainMass Solution

Intermediate Accounting, Kieso, Weygandt, and Warfield, 14th edition; Wiley.

Accounting 350 - Computer Spreadsheet Assignment #2 (Chapter 7)

Dusty Horseman Corporation provides the following aging schedule of accounts receivable at December 31, 2011. The balance in the Allowance for Doubtful Accounts was $175,000 at January 1. During the year, $159,000 of bad debts were written off.

Classification by Month of Sale Balance in Each Category Estimated Uncollectible
November-December $1,080,000 3%
July-October 650,000 15%
January-June 420,000 50%
Prior to 1/1 150,000 90%
$2,300,000

Instructions:
A. Prepare a spreadsheet analyzing the accounts receivable and allowance for Doubtful Accounts for the year ended December 31, 2011. Prepare the journal entry for the year-end adjustment to the Allowance for Doubtful Accounts..
B. The company discovered $100,000 of the accounts receivable listed as January-June sales had been ordered during that time period, but the actual sales were not made until August. Revise your report to include this information.
C. In addition to the change in part (B), assume Dusty Horseman Corporation revises its estimates of uncollectible accounts for the November-December sales category to 5%. Revise your report to include this additional information.

Accounting 350 - Computer Spreadsheet Assignment #3 (Chapter 10)

Insole Vent Company purchased a building on January 1, 2003, paying $500,000 down and signing a mortgage note for an additional $4,500,000. The mortgage will be paid off with semi-annual payments (June 30 and December 31) over a period of 15 years. The mortgage rate is 8%.

Required:
A. 1. Prepare the mortgage amortization schedule for the 15 years of this note.
2. Prepare the journal entries for the date of the sale and for the first two payments.

B. Repeat part A assuming the mortgage rate is 6%.
C. Repeat part A assuming the down payment of $1,000,000 and a mortgage of $4,000,000.

View Full Posting Details