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.