Description
For my computer systems class.New Perspectives Excel 2019 | Module 9: SAM Project 1b
ChargeAll
PERFORM FINANCIAL CALCULATIONS
GETTING STARTED
•
Open the file NP_EX19_9b_FirstLastName_1.xlsx, available for download from the
SAM website.
•
Save the file as NP_EX19_9b_FirstLastName_2.xlsx by changing the “1” to a “2”.
o
•
If you do not see the .xlsx file extension in the Save As dialog box, do not type it.
The program will add the file extension for you automatically.
With the file NP_EX19_9b_FirstLastName_2.xlsx still open, ensure that your first
and last name is displayed in cell B6 of the Documentation sheet.
o
If cell B6 does not display your name, delete the file and download a new copy
from the SAM website.
PROJECT STEPS
1.
2.
3.
Hwan Rhee is considering whether to start a software company called ChargeAll in Menlo
Park, California, that will produce full-room wireless chargers for any type of mobile
electronic device. Hwan is using an Excel workbook to analyze the financial data for a
startup loan that will fund the parts and manufacturing of his product. He asks for your
help in correcting errors and making financial calculations in the workbook.
Go to the Loan Analysis worksheet. Before Hwan can calculate the principal and interest
payments on the loan, he asks you to correct the errors in the worksheet. Correct the
first error as follows:
a.
In cell D11, use the Error Checking command to identify the error in the cell.
b.
Correct the error to calculate the monthly payment for the loan.
Correct the #DIV/0! errors in the worksheet as follows:
a.
Use Trace Precedents arrows to find the source of the #DIV/0! error in cell G12.
b.
Correct the formula in cell G12, which should divide the remaining principal (cell
G11) by the loan amount (cell D6) to find the percentage of remaining principal.
c.
Fill the range H12:K12 with the formula in cell G12 to correct the remaining
#DIV/0! errors.
d.
Remove any remaining trace arrows.
Now Hwan is ready to calculate the annual principal and interest payments for the
startup loan. Start by calculating the cumulative interest payments as follows:
a.
In cell G9, enter a formula using the CUMIPMT function to calculate the
cumulative interest paid on the loan for Year 1 (payment 1 in cell G7 through
New Perspectives Excel 2019 | Module 9: SAM Project 1b
payment 12 in cell G8). Use 0 as the type argument in your formula because
payments are made at the end of the period.
4.
5.
6.
b.
Use absolute references for the rate, nper, and pv arguments, which are listed in
the range D6:D12.
c.
Use relative references for the start and end arguments.
d.
Fill the range H9:K9 with the formula in cell G9 to calculate the interest paid in
Years 2–5 and the total interest.
Calculate the cumulative principal payments as follows:
a.
In cell G10, enter a formula using the CUMPRINC function to calculate the
cumulative principal paid for Year 1 (payment 1 in cell G7 through payment 12 in
cell G8). Use 0 as the type argument in your formula because payments are made
at the end of the period.
b.
Use absolute references for the rate, nper, and pv arguments, which are listed in
the range D6:D12.
c.
Use relative references for the start and end arguments.
d.
Fill the range H10:K10 with the formula in cell G10 to calculate the principal paid
in Years 2–5 and the total principal.
Go to the Depreciation worksheet. Hwan needs to correct the errors on this worksheet
before he can perform any depreciation calculations.
Correct the errors as follows:
a.
Use Trace Dependents arrows to determine whether the #VALUE! error in cell D20
is causing the other errors in the worksheet.
b.
Use Trace Precedents arrows to find the source of the error in cell D20.
c.
Correct the error so that the formula in cell D20 calculates the cumulative declining
balance depreciation of the hardware by adding the cumulative depreciation value
in Year 1 to the annual depreciation value in Year 2.
Hwan wants to compare straight-line depreciation amounts with declining balance
depreciation amounts to determine which method is more favorable for his company’s
balance sheet. In the range D6:D8, he estimates that the hardware for the new product
will have $478,000 in tangible assets at startup, and that the useful life of these assets
is six years with a salvage value of $75,650.
Start by calculating the straight-line depreciation amounts as follows:
a.
In cell C12, enter a formula using the SLN function to calculate the straight-line
depreciation for the product hardware during the first year.
b.
Use absolute references for the cost, salvage, and life arguments in the SLN
formula.
c.
Fill the range D12:H12 with the formula in cell C12 to calculate the annual and
cumulative straight-line depreciation in Years 2–6.
New Perspectives Excel 2019 | Module 9: SAM Project 1b
7.
8.
Calculate the declining balance depreciation amounts as follows:
a.
In cell C19, enter a formula using the DB function to calculate the declining
balance depreciation for the hardware during the first year of operation.
b.
Use Year 1 (cell C18) as the current period.
c.
Use absolute references only for the cost, salvage, and life arguments in the DB
formula.
d.
Fill the range D19:H19 with the formula in cell C19 to calculate the annual and
cumulative declining balance depreciation in Years 2–6.
Hwan also wants to determine the depreciation balance for the first year and the last
year of the useful life of the product hardware.
Determine these amounts as follows:
a.
In cell E23, enter a formula using the SYD function to calculate the depreciation
balance for the first year.
b.
Use Year 1 (cell C18) as the current period.
c.
In cell E24, enter a formula using the SYD function to calculate the depreciation
balance for the last year.
d.
Use Year 6 (cell H18) as the current period.
9.
Go to the Estimated Earnings worksheet. Hwan has entered most of the income and
expense data on the worksheet. He estimates sales will be $825,000 in Year 1 and
$1,800,000 in Year 5. He needs to calculate the sales amounts for Years 2–4. The sales
should increase at a constant amount from year to year.
Project the sales amounts for Years 2–4 (cells D9:F9) using a Linear Trend interpolation.
10.
Hwan also needs to calculate some expense amounts. He knows the starting amount of
the miscellaneous expense and has estimated the amount in Year 5. He thinks this
expense will increase by a constant percentage.
Project the miscellaneous expenses for Years 2–4 (cells D17:F17) using a Growth Trend
interpolation.
11.
Hwan also knows the rent will be $45,000 in Year 1 and will increase by at least 4
percent per year.
Project the rent expenses as follows:
12.
13.
a.
Project the expenses for rent for Years 2–5 (cells D19:G19) using a Growth Trend
extrapolation.
b.
Use 1.04 (a 4 percent increase) as the step value.
The Estimated Gross Profit line chart in the range H25:Q45 shows the revenue estimated
for the Years 1–5. Hwan wants to extend the projection into Year 6.
Modify the Estimated Gross Profit line chart as follows to forecast the future trend:
a.
Add a Linear Trendline to the Estimated Gross Profit line chart.
b.
Format the trendline to forecast 1 period forward.
The Projected Monthly Revenue scatter chart in the range A25:G44 is based on monthly
revenue estimates listed on the Monthly Projections worksheet. Hwan wants to include a
trendline for this chart that shows how revenues increase quickly at first and then level
off in later months.
New Perspectives Excel 2019 | Module 9: SAM Project 1b
Modify the Projected Monthly Revenue scatter chart as follows to include a logarithmic
trendline:
14.
15.
a.
Add a Trendline to the Projected Monthly Revenue scatter chart.
b.
Format the trendline to use the Logarithmic option.
Go to the Investment Analysis worksheet. This worksheet should show the returns
potential investors could realize if they invested $295,000 in the ChargeAll full-room
wireless charger. Hwan figures a desirable rate of return would be 10 percent. He
estimates the investment would pay different amounts each year (range C8:C12) and
wants to calculate the present value of the investment.
Calculate the present value of the investment as follows:
a.
In cell C15, enter a formula that uses the NPV function to calculate the present
value of the investment in the ChargeAll product.
b.
Use the desired rate of return value (cell C14) as the rate argument.
c.
Use the payments in Years 1–5 (range C8:C12) as the returns paid to investors.
(Hint: If a Formula Omits Adjacent Cell error warning appears, ignore it.)
Hwan also wants to calculate the internal rate of return on the investment. If it is 10
percent or higher, he is confident he can attract investors.
Calculate the internal rate of return on the investment as follows:
a.
In cell C17, enter a formula that uses the IRR function to calculate the internal
rate of return for investing in the ChargeAll product.
b.
Use the payments for startup and Years 1–5 (range C7:C12) as the returns paid to
investors.
Your workbook should look like the Final Figures on the following pages. Save your changes,
close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your
completed project.
New Perspectives Excel 2019 | Module 9: SAM Project 1b
Final Figure 1: Loan Analysis Worksheet
Final Figure 2: Depreciation Worksheet
New Perspectives Excel 2019 | Module 9: SAM Project 1b
Final Figure 3: Estimated Earnings Worksheet
New Perspectives Excel 2019 | Module 9: SAM Project 1b
Final Figure 4: Monthly Projections Worksheet
New Perspectives Excel 2019 | Module 9: SAM Project 1b
Final Figure 5: Investment Analysis Worksheet
New Perspectives Excel 2019 | Module 9: SAM Project 1b
ChargeAll
PERFORM FINANCIAL CALCULATIONS
Author: Celeste Machuca
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the
file from the SAM website.
w copy of the
New Product Loan Analysis
ChargeAll Full-Room Wireless Charger
Loan Conditions
Loan amount (pv)
Annual interest rate
Monthly interest rate (rate)
Loan period in years
Loan period in months (nper)
Monthly payment
Start date of loan
$478,000
4.85%
0.40%
5
60
#NAME?
1/6/2022
Principal and Interest Payments
Year 1
1
Months
12
Interest
Principal
Principal remaining $478,000
Remaining % #DIV/0!
Year 2
13
24
Year 3
25
36
Year 4
37
48
Year 5
49
60
$478,000
#DIV/0!
$478,000
#DIV/0!
$478,000
#DIV/0!
$478,000
#DIV/0!
Total
$0
$0
New Product Depreciation Schedule
ChargeAll Full-Room Wireless Charger
Hardware
Long-term assets (cost)
Salvage value (salvage)
Life of asset (life)
Straight-Line Depreciation
Year
Annual depreciation
Cumulative depreciation
Depreciated asset value
$
$
1
$0
$478,000
Declining Balance Depreciation
Year
1
Annual depreciation
$0
Cumulative depreciation
$478,000
Depreciated asset value
478,000
75,650
6
2
$0
$478,000
3
$0
$478,000
4
$0
$478,000
5
$0
$478,000
6
$0
$478,000
2
3
4
5
6
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
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
Percent cost of research & development
Income
Year 1
Sales
$ 825,000
Cost of sales & marketing
231,000
Cost of research & development
181,500
$ 412,500
Gross Profit
Expenses
Insurance
Licensing
Miscellaneous
Payroll
Rent
Utilities
Total General Expenses
$
22,000
24,000
15,000
400,000
45,000
55,000
$ 561,000
28%
22%
$
$
Year 2
Year 3
Year 4
–
–
–
24,250
25,250
$
$
25,563
26,563
$
$
26,941
27,941
Year 5
$ 1,800,000
504,000
396,000
$ 900,000
$
443,000
517,300
542,330
28,388
29,387
18,233
618,563
58,000
$ 550,500
61,150
$ 630,575
64,457
$ 661,668
67,930
$ 762,500
$ (148,500) $ (550,500) $ (630,575) $ (661,668) $ 137,500
Initial Earnings
Projected Monthly Revenue
Estimated Gross Profit
$120,000
$1,000,000
$900,000
$800,000
$80,000
$700,000
$60,000
$600,000
Income
Revenue
$100,000
$40,000
$500,000
$400,000
$300,000
$20,000
$200,000
$0
0
10
20
30
40
Months in Operation
50
60
$100,000
$-
Year 1
Year 2
Year 3
Year 4
Year 5
Month
Month Number
Jan 2022
1
Feb 2022
2
Mar 2022
3
Apr 2022
4
May 2022
5
Jun 2022
6
Jul 2022
7
Aug 2022
8
Sep 2022
9
Oct 2022
10
Nov 2022
11
Dec 2022
12
Jan 2023
13
Feb 2023
14
Mar 2023
15
Apr 2023
16
May 2023
17
Jun 2023
18
Jul 2023
19
Aug 2023
20
Sep 2023
21
Oct 2023
22
Nov 2023
23
Dec 2023
24
Jan 2024
25
Feb 2024
26
Mar 2024
27
Apr 2024
28
May 2024
29
Jun 2024
30
Jul 2024
31
Aug 2024
32
Sep 2024
33
Oct 2024
34
Nov 2024
35
Dec 2024
36
Jan 2025
37
Feb 2025
38
Mar 2025
39
Apr 2025
40
May 2025
41
Jun 2025
42
Jul 2025
43
Aug 2025
44
Revenue
63,443
58,967
65,268
69,665
71,775
76,834
71,995
76,935
78,021
81,566
83,775
90,756
85,064
84,761
81,968
82,623
82,529
82,181
80,015
82,631
84,166
84,570
84,621
86,866
89,089
93,192
93,488
95,502
97,665
98,488
98,255
98,738
98,550
99,111
100,477
101,448
105,028
106,887
107,982
107,994
108,215
104,568
103,865
106,495
Sep 2025
Oct 2025
Nov 2025
Dec 2025
Jan 2026
Feb 2026
Mar 2026
Apr 2026
May 2026
Jun 2026
Jul 2026
Aug 2026
Sep 2026
Oct 2026
Nov 2026
Dec 2026
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
107,125
107,886
108,084
108,874
108,643
108,518
109,643
108,444
109,663
109,293
108,183
109,650
110,430
110,605
109,093
109,830
New Product Investor Returns
ChargeAll Full-Room Wireless Charger
Repayment Schedule
Startup
Year 1
Year 2
Year 3
Year 4
Year 5
Desired rate of return
Present value
Net present value (npv)
Internal rate of return
$
Payments
Net Cash Flow
(295,000) $
(295,000)
55,000
(240,000)
75,000
(165,000)
87,500
(77,500)
100,500
23,000
125,500
148,500
10.00%
$
(295,000)
Purchase answer to see full
attachment
We offer the bestcustom writing paper services. We have done this question before, we can also do it for you.
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.
