TN - Excel Project 1 template- IS and BS SP24
.xlsx
keyboard_arrow_up
School
San Jose State University *
*We aren’t endorsed by this school
Course
173A
Subject
Finance
Date
May 17, 2024
Type
xlsx
Pages
5
Uploaded by SuperHumanThunder12272 on coursehero.com
We, PolarSkis Co., are creating a new set of Awesome Skis. Information below.
1) The Awesome Skis will sell for $2000 and have variable costs of $600. We will sell 21,000 units of Awesome S
4) Fixed costs each year are $4,400,000.
6) We must purchase buildings and equipment of $29,400,000 in year 0, depreciated straight-line over 7 years t
8) Tax rate is 21%. The firm has no debt and therefore, no interest.
Part 1: Build the income statement. Item
Info
Selling Price, Awesome Skis
$ 2,000 VC, Awesome Skis
$ 600 Unit Sales/year 1, Awesome Skis
21,000 Sales growth rate
10%
Fixed Costs/year
$ 4,400,000 CAPEX
$ 29,400,000 Depr/year
$ 4,200,000 Tax Rate
21%
Income Statement
Year
1
2
3
4
Selling Price
$ 2,000 $ 2,000 $ 2,000 $ 2,000 Unit Sales
21,000
23,100
25,410
27,951
Sales, Awesome
$ 42,000,000 $ 46,200,000 $ 50,820,000 $ 55,902,000 VC, Awesome
$ (12,600,000) $ (13,860,000) $ (15,246,000) $ (16,770,600)
Fixed Costs
$ (4,400,000) $ (4,400,000) $ (4,400,000) $ (4,400,000)
Depreciation
$ (4,200,000) $ (4,200,000) $ (4,200,000) $ (4,200,000)
EBIT
$ 20,800,000 $ 23,740,000 $ 26,974,000 $ 30,531,400 Interest
$ - $ - $ - $ - EBT
$ 20,800,000 $ 23,740,000 $ 26,974,000 $ 30,531,400 Taxes
$ 4,368,000 $ 4,985,400 $ 5,664,540 $ 6,411,594 Net Income
$ 16,432,000 $ 18,754,600 $ 21,309,460 $ 24,119,806 OCF
$ 20,632,000 $ 22,954,600 $ 25,509,460 $ 28,319,806 To help, fill in this chart so you have all info and can reference cells when building parts 1. Hard-coding numbers is not permitted beyond this purple chart (e.g. fixed costs in your income statement should be "=D17")
As a whole, 5 total points
COMMON SIZE STATEMENT
1
2
3
4
Sales, Awesome
100%
100%
100%
100%
VC, Awesome
-30.00%
-30.00%
-30.00%
-30.00%
Fixed Costs
-10.48%
-9.52%
-8.66%
-7.87%
Depreciation
-10.00%
-9.09%
-8.26%
-7.51%
EBIT
49.52%
51.39%
53.08%
54.62%
Interest
0%
0%
0%
0%
EBT
49.52%
51.39%
53.08%
54.62%
Taxes
10.40%
10.79%
11.15%
11.47%
Net Income
39.12%
40.59%
41.93%
43.15%
Part 2: Finish the Balance Sheet
Balance Sheet (in 000s)
Assets
Cash
50,530
Accounts Receivable
14,148
Inventories
3,355
Other
10,530
Total Current Assets
78,563
Marketable Securities
115,876
Property, Plant and Equipment (PP&E)
37,636
Other
33,634
Non-current Assets
187,146
Total Assets
265,709
Liabilities and Shareholders' Equity
Accounts Payable
29,115
Other
31,673
Commercial Paper
9,953
Short-term Debt
13,529
Total Current Liabilities
84,270
Long-term Debt
84,936
Other
49,984
Total Liabilities
219,190
Shareholders' Equity
46,519
Total Liabilities and Shareholders' Equity
265,709
Create a Common-Size income statement referencing the cells from your income statement above to create the common-size statement below
As a whole, 5 total points
Part 3: Supplemental Questions
Question
Answer
Points
What is the current ratio of the firm? 0.932277204224516
10
26.97
5%
5.48
What is the ROA in year 2?
0.0706
If the firm did have annual interest payments of $1,000,000 what would be the r=times interest earned ratio in year 3?
If the firm pays dividends of $1,500,000 in year 6, what is the dividend payout ratio for that year?
If the stock price is $30 per share and there are 3 million shares outstanding, what is the P/E ratio in year 1?
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Related Questions
Andres Gimenez is considering purchasing the DoublePlay 3000 machine. The machine will cost
Ahmed $100,000 and he estimates it will last two years after which he will sell the machine for
$30,000.
The machine is considered specialty equipment under MACRS depreciation and depreciated
over 4 years as shown below.
The tax rate is 40%.
Revenue
Fixed Costs
SG&A expenses
Depreciation
I
EBIT
Taxes
Year 1 Year 2
$125,000 $ 125,000
$ 40,000 $ 40,000
20,000
20,000
33,333
44,450
$ 31,667 $ 20,550
8,220
12,667
Net Income
$19,000 $ 12,330
1. What is the net present value of the investment, assuming an interest rate of 10%?
2. What is the internal rate of return of the investment?
arrow_forward
3
Your firm needs a computerized machine tool lathe which costs $59,000 and
requires $12,900 in maintenance for each year of its 3-year life. After three
years, this machine will be replaced. The machine falls into the MACRS 3-year
class life category, and neither bonus depreciation nor Section 179 expensing
can be used. Assume a tax rate of 21 percent and a discount rate of 12
percent.
If the lathe can be sold for $5,900 at the end of year 3, what is the after-tax
salvage value? (Round your answer to 2 decimal places.)
Salvage value after tax $
5,380.45X
4
arrow_forward
Pisa Pizza Parlor is investigating the purchase of a new $45,000 delivery truck that would contain specially designed warming racks. Thenew truck would have a six-year useful life. It would save $5,400 per year over the present method of delivering pizzas. In addition, it would result in the sale of 1,800 more pizzas each year. The company realizes a contribution margin of $2 per pizza.
Required:
(Ignore income taxes.)
1. What would be the total annual cash inflows associated with the new truck for capital budgeting purposes?
2. Find the internal rate of return promised by the new truck to the nearest whole percent.
arrow_forward
i will 10 upvotes urgent
Your firm needs a computerized machine tool lathe which costs $56,000 and requires $12,600 in maintenance for each year of its 3-year life. After three years, this machine will be replaced. The machine falls into the MACRS 3-year class life category. Assume a tax rate of 35 percent and a discount rate of 12 percent. Calculate the depreciation tax shield for this project in year 3.
arrow_forward
Your company decided to offer product delivery service and would need a truck for the delivery. You are evaluating the
following two options. Option 1: Your company can lease a truck at a pretax cost of $27,000 per year. Option 2: Your
company can buy a truck at a cost of $84,000, with annual pretax maintenance expenses of $14,000. The truck will
depreciate to zero on a straight-line basis. The truck has a 4 years life and will have a salvage value of $18,000 at the
end of its life. If the tax rate is 35% and the discount rate is 10%, which option would you prefer? (Hint: compute the
equivalent annual cost (EAC).)
arrow_forward
A cyber is planning on replacing its existing computers, which were purchased 2 years ago at a cost of $16.500. They could be sold today at $12.500. The depreciation period of this system is 5 years from the date of its purchase. New PCs could be bought for $18.000. Asume a 25% tax rate over profits.
Calculate the book value of the old PCs
Calculate the after-tax proceeds of its sale for $12.500
Calculate the initial investment associated with the replacement project.
arrow_forward
You install all new hardwood floors in your upscale ski resort. The total cost of the floor
installation is $62,000. Instead of realizing the full expense in this fiscal year, you will
depreciate the cost over the useful life of the floors. You expect the floors will have a useful
life of 12 years,
and the scrap value will be $1,000. Using straight line depreciation, how much
depreciation should you recognize each year?
O $4,000
O $5,083.33
$5,166.67
$6,200.10
arrow_forward
AAA Shop is considering a project to improve their production efficiency. They are trying to decide whether it is a good idea or not to buy an automated injection molding machine which will result in reducing pre-tax costs by $500,000 for each of the next three years. The molding machine will cost $1,000,000 and the IRS says it must be depreciated as 3 year MACRS equipment. AAA Shop believes they can sell the machine for $80,000 at the end of three years. The molding machine will require an initial investment to increase inventoru by $34,000, and then an additional inventory increase of $3,000 for each succeeding year of the project. AAA's tax rate is 35% and uses a discount rate of 14%. AAA already spent $5,000 investigating the feasibility of using the machine. Please show your detailed work so that the NPV of the project is $52,215.89. What is the $5000 that AAA already spent investigating the feasibility of using the machine? What is the change in networking capital at the end of…
arrow_forward
Hey I'm having trouble with this problem, I'm not sure if 48,000 is pv or fv also I'm not sure if what to do with annual maintenance cost either.
"Esquire Company needs to acquire a molding machine to be used in its manufacturing process. Two types of machines that would be appropriate are presently on the market.
Machine A could be purchased for $48,000. It will last 10 years with annual maintenance costs of $1,000 per year. After 10 years the machine can be sold for $5,000.Machine B could be purchased for $40,000. It also will last 10 years and will require maintenance costs of $4,000 in year three, $5,000 in year six, and $6,000 in year eight. After 10 years, the machine will have no salvage value.
Assume an interest rate of 8% properly reflects the time value of money in this situation and that maintenance costs are paid at the end of each year. Ignore income tax considerations.Calculate the present value of Machine A & Machine B. Which machine Esquire should purchase?"
arrow_forward
4. A warehouse is currently sitting empty, but can be rented out to a third party for
$100,000 a year. A project is proposed in the warehouse by your company which will
require an investment of $200,000 and will be depreciated using MACRS over a five year
life. At the end of the project in THREE YEARS, the machinery will be sold for $50,000.
Products sold will be 20,000 units per year at $30 each. Variable costs will be 35 percent
of sales. Fixed costs (not counting the warehouse) will be $20,000 per year. Working
capital will be $50,000. The tax rate is 30 percent. If the interest rate is 10 percent, what is
the NPV of this project?
arrow_forward
It is being decided whether the company is buying a delivery truck worth 25000
USD, which can be rented out to other warehouses in your area. For a useful life
of 15 yrs, it is projected that you can earn as much as 3100 USD. At the end of its
useful life, the salvage value is zero. Disregarding taxes, the ROR is projected to
be approximately equal to:
7.1%
6.5%
5.3%
4.2%
Other:
arrow_forward
•
Massey Machine Shop is considering a project to improve their production efficiency. They are trying to decide whether it is a good idea or not to buy an automated injection molding machine which will result in reducing pre-tax costs by $160,000 for each of the next four years. The molding machine will cost $480,000 and the IRS says it must be depreciated as 5-year MACRS equipment. Massey Machine Shop believes they can sell the machine for $70,000 at the end of four years. The molding machine will require an initial investment to increase inventory by $20,000, and then an additional inventory increase of $3,000 for each succeeding year of the project. Massey’s tax rate is 35% and uses a discount rate of 14%.
arrow_forward
Beryl's Iced Tea currently rents a bottling machine for $52 000 per year, including all maintenance expenses. It is considering purchasing a machine instead and is comparing two options:
a. Purchase the machine it is currently renting for $155 000. This machine will require $21 000 per year in ongoing maintenance expenses.
b. Purchase a new, more advanced machine for $265 000. This machine will require $20 000 per year in ongoing maintenance expenses and will lower bottling costs by $15 000 per year. Also, $37 000
will be spent up front training the new operators of the machine.
Suppose the appropriate discount rate is 8% per year and the machine is purchased today. Maintenance and bottling costs are paid at the end of each year, as is the rental of the machine. Assume also
that the machines will be depreciated via the straight-line method over seven years and that they have a ten-year life with a negligible salvage value. The marginal corporate tax rate is 35%.
Should Beryl's Iced Tea…
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
EBK CONTEMPORARY FINANCIAL MANAGEMENT
Finance
ISBN:9781337514835
Author:MOYER
Publisher:CENGAGE LEARNING - CONSIGNMENT
Essentials Of Business Analytics
Statistics
ISBN:9781285187273
Author:Camm, Jeff.
Publisher:Cengage Learning,
Intermediate Financial Management (MindTap Course...
Finance
ISBN:9781337395083
Author:Eugene F. Brigham, Phillip R. Daves
Publisher:Cengage Learning
Related Questions
- Andres Gimenez is considering purchasing the DoublePlay 3000 machine. The machine will cost Ahmed $100,000 and he estimates it will last two years after which he will sell the machine for $30,000. The machine is considered specialty equipment under MACRS depreciation and depreciated over 4 years as shown below. The tax rate is 40%. Revenue Fixed Costs SG&A expenses Depreciation I EBIT Taxes Year 1 Year 2 $125,000 $ 125,000 $ 40,000 $ 40,000 20,000 20,000 33,333 44,450 $ 31,667 $ 20,550 8,220 12,667 Net Income $19,000 $ 12,330 1. What is the net present value of the investment, assuming an interest rate of 10%? 2. What is the internal rate of return of the investment?arrow_forward3 Your firm needs a computerized machine tool lathe which costs $59,000 and requires $12,900 in maintenance for each year of its 3-year life. After three years, this machine will be replaced. The machine falls into the MACRS 3-year class life category, and neither bonus depreciation nor Section 179 expensing can be used. Assume a tax rate of 21 percent and a discount rate of 12 percent. If the lathe can be sold for $5,900 at the end of year 3, what is the after-tax salvage value? (Round your answer to 2 decimal places.) Salvage value after tax $ 5,380.45X 4arrow_forwardPisa Pizza Parlor is investigating the purchase of a new $45,000 delivery truck that would contain specially designed warming racks. Thenew truck would have a six-year useful life. It would save $5,400 per year over the present method of delivering pizzas. In addition, it would result in the sale of 1,800 more pizzas each year. The company realizes a contribution margin of $2 per pizza. Required: (Ignore income taxes.) 1. What would be the total annual cash inflows associated with the new truck for capital budgeting purposes? 2. Find the internal rate of return promised by the new truck to the nearest whole percent.arrow_forward
- i will 10 upvotes urgent Your firm needs a computerized machine tool lathe which costs $56,000 and requires $12,600 in maintenance for each year of its 3-year life. After three years, this machine will be replaced. The machine falls into the MACRS 3-year class life category. Assume a tax rate of 35 percent and a discount rate of 12 percent. Calculate the depreciation tax shield for this project in year 3.arrow_forwardYour company decided to offer product delivery service and would need a truck for the delivery. You are evaluating the following two options. Option 1: Your company can lease a truck at a pretax cost of $27,000 per year. Option 2: Your company can buy a truck at a cost of $84,000, with annual pretax maintenance expenses of $14,000. The truck will depreciate to zero on a straight-line basis. The truck has a 4 years life and will have a salvage value of $18,000 at the end of its life. If the tax rate is 35% and the discount rate is 10%, which option would you prefer? (Hint: compute the equivalent annual cost (EAC).)arrow_forwardA cyber is planning on replacing its existing computers, which were purchased 2 years ago at a cost of $16.500. They could be sold today at $12.500. The depreciation period of this system is 5 years from the date of its purchase. New PCs could be bought for $18.000. Asume a 25% tax rate over profits. Calculate the book value of the old PCs Calculate the after-tax proceeds of its sale for $12.500 Calculate the initial investment associated with the replacement project.arrow_forward
- You install all new hardwood floors in your upscale ski resort. The total cost of the floor installation is $62,000. Instead of realizing the full expense in this fiscal year, you will depreciate the cost over the useful life of the floors. You expect the floors will have a useful life of 12 years, and the scrap value will be $1,000. Using straight line depreciation, how much depreciation should you recognize each year? O $4,000 O $5,083.33 $5,166.67 $6,200.10arrow_forwardAAA Shop is considering a project to improve their production efficiency. They are trying to decide whether it is a good idea or not to buy an automated injection molding machine which will result in reducing pre-tax costs by $500,000 for each of the next three years. The molding machine will cost $1,000,000 and the IRS says it must be depreciated as 3 year MACRS equipment. AAA Shop believes they can sell the machine for $80,000 at the end of three years. The molding machine will require an initial investment to increase inventoru by $34,000, and then an additional inventory increase of $3,000 for each succeeding year of the project. AAA's tax rate is 35% and uses a discount rate of 14%. AAA already spent $5,000 investigating the feasibility of using the machine. Please show your detailed work so that the NPV of the project is $52,215.89. What is the $5000 that AAA already spent investigating the feasibility of using the machine? What is the change in networking capital at the end of…arrow_forwardHey I'm having trouble with this problem, I'm not sure if 48,000 is pv or fv also I'm not sure if what to do with annual maintenance cost either. "Esquire Company needs to acquire a molding machine to be used in its manufacturing process. Two types of machines that would be appropriate are presently on the market. Machine A could be purchased for $48,000. It will last 10 years with annual maintenance costs of $1,000 per year. After 10 years the machine can be sold for $5,000.Machine B could be purchased for $40,000. It also will last 10 years and will require maintenance costs of $4,000 in year three, $5,000 in year six, and $6,000 in year eight. After 10 years, the machine will have no salvage value. Assume an interest rate of 8% properly reflects the time value of money in this situation and that maintenance costs are paid at the end of each year. Ignore income tax considerations.Calculate the present value of Machine A & Machine B. Which machine Esquire should purchase?"arrow_forward
- 4. A warehouse is currently sitting empty, but can be rented out to a third party for $100,000 a year. A project is proposed in the warehouse by your company which will require an investment of $200,000 and will be depreciated using MACRS over a five year life. At the end of the project in THREE YEARS, the machinery will be sold for $50,000. Products sold will be 20,000 units per year at $30 each. Variable costs will be 35 percent of sales. Fixed costs (not counting the warehouse) will be $20,000 per year. Working capital will be $50,000. The tax rate is 30 percent. If the interest rate is 10 percent, what is the NPV of this project?arrow_forwardIt is being decided whether the company is buying a delivery truck worth 25000 USD, which can be rented out to other warehouses in your area. For a useful life of 15 yrs, it is projected that you can earn as much as 3100 USD. At the end of its useful life, the salvage value is zero. Disregarding taxes, the ROR is projected to be approximately equal to: 7.1% 6.5% 5.3% 4.2% Other:arrow_forward• Massey Machine Shop is considering a project to improve their production efficiency. They are trying to decide whether it is a good idea or not to buy an automated injection molding machine which will result in reducing pre-tax costs by $160,000 for each of the next four years. The molding machine will cost $480,000 and the IRS says it must be depreciated as 5-year MACRS equipment. Massey Machine Shop believes they can sell the machine for $70,000 at the end of four years. The molding machine will require an initial investment to increase inventory by $20,000, and then an additional inventory increase of $3,000 for each succeeding year of the project. Massey’s tax rate is 35% and uses a discount rate of 14%.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- EBK CONTEMPORARY FINANCIAL MANAGEMENTFinanceISBN:9781337514835Author:MOYERPublisher:CENGAGE LEARNING - CONSIGNMENTEssentials Of Business AnalyticsStatisticsISBN:9781285187273Author:Camm, Jeff.Publisher:Cengage Learning,
- Intermediate Financial Management (MindTap Course...FinanceISBN:9781337395083Author:Eugene F. Brigham, Phillip R. DavesPublisher:Cengage Learning
EBK CONTEMPORARY FINANCIAL MANAGEMENT
Finance
ISBN:9781337514835
Author:MOYER
Publisher:CENGAGE LEARNING - CONSIGNMENT
Essentials Of Business Analytics
Statistics
ISBN:9781285187273
Author:Camm, Jeff.
Publisher:Cengage Learning,
Intermediate Financial Management (MindTap Course...
Finance
ISBN:9781337395083
Author:Eugene F. Brigham, Phillip R. Daves
Publisher:Cengage Learning