Both the internal and external sources of finance are very important means of funding for
entrepreneurs. How?

Your company currently has $1,000 par, 6% coupon bonds with 10 years to maturity and a price of $1,082. If you want to issue new 10-year coupon bonds at par, what

coupon rate do you need to​ set? Assume that for both​ bonds, the next coupon payment is due in exactly six months.

Can you send the excel file if I provide an email address?

This is a question on managerial decision making with excel spreadsheet. A step wise how to solve this will be appreciated for homework and not any exam. A model in excel will really help.

John’s Truck

John owns John’s Truck Rental, which is currently for sale for $650,000. He would like you to develop a five-year spreadsheet model to assist buyers in their evaluation of the company.

The company currently has a fleet of 100 trucks, which are rented out for $15,000 per year. At this rental rate, the average utilization of the fleet is 62%. He believes that for every $1,500 decrease in the annual rental rate, the utilization will increase by 7%. Conversely, the same size increase in the rental rate will lead to a decrease of the same amount. The spreadsheet model should be able to handle a change in rental rate and calculate the corresponding change in utilization- the user should not be limited to increments of $1,500 only (assume that fractional values of utilization are acceptable as these are annual averages of utilization). The annual rental rate should be set once and held steady for all five years of the model.

The cost of maintaining the fleet runs $4,800 per truck per year (regardless of utilization), which includes inspection fees, licenses, normal maintenance, and storage. These maintenance costs are expected to increase by 9% per year. The company has fixed office costs of $60,000 per year, which are expected to remain stable, and pays property taxes of $35,000, which is expected to grow at a rate of 3% per year. Profits are subject to a 30% income tax rate (and losses, if incurred, are not taxed).

At the end of five years, it is assumed that the buyer will resell the business for cash. John estimates the selling price at that time to be three times the gross revenue in that final year. Cash flow in the final year would include cash from the sale of the business, which would be taxed at the same rate as annual income (and we will assume full depreciation of the trucks has already occurred, thus no tax effects). Investment profit for the buyers is defined to be the Net Present Value of the annual cash flows (all operating revenues and expenses are in cash), computed at a discount rate of 10%. The calculation of NPV includes the purchase price, incurred at the beginning of Year 1, and net income from the operations (include the sale price in Year 5) over five years.

To do

1. Build the spreadsheet model to solve this problem. Make sure you follow the guidelines for good spreadsheet design including proper cell referencing, good flow/layout, etc. Your grade on this portion of the HW reflects having correct calculations, following guidelines and creating a well-designed and easy-to-use worksheet.
   • Use Excel functions to help you, as necessary.

2. John is thinking about raising the selling price. Use Goal Seek to find the point at which the NPV hits $1 million (John considers the point at which it might be hard to find a buyer).
   • What is the selling price at which this NPV occurs? Write the answer to this question somewhere (easy to find) on your worksheet.
   • Make sure to reset the selling price to the base value before proceeding to Task 3!

3. John would to have some additional analysis available on the spreadsheet to provide the potential buyers with information that might help inform their decision. He would like you to analyze the effect of the annual rental rate on the NPV. You recommend using a Data Table. Build this table on the same worksheet as your model, using values of annual rental rate ranging from $10,500 to $19,500 in increments of $1,500. Beyond including the corresponding NPV output values, you want to include the utilization rates in this table to confirm for the potential buyer that these changes are being calculated correctly.
   • Based on the results of the table alone, what is the annual rental rate that is most attractive to a potential buyer?

Homework Submission Instructions:

• Upload your completed Excel workbook
• Make sure that your model has the correct base-case input values and results!
• Use at least 2 decimal places for any fractional value.

Are rising inflation rates a threat to healthcare companies?

Compute the Earning After Tax using both FIFO & LIFO inventory evaluation methods from the following data:

Ratios are mostly calculated using data drawn from the financial statements of a firm. However, another group of ratios, called market value ratios, relate to a firm’s observable market value, stock prices, and book values, integrating information from both the market and the firm’s financial statements.

Consider the case of Green Caterpillar Garden Supplies Inc.:

Green Caterpillar Garden Supplies Inc. just reported earnings after tax (also called net income) of $9,250,000 and a current stock price of $12.00 per share. The company is forecasting an increase of 25% for its after-tax income next year, but it also expects it will have to issue 3,000,000 new shares of stock (raising its shares outstanding from 5,500,000 to 8,500,000).

If Green Caterpillar’s forecast turns out to be correct and its price/earnings (P/E) ratio does not change, what does the company’s management expect its stock price to be one year from now? (Round any P/E ratio calculation to four decimal places.)

$9.71 per share

$12.00 per share

$7.28 per share

$12.14 per share

One year later, Green Caterpillar’s shares are trading at $55.80 per share, and the company reports the value of its total common equity as $16,507,000. Given this information, Green Caterpillar’s market-to-book (M/B) ratio is__.

Can a company’s shares exhibit a negative P/E ratio?

Yes

No

Which of the following statements is true about market value ratios?

Companies with high research and development (R&D) expenses tend to have low P/E ratios.

Companies with high research and development (R&D) expenses tend to have high P/E ratios.

Explain “child” in tax law and the conditions to apply for a tax relief that comes with a “child” in malaysia. [8 marks]

RATIO ANALYSIS

Data for Barry Computer Co. and its industry averages follow.

1. Calculate the indicated ratios for Barry. Round your answers to two decimal places.

   Ratio	Barry	Industry Average
   Current	x	2.28x
   Quick	x	1.36x
   Days sales outstandinga	days	24.40 days
   Inventory turnover	x	7.06x
   Total assets turnover	x	2.05x
   Profit margin	%	1.40%
   ROA	%	2.87%
   ROE	%	6.18%
   ROIC	%	7.00%
   TIE	x	2.54x
   Debt/Total capital	%	39.21%

   
   ^aCalculation is based on a 365-day year.
   
2. Construct the DuPont equation for both Barry and the industry. Round your answers to two decimal places.

   	FIRM	INDUSTRY
   Profit margin	%	1.40%
   Total assets turnover	x	2.05x
   Equity multiplier	x	x

Kaye's Kitchenware has a market/book ratio equal to 1. Its stock price is $15 per share and it has 4.7 million shares outstanding. The firm's total capital is $135 million and it finances with only debt and common equity. What is its debt-to-capital ratio? Round your answer to two decimal places.

%

The most recent data from the annual balance sheets of Pellegrini Southern Corporation and Jing Foodstuffs Corporation are as follows:

Balance Sheet December 31st (Millions of dollars)

Pellegrini Southern Corporation’s current ratio is___, and its quick ratio is___; Jing Foodstuffs Corporation’s current ratio is___, and its quick ratio is___. Note: Round your values to four decimal places.

Which of the following statements are true? Check all that apply.

Pellegrini Southern Corporation has less liquidity but also a greater reliance on outside cash flow to finance its short-term obligations than Jing Foodstuffs Corporation.

If a company’s current liabilities are increasing faster than its current assets, the company’s liquidity position is weakening.

If a company has a quick ratio of less than 1 but a current ratio of more than 1 and if the difference between the two ratios is large, then the company depends heavily on the sale of its inventory to meet its short-term obligations.

Pellegrini Southern Corporation has a better ability to meet its short-term liabilities than Jing Foodstuffs Corporation.

An increase in the current ratio over time always means that the company’s liquidity position is improving.

The Australian government issues bonds through a discriminatory variable rate auction.

Which of the following statements is correct?