NP_EX19_9b_SabrinaCapozzi-Herrera_2
xlsx
keyboard_arrow_up
School
Western Illinois University *
*We aren’t endorsed by this school
Course
320
Subject
Information Systems
Date
Dec 6, 2023
Type
xlsx
Pages
12
Uploaded by Sabrina483
Author:
Note: D
ChargeAll
PERFORM FINANCIAL CALCULATIONS
Sabrina Capozzi-Herrera
New Perspectives Excel 2019
| Module 9: SAM Project 1b
Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the S
website.
AM
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
New Product Loan Analysis
ChargeAll Full-Room Wireless Charger
Loan Conditions
Loan amount (pv)
$478,000
Annual interest rate
4.85%
Monthly interest rate (rate)
0.40%
Loan period in years
5
Loan period in months (nper)
60
Monthly payment
($8,988)
Start date of loan
1/6/2022
Principal and Interest Payments
Year 1
Year 2
Year 3 Year 4
Months
1
13
25
37
12
24
36
48
Interest
($21,275)
($16,982)
($12,475)
($7,745)
Principal
($86,576)
($90,870)
($95,376)
($100,106)
Principal remaining
$391,424 $300,554 $205,177 $105,071 Remaining %
82%
63%
43%
22%
Year 5
49
60
Total
($2,781)
($61,258)
($105,071)
($478,000)
$0 0%
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
New Product Depreciation Schedule
ChargeAll Full-Room Wireless Charger
Hardware
Long-term assets (cost)
$ 478,000 Salvage value (salvage)
$ 75,650 Life of asset (life)
6 Straight-Line Depreciation
Year
1
2
3
4
5
6
Annual depreciation
$67,058 $67,058 $67,058 $67,058 $67,058 $67,058 Cumulative depreciation
$67,058 $134,117 $201,175 $268,233 $335,292 $402,350 Depreciated asset value
$410,942 $343,883 $276,825 $209,767 $142,708 $75,650 Declining Balance Depreciation
Year
1
2
3
4
5
6
Annual depreciation
Err:502
$93,102 $68,430 $50,296 $36,968 $27,171 Cumulative depreciation
Err:502
Err:502
Err:502
Err:502
Err:502
Err:502
Depreciated asset value
Err:502
Err:502
Err:502
Err:502
Err:502
Err:502
Yearly depreciation allowance for the first year:
Yearly depreciation allowance for the last year:
New Product Estimated Earnings
ChargeAll Full-Room Wireless Charger
Percent cost of sales & marketing
28%
Percent cost of research & development
22%
Income
Year 1
Year 2
Year 3
Year 4
Year 5
Sales
$ 825,000 $ 1,068,750 $ 1,312,500 $ 1,556,250 $ 1,800,000 Cost of sales & marketing
231,000 299,250 367,500 435,750 504,000 Cost of research & development
181,500 235,125 288,750 342,375 396,000 Gross Profit
$ 412,500 $ 534,375 $ 656,250 $ 778,125 $ 900,000 Expenses
Insurance
$ 22,000 $ 24,250 $ 25,563 $ 26,941 $ 28,388 Licensing
24,000 25,250 26,563 27,941 29,387 Miscellaneous
15,000 15,750 16,538 17,364 18,233 Payroll
400,000 443,000 517,300 542,330 618,563 Rent
45,000 46,800 48,672 50,619 52,644 Utilities
55,000 58,000 61,150 64,457 67,930 Total General Expenses
$ 561,000 $ 613,050 $ 695,785 $ 729,652 $ 815,144 Initial Earnings
$ (148,500)
$ (78,675)
$ (39,535) $ 48,473 $ 84,856 Year 1
Year 2
Year 3
Year 4
Year 5
$- $100,000 $200,000 $300,000 $400,000 $500,000 $600,000 $700,000 $800,000 $900,000 $1,000,000 Estimated Gross Profit
Income
0
10
20
30
40
50
60
$0
$20,000
$40,000
$60,000
$80,000
$100,000
$120,000
Projected Monthly Revenue
Months in Operation
Revenue
Month
Month Number
Revenue
Jan 2022
1
63,443 Feb 2022
2
58,967 Mar 2022
3
65,268 Apr 2022
4
69,665 May 2022
5
71,775 Jun 2022
6
76,834 Jul 2022
7
71,995 Aug 2022
8
76,935 Sep 2022
9
78,021 Oct 2022
10
81,566 Nov 2022
11
83,775 Dec 2022
12
90,756 Jan 2023
13
85,064 Feb 2023
14
84,761 Mar 2023
15
81,968 Apr 2023
16
82,623 May 2023
17
82,529 Jun 2023
18
82,181 Jul 2023
19
80,015 Aug 2023
20
82,631 Sep 2023
21
84,166 Oct 2023
22
84,570 Nov 2023
23
84,621 Dec 2023
24
86,866 Jan 2024
25
89,089 Feb 2024
26
93,192 Mar 2024
27
93,488 Apr 2024
28
95,502 May 2024
29
97,665 Jun 2024
30
98,488 Jul 2024
31
98,255 Aug 2024
32
98,738 Sep 2024
33
98,550 Oct 2024
34
99,111 Nov 2024
35
100,477 Dec 2024
36
101,448 Jan 2025
37
105,028 Feb 2025
38
106,887 Mar 2025
39
107,982 Apr 2025
40
107,994
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
May 2025
41
108,215 Jun 2025
42
104,568 Jul 2025
43
103,865 Aug 2025
44
106,495 Sep 2025
45
107,125 Oct 2025
46
107,886 Nov 2025
47
108,084 Dec 2025
48
108,874 Jan 2026
49
108,643 Feb 2026
50
108,518 Mar 2026
51
109,643 Apr 2026
52
108,444 May 2026
53
109,663 Jun 2026
54
109,293 Jul 2026
55
108,183 Aug 2026
56
109,650 Sep 2026
57
110,430 Oct 2026
58
110,605 Nov 2026
59
109,093 Dec 2026
60
109,830
New Product Investor Returns
ChargeAll Full-Room Wireless Charger
Repayment Schedule
Payments
Net Cash Flow
Startup
$ (295,000)
$ (295,000)
Year 1
55,000 (240,000)
Year 2
75,000 (165,000)
Year 3
87,500 (77,500)
Year 4
100,500 23,000 Year 5
125,500 148,500 Desired rate of return
10.00%
Present value
$ 324,292 Net present value (npv)
$ 29,292 Internal rate of return
13.34%
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