Excel Assignment 4 INSERT YOUR NAME AND SECTION HERE A beverage company that currently sells apple juice is considering adding grape juice to its product line. They have determined they can sell the new product, grape juice, for $1.50 per bottle. Their manufacturing plant has excess capacity in a fully depreciated building to process the grape juice drink. The grape juice drink will be discontinued after three years. The new equipment will be depreciated straight line to zero over its four year life. The new grape juice project will require an additional $200,000 of working capital. The projected sales of the grape juice are 300,000 bottles the first year, with a 20% sales growth rate for both the following years. Variable costs are 25% of sales and fixed costs are $100,000 each year. The new equipment costs $800,000 and will have a salvage value of $150,000. The corporate marginal tax rate is 35% and the company currently has 1,000,000 shares of stock outstanding at a current price of $20 per share. The company also has 30,000 bonds outstanding, with a current price of $960. The bonds pay interest semi-annually and the coupon rate is 6%. The bonds have a par value of $1,000 and will mature in 15 years. Even though the company has stock outstanding it is not publicly traded. Therefore, there is no public financial information available. However management believes that given the industry they are in, the most reasonable comparable publicly traded company is PepsiCo, Inc (ticker symbol PEP). In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the beta from PEP and the market risk premium based on the S&P 500 annual expected rate of return. (We calculated a monthly expected retään for the market in the return exercise. You can simply multiply that rate by 12 for an expected annual rate on the market.) Use the most recent 3 month U.S. Treasury Bill rate for the Risk Free Rate. The WACC is then calculated using this information and the other information provided above. Clearly show all your calculations and sources for all parameter estimates used in the WACC. Required In the Blank Template worksheet tab: 1. Fill in all the yellow shaded cells with the given information. 2. Insert formulas in the blue shaded cells, II. Calculations, referencing cells in Section 1. Calculate the cost of debt using the =Rate formula, and the given bond information. 3. Enter formulas in the blue cells in Sections III, IV, V and VI creating a partial income statement, incremental cash flows including terminal cash flows and the calulation of NPV. A35

Excel Assignment 4 INSERT YOUR NAME AND SECTION HERE A beverage company that currently sells apple juice is considering adding grape juice to its product line. They have determined they can sell the new product, grape juice, for $1.50 per bottle. Their manufacturing plant has excess capacity in a fully depreciated building to process the grape juice drink. The grape juice drink will be discontinued after three years. The new equipment will be depreciated straight line to zero over its four year life. The new grape juice project will require an additional $200,000 of working capital. The projected sales of the grape juice are 300,000 bottles the first year, with a 20% sales growth rate for both the following years. Variable costs are 25% of sales and fixed costs are $100,000 each year. The new equipment costs $800,000 and will have a salvage value of $150,000. The corporate marginal tax rate is 35% and the company currently has 1,000,000 shares of stock outstanding at a current price of $20 per share. The company also has 30,000 bonds outstanding, with a current price of $960. The bonds pay interest semi-annually and the coupon rate is 6%. The bonds have a par value of $1,000 and will mature in 15 years. Even though the company has stock outstanding it is not publicly traded. Therefore, there is no public financial information available. However management believes that given the industry they are in, the most reasonable comparable publicly traded company is PepsiCo, Inc (ticker symbol PEP). In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the beta from PEP and the market risk premium based on the S&P 500 annual expected rate of return. (We calculated a monthly expected retään for the market in the return exercise. You can simply multiply that rate by 12 for an expected annual rate on the market.) Use the most recent 3 month U.S. Treasury Bill rate for the Risk Free Rate. The WACC is then calculated using this information and the other information provided above. Clearly show all your calculations and sources for all parameter estimates used in the WACC. Required In the Blank Template worksheet tab: 1. Fill in all the yellow shaded cells with the given information. 2. Insert formulas in the blue shaded cells, II. Calculations, referencing cells in Section 1. Calculate the cost of debt using the =Rate formula, and the given bond information. 3. Enter formulas in the blue cells in Sections III, IV, V and VI creating a partial income statement, incremental cash flows including terminal cash flows and the calulation of NPV. A35

Excel Assignment 4 INSERT YOUR NAME AND SECTION HERE A beverage company that currently sells apple juice is considering adding grape juice to its product line. They have determined they can sell the new product, grape juice, for $1.50 per bottle. Their manufacturing plant has excess capacity in a fully depreciated building to process the grape juice drink. The grape juice drink will be discontinued after three years. The new equipment will be depreciated straight line to zero over its four year life. The new grape juice project will require an additional $200,000 of working capital. The projected sales of the grape juice are 300,000 bottles the first year, with a 20% sales growth rate for both the following years. Variable costs are 25% of sales and fixed costs are $100,000 each year. The new equipment costs $800,000 and will have a salvage value of $150,000. The corporate marginal tax rate is 35% and the company currently has 1,000,000 shares of stock outstanding at a current price of $20 per share. The company also has 30,000 bonds outstanding, with a current price of $960. The bonds pay interest semi-annually and the coupon rate is 6%. The bonds have a par value of $1,000 and will mature in 15 years. Even though the company has stock outstanding it is not publicly traded. Therefore, there is no public financial information available. However management believes that given the industry they are in, the most reasonable comparable publicly traded company is PepsiCo, Inc (ticker symbol PEP). In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the beta from PEP and the market risk premium based on the S&P 500 annual expected rate of return. (We calculated a monthly expected retään for the market in the return exercise. You can simply multiply that rate by 12 for an expected annual rate on the market.) Use the most recent 3 month U.S. Treasury Bill rate for the Risk Free Rate. The WACC is then calculated using this information and the other information provided above. Clearly show all your calculations and sources for all parameter estimates used in the WACC. Required In the Blank Template worksheet tab: 1. Fill in all the yellow shaded cells with the given information. 2. Insert formulas in the blue shaded cells, II. Calculations, referencing cells in Section 1. Calculate the cost of debt using the =Rate formula, and the given bond information. 3. Enter formulas in the blue cells in Sections III, IV, V and VI creating a partial income statement, incremental cash flows including terminal cash flows and the calulation of NPV. A35

Accounting

27th Edition

ISBN:9781337272094

Author:WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.

Publisher:WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.

Chapter10: Long-term Assets: Fixed And Intangible

Section: Chapter Questions

Problem 10.5CP: Communication Godwin Co. owns three delivery trucks. Details for each truck at the end of the most...

See similar textbooks

Related questions

Question

Excel Assignment 4
INSERT YOUR NAME AND SECTION HERE
A beverage company that currently sells apple juice is considering adding grape juice to its product line. They
have determined they can sell the new product, grape juice, for $1.50 per bottle. Their manufacturing plant
has excess capacity in a fully depreciated building to process the grape juice drink. The grape juice drink will
be discontinued after three years. The new equipment will be depreciated straight line to zero over its four year
life. The new grape juice project will require an additional $200,000 of working capital. The projected sales of
the grape juice are 300,000 bottles the first year, with a 20% sales growth rate for both the following years.
Variable costs are 25% of sales and fixed costs are $100,000 each year. The new equipment costs $800,000
and will have a salvage value of $150,000.
The corporate marginal tax rate is 35% and the company currently has 1,000,000 shares of stock outstanding
at a current price of $20 per share. The company also has 30,000 bonds outstanding, with a current price of
$960. The bonds pay interest semi-annually and the coupon rate is 6%. The bonds have a par value of $1,000
and will mature in 15 years.
Even though the company has stock outstanding it is not publicly traded. Therefore, there is no public
financial information available. However management believes that given the industry they are in, the most
reasonable comparable publicly traded company is PepsiCo, Inc (ticker symbol PEP).
In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio.
Therefore, the cost of equity is calculated using the beta from PEP and the market risk premium based on the
S&P 500 annual expected rate of return. (We calculated a monthly expected retään for the market
in the return exercise. You can simply multiply that rate by 12 for an expected annual rate on the market.)
Use the most recent 3 month U.S. Treasury Bill rate for the Risk Free Rate.
The WACC is then calculated using this information and the other information provided above.
Clearly show all your calculations and sources for all parameter estimates used in the WACC.
Required
In the Blank Template worksheet tab:
1. Fill in all the yellow shaded cells with the given information.
2. Insert formulas in the blue shaded cells, II. Calculations, referencing cells in Section 1. Calculate the
cost of debt using the =Rate formula, and the given bond information.
3. Enter formulas in the blue cells in Sections III, IV, V and VI creating a partial income statement, incremental
cash flows including terminal cash flows and the calulation of NPV.
A35

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Blurred answer

Similar questions

I need help with questions 4/5/6A Lou Barlow, a divisional manager for Sage Company, has an opportunity to manufacture and sell one of two new products for a five-year period. His annual pay raises are determined by his division’s return on investment (ROI), which has exceeded 21% each of the last three years. He has computed the cost and revenue estimates for each product as follows:    Product A Product B Initial investment: Cost of equipment (zero salvage value) $ 210,000 $ 420,000 Annual revenues and costs: Sales revenues $ 290,000 $ 390,000 Variable expenses $ 136,000 $ 186,000 Depreciation expense $ 42,000 $ 84,000 Fixed out-of-pocket operating costs $ 74,000 $ 54,000    The company’s discount rate is 19%.    Click here to view Exhibit 8B-1 and Exhibit 8B-2, to determine the appropriate discount factor using tables.    Required: 1. Calculate the payback period for each product. (Round your answers to 2 decimal places.)…
part 3 4 Question 4.4                                                                                                         Celebration Greeting Cards Incorporated is starting a new business venture and are in the process of evaluating its product lines. Information for one new product, traditional parchment grade cards, is as follows: Sixteen times each year, a new card design will be put into production. Each new design will require $600 in setup costs. The parchment grade card product line incurred $75,000 in development costs and is expected to be produced over the next four years. Direct costs of producing the designs average $0.50 each. Indirect manufacturing costs are estimated at $50,000 per year. Customer service expenses average $0.10 per card. Current sales are expected to be 2,500 units of each card design. Each card sells for $3.50. Sales units equal production units each year. Required What are the estimated life-cycle revenues? What is the estimated life-cycle…
Question 4 You are contemplating leaving your full-time employment to concentrate your ability on the marketing of a new low-energy portable heater. You have spoken to a group of manufacturers of similar product, and you have produced the following data based upon the production of 1200 heaters in the six months to December 31, 2021.                                                                                     R                                 R Unit selling price                                                                                            160 Less: Direct materials                                                 50            Direct labour                                                    30 Fixed Overheads: Admin                                                                         4.00 Rent                                                                            7.50 Rates…
Question #1 North Carolina Manufacturing Co. purchased a new industrial press and installed it in its main processing building. The press had a list price of $75,000. The seller agreed to allow a 12% discount because North Carolina paid cash. Taxes were 6% of the net purchase price. Delivery terms were FOB shipping point. Freight cost amounted to $1,500. North Carolina hired two workers to operate the press. The new employees spent their first two months of employment working on installation of the press. The new workers’ annual salaries are $24,000 per year (each). The cost of the company’s liability insurance policy increased by $4,340 per year as a result of the acquisition of the press. Modifications to the building to allow for installation of the new press cost $2,600. Costs incurred during installation and set-up (calibration, scrap, etc.) totaled $600. The press has an eight-year useful life and an expected salvage value of $8,400. Required Determine the amount to be…
Please answers questions in bold Q1 The production department of Y Company is planning to purchase a new machine to improve product quality. The company’s management accountant is currently evaluating two options- Buy the machine OR Rent it. Following information is available: The company has to pay £3,200 to set up the machine. Insurance cost £450 per annum. If it is bought, the new machine is depreciated on reducing balance basis at the rate of 25%. After various calculations, the company has to pay £4,200 maintenance cost every year and estimated repair cost would be £300 per year. The firm will have to sell old machines, which had cost £65,000 six years ago. Apart from the above information, the £500 of delivery cost is incurred for this purchase option. If it is rented, £ 4,650 per year to pay as rent. There is no cost for repair and maintenance. However, the firm is required to pay the administration charge of £650 with this rent option. For rent option, the delivery cost…
Question 5 You are contemplating leaving your full-time employment to concentrate your ability on the marketing of a new low-energy portable heater. You have spoken to a group of manufacturers of similar product, and you have produced the following data based upon the production of 1200 heaters in the six months to December 31, 2021. R R Unit selling price 160 Less: Direct materials 50 Direct labour 30 Fixed Overheads: Admin 4.00 Rent 7.50 Rates…
Question 7 You are contemplating leaving your full-time employment to concentrate your ability on the marketing of a new low-energy portable heater. You have spoken to a group of manufacturers of similar product, and you have produced the following data based upon the production of 1200 heaters in the six months to December 31, 2021. R R Unit selling price 160 Less: Direct materials 50 Direct labour 30 Fixed Overheads: Admin 4.00 Rent 7.50 Rates…
Chapter 9 Section 4 Problem 2 (09.04-PR002) - use MACRS-GDS 7 year property using excel to a answer the following. Michelin is considering going “lights out” in the mixing area of the business that operates 24/7. Currently, personnel with a loaded cost of $600,000 per year are used to manually weigh real rubber, synthetic rubber, carbon black, oils, and other components prior to manual insertion in a Banbary mixer that provides a homogeneous blend of rubber for making tires (rubber products). New technology is available that has the reliability and consistency desired to equal or exceed the quality of blend now achieved manually. It requires an investment of $2.5 million, with $110,000 per year operational costs and will replace all of the manual effort described above. The planning horizon is 8 years and there will be a $300,000 salvage value at that time for the new technology. The income-tax rate is 25% and the after-tax MARR is 10%. A. Determine the annual cost of purchasing the…
Question 11 You are contemplating leaving your full-time employment to concentrate your ability on the marketing of a new low-energy portable heater. You have spoken to a group of manufacturers of similar product, and you have produced the following data based upon the production of 1200 heaters in the six months to December 31, 2021. R R Unit selling price 160 Less: Direct materials 50 Direct labour 30 Fixed Overheads: Admin 4.00 Rent 7.50 Rates…
Comprehensive Problem 5Part B: Note: This section is a continuation from Part A of the comprehensive problem. Be sure you have completed Part A before attempting Part B. You may have to refer back to data presented in Part A and use answers from Part A when completing this section. Genuine Spice Inc. began operations on January 1 of the current year. The company produces eight- ounce bottles of hand and body lotion called Eternal Beauty. The lotion is sold wholesale in 12-bottle cases for $100 per case. There is a selling commission of $20 per case. The January direct materials, direct labor, and factory overhead costs are as follows: DIRECT MATERIALS CostBehavior Unitsper Case Costper Unit Direct MaterialsCost per Case Cream base Variable 100 ozs. $0.02 $2.00 Natural oils Variable 30 ozs. 0.30 9.00 Bottle (8-oz.) Variable 12 bottles 0.50 6.00 $17.00 DIRECT LABOR Department CostBehavior Timeper Case Labor Rateper Hour Direct LaborCost…
Ethical Dilemma: Recognition Point and Ethical Considerations C7. Business Application ▶ Robert Shah, a sales representative for Quality Office Supplies Corporation, will receive a substantial bonus if he meets his annual sales goal. The company’s recognition point for sales is the day of shipment. On December 31, Shah realizes he needs sales of $2,000 to reach his sales goal and receive the bonus. He calls a purchaser for a local insurance company, whom he knows well, and asks him to buy $2,000 worth of copier paper today. The purchaser says, “But Robert, that’s more than a year’s supply for us.” Shah says, “Buy it today. If you decide it’s too much, you can return however much you want for full credit next month.” The purchaser says, “Okay, ship it.” The paper is shipped on December 31 and recorded as a sale. On January 15, the purchaser returns $1,750 worth of paper for full credit (approved by Shah) against the bill. Should the shipment on December 31 be recorded as a…
Question1 Your company has been doing well, reaching $1.04 million in earnings, and is considering launching a new product. Designing the new product has already cost $507,000. The company estimates that it will sell 839,000 units per year for $2.95 per unit and variable non-labor costs will be $1.04 per unit. Production will end after year 3. New equipment costing $1.04 million will be required. The equipment will be depreciated to zero using the 7-year MACRS schedule. You plan to sell the equipment for book value at the end of year 3. Your current level of working capital is $303,000. The new product will require the working capital to increase to a level of $385,000 immediately, then to $399,000 in year 1, in year 2 the level will be $341,000, and finally in year 3 the level will return to $303,000. Your tax rate is 21%. The discount rate for this project is 9.8%. Do the capital budgeting analysis for this project and calculate its NPV.

SEE MORE QUESTIONS

Recommended textbooks for you

Accounting

Accounting

ISBN:

9781337272094

Author:

WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.

Publisher:

Cengage Learning,

Accounting

Accounting

ISBN:

9781337272094

Author:

WARREN, Carl S., Reeve, James M., Duchac, Jonathan E.

Publisher:

Cengage Learning,

SEE MORE TEXTBOOKS