Description
4 word doc problems with 3 starter excel files on Scenario Analytics, Chapter 5 problem 1 for starter excel 5-1,Chapter 5 problem 2 for starter excel 5-2,for chapter 5 problem 3 for this problem, you need to create your own worksheet and chapter5 problem4 for starter excel 5-4I will send the rest of the starter files after I choose a tutor
Unformatted Attachment Preview
Update Details for this Workbook
Last Update by:
Scenarios Updated on:
Scenarios for fiscal year-end:
Cash Outflows Fiscal Year:
First Quarter
Month End
Paid During
AP
Loans
Leases
Salaries
Utilities
Supplies
Total Outflow
$
$
$
25.000
850
600
$
$
$
25.000
850
600
$
$
$
25.000
850
600
Cash Payment Schedule for Accounts Payable
Purchase Amount
$
50.000 November
$
40.000 December
$
30.000 January
$
40.000 February
$
60.000 March
Total Cash Payments
November
December
January
August 29,2023
ccounts Payable
February
March
AP Balance
Payment Model for Accounts Payable
First Month Second Month
Third Month
60%
30%
10%
Loan for Borrowing Cash
APR
Repayment in Years
Amount Borrowed
Monthly Payment
$
3,75%
5
100.000
Lease for New Equipment
APR
Lease Duration in Years
Asset Cost
Residual Value
Monthly Payment
$
$
2,90%
10
250.000
20.000
Chapter 5, Problem 2
The purpose of this exercise is to analyze a worksheet to identify and correct any integrity
flaws. Read the scenario below, then open the Excel workbook related to this exercise. Use
the AnswerSheet worksheet for any written responses required for this exercise.
SCENARIO
Your coworker is analyzing a few financial calculations in Excel and is asking for your
assistance. The workbook contains calculations for estimating the future value of
investments and annual lease payments for equipment. The following was explained in an
e-mail that was sent with the workbook:
•
•
The Investment Plan worksheet shows the investment valuation estimates. I set the time
horizon for each investment to five years. For some reason the outputs for the functions I
used are all negative. I think the numbers are correct, so just assume these values are
positive.
The Lease Payments worksheet shows the equipment we need to lease. I estimated the
equipment cost at $675,000. Our budget is $45,000 a year for lease payments. My
calculations show the annual payments are $52,409.38, so will need to lower the equipment
cost.
ASSIGNMENT
1. Look at the FV function that was used in column F on the Investment Plans worksheet. Why
are all the outputs negative?
2. Your coworker assumes the outputs produced by the FV function are correct, but they are
just negative instead of positive. Do you agree with this statement?
3. Evaluate the function used to calculate the lease payments in the Lease Payments
worksheet. Is the function producing a reliable output?
4. Your coworker believes the annual lease payment exceeds the $49,000 budget. If this is
true, what can you do to adjust the Equipment Cost so the annual payments are exactly
$49,000?
5. Make any necessary corrections to the functions used in this workbook. Set the equipment
cost in the Lease Payments worksheet so the annual payments are exactly $49,000.
Chapter 5, Problem 3
Credit cards can offer people considerable flexibility when it comes to purchasing power.
However, this flexibility usually comes at a cost through a high APR on any unpaid
balances. If only the minimum payment is made on a credit card balance, this debt could be
carried for many years and cost thousands of dollars in interest expense. The purpose of
this exercise is to design an amortization schedule for a credit card where only the minimum
payment is made. Assume the bank charges an APR of 16% and that the current balance
on the credit card is $5,000. The minimum payment is established on a fourteen year
repayment period and payments are made at the end of each month. The design of your
amortization table must include the following:
1. Design is limited to one worksheet.
2. Assume credit card statements are issued on the first day of each month. Show the
statement date for each payment and the statement month on the amortization schedule.
The date for the first statement is 9/1/2020.
3. For each payment, show the total payment, how much of the payment is interest expense,
and how much is used to reduce the balance of the credit card. The schedule should also
show the credit card balance at the beginning of every month.
4. Once the amortization table is complete, use the data to create a PivotTable on a second
worksheet. The PivotTable should show how much total interest is paid each year and the
total interest paid on the $5,000 balance after fourteen years. Also show the total amount of
principal paid each year. The row heading should be the year sorted in chronological order
beginning with 2020. Finally, show the number of payments made each year. Since the first
statement is 9/1/2020, the year 2020 will have four payments.
5. Use column and/or row headings, add titles to your worksheets, and rename the worksheet
tabs with appropriate labels. Also, use appropriate number formats on both the amortization
worksheet and the PivotTable.
Chapter 5, Problem 4
Answer the following questions by executing the required skills on the starter file listed
above. Answer each question in the order it appears. Questions that are preceded with the
letters KO indicate you must only use your keyboard and not your mouse to execute the
required skill.
1. Open the Investments worksheet. In cell B5, enter a function that will calculate the present
value of an investment using the data in the range B2:B4. Assume this investment will be
made at the beginning of the first year.
2. Consider the present value calculation in cell B5. Assume that there is only $275,000 that
can be invested today, but you still need to achieve a future value of $1,550,000. Use Goal
Seek to set the present value calculation to $269,000 by changing the Annual Rate of
Return.
3. In cell B11, enter a function that will calculate the present value of an investment that will
provide funding for the cost in cell B10. Use the data in the range B8:B10 to set up the
function. Assume that payments are made at the beginning of each year.
4. Enter a function in cell B17 that will calculate the monthly payment on an auto loan. Use the
data in the range B14:B16 to create the function. Assume the monthly payment are made at
the end of each month.
5. Enter a function in cell B24 that will calculate the future value of a recurring investment. Use
the data in the range B20:B23 to create the function. Assume that investments are made at
the end of each month.
6. Open the Loan worksheet. Use the necessary data in the range B2:B5 to create a formula
in cell B6 that calculates the Loan Principal.
7. Enter the date 12/17/2022 into cell B7.
8. Enter a formula into cell B8 that displays a date 90 days from the date entered into cell B7.
9. Enter a function into cell B9 that shows the date that occurs on the last day of the month,
one month after the date in cell B8.
10. Enter a function into cell B10 that shows just the year of the date that is in cell B9.
11. Enter a function into cell B11 that calculates the monthly payments for the loan. Use the
data in the range B2:B6 to create the function. Assume that payments are made at the end
of each month.
12. Open the Lease worksheet. Enter a formula into cell B6 that calculates the monthly lease
payments. Use the data in the range B2:B5 to create the function. Assume that payments
are made at the end of each month.
13. Apply protection to the Lease worksheet such that users can edit the range B2:B5. Name
this range Lease Data.
14. Open the Summary worksheet. Use a cell reference in cell B2 to display the value in cell B4
on the Lease worksheet.
15. Use a cell reference in cell B3 to display the value in cell B6 on the Loan worksheet.
16. Enter a formula in cell B4 that adds the values in cells B5 and B11 on the Investments
worksheet.
17. Enter a formula in cell B5 that adds the values in cells B17 and B24 on the Investments
worksheet.
18. Save and close your workbook.
Chapter 5, Problem 1
Evaluating scenarios to determine the amount of cash that will be paid or become an
outflow for a business is a critical exercise. If cash outflows are more than what is planned,
a company runs the risk of not being able to pay its obligations. In addition, it may be very
difficult to borrow additional funds to pay for these obligations which may put the company
at risk entering bankruptcy. The purpose of this exercise is to construct a basic cash outflow
schedule to help a company determine how much cash will be spent during the first quarter.
The company’s total cash outflow for any given month must not exceed $80,000.
Begin the exercise by opening the file named Chapter 5, Problem 1. Questions that are
preceded with the letters KO indicate you must only use your keyboard and not your mouse
to execute the required skill.
1.
2.
Type your name into cell B2 in the Update Status worksheet. This worksheet will serve
as a data internal control as it provides information regarding who is making changes to
the workbook, the date the changes were made, and the fiscal year that is being
estimated.
Type today’s date into cell B3.
3.
This exercise will assume that the company’s fiscal year ends on December 31 every
year. In cell B4, type the December 31 date using one year later from the date entered
into cell B3. For example, if the date typed into cell B3 is 10/20/2020, then the date in
cell B4 should be 12/31/2021.
4.
Open the Cash Flow worksheet. Type the date 1/31/2022 into cell B2. This cash outflow
schedule will show total cash payments for the first quarter. The first quarter for this
company’s fiscal year is January, February, and March which is the same as a calendar
year.
5.
Use the EOMONTH function in cell C2 to show the last day of the next month which is
February. Begin the function by typing an equal sign =, the function name “eomonth”,
and an open parenthesis.
6.
Click cell B2 to define the “start_date” argument. Then type a comma to advance to the
“months” argument.
7.
Type the number 1 to define the “months” argument. Then, type a closing parenthesis
and tap the ENTER key on your keyboard.
8.
The output of the EOMONTH function should be the number 44620. Click cell C2 and
change the format by clicking the DOWN arrow for the number format dropdown menu
in the Home tab of the Ribbon. Select the Short Date option from the number dropdown
list.
9.
Copy cell C2 and paste it into cell D2. Row 2 should now show the last day for each
month in the first quarter.
10. Click cell D1. Enter the YEAR function to show just the year for the date that is entered
into cell B2. Begin the function by typing an equal sign =, followed by the function name
“year”, and an open parenthesis.
11. Click cell B2, type a closing parenthesis and tap the ENTER key on your keyboard.
12. Click cell B3. Use the TEXT function to show the full month name for the date entered
into cell B2. Begin the function by typing an equal sign =, the function name “text”, and
an open parenthesis.
13. Click cell B2 to define the “value” argument. Type a comma to advance to the
“format_text” argument.
14. Type an open quotation followed by the letter “m” four times and then a closing
quotation. The “format_text” argument should appear exactly as shown here: “mmmm”.
It is important to remember to include the quotations as shown.
15. Type a closing parenthesis and then tap the ENTER key on your keyboard.
16. Copy cell B3 and paste it into the range C3:D3.
17. Open the AP worksheet. Accounts payable (AP) is a significant source of cash outflows
for a company. This account includes items that were purchased on credit where the
company is given an invoice and has a certain amount of time to pay it. For example, a
company may purchase inventory from a supplier to sell to customers. The supplier may
allow the company to pay the invoice in sixty days. The amount owed on this invoice will
appear in the company’s accounts payable (AP) account until the invoice is paid.
18. Click cell C3 and start a formula by typing an equal sign =.
19. Click cell A3 and then tap the F4 key on your keyboard to add an absolute reference to
this cell. This is the value of all the items purchased in the month of November.
20. Type an asterisk * for multiplication.
21. Click the AP Pay Model worksheet tab. This worksheet shows a model of when and how
much the company pays for purchases. Since invoices can vary in how long a company
has to pay, this worksheet shows what the expected pay percent is for all purchases in
any given month.
22. Click cell A3 on the AP Pay Model worksheet. This cell shows that 60% of the
purchases in any given month will be paid in that same month.
23. Complete the formula by tapping the ENTER key on your keyboard. Since there were
$50,000 of items purchased in November, 60% or $30,000 will be paid in November.
24. Copy cell C3 and paste it into the range D3:E3. Notice that relative referencing changes
the cell reference that was used from the AP Pay Model worksheet. Also, you will see
that $5,000 of purchases in November will be paid in January. This is why November is
included in the cash outflow schedule for the first quarter.
25. Click cell D4. Start a formula by typing an equal sign =, click cell A4, and tap the F4 key
on your keyboard to add an absolute reference to this cell.
26. Type an asterisk * and then click cell A3 on the AP Pay Model worksheet.
27. Tap the ENTER key on your keyboard and then copy cell D4 and paste it into the range
E4:F4.
28. Start a formula in cell E5 by typing an equal sign =, click cell A5 and add an absolute
reference.
29. Type an asterisk * and then click cell A3 on the AP Pay Model worksheet. Tap the
ENTER key to complete the formula.
30. Copy cell E5 and paste it into the range F5:G5.
31. Enter a formula in cell F6 that multiplies cell A6 by cell A3 on the AP Pay Model
worksheet. Put an absolute reference on cell A6 in your formula.
32. Copy cell F6 and paste it into cell G6.
33. Enter a formula in cell G7 that multiplies cell A7 by cell A3 on the AP Pay Model
worksheet.
34. Enter a SUM function in cell C8 on the AP worksheet that sums the values in the range
C3:C7.
35. Copy cell C8 and paste it into the range D8:H8.
36. Enter a formula in cell H3 that subtracts from cell A3 the sum of the values in the range
C3:G3. When constructing the formula, add cell A3 first, type a minus sign -, and then
add the SUM function that will sum the values in the range C3:G3. The output should be
zero. This formula is calculating how much of the purchase amount in cell A3 has not
been paid. Since all purchases from November will have been paid by the end of
January, the unpaid amount that would be left in the AP account at the end of the first
quarter would be zero.
37. Copy cell H3 and paste it into the range H4:H7.
38. Open the Loan worksheet.
39. Click cell B5. Begin the PMT function by typing an equal sign =, the function name
“pmt”, and an open parenthesis. The purpose of this function is to determine the monthly
payments for the loan details in the range B3:B4.
40. To define the “rate” argument, click cell B2, type a front slash / for division, and type the
number 12. Since the PMT function is calculating monthly payments, the APR is divided
by 12.
41. Type a comma to advance to the “nper” argument.
42. Click cell B3, type an asterisk *, and then type the number 12. This will convert the
duration of the loan from years to months.
43. Type a comma to advance to the “pv” argument.
44. Type a minus sign – and then click cell B4.
45. Complete the function by typing a closing parenthesis and then tap the ENTER key on
your keyboard. Since there will not be a lump-sum payment at the end of the loan, and
since payments will be made at the end of the month, the last two arguments do not
have to be defined. Excel will assume zero for both.
46. Open the Lease worksheet.
47. Click cell B6. Begin the PMT function by typing an equal sign =, the function name
“pmt”, and an open parenthesis. The purpose of this function is to determine the monthly
payments for the equipment lease details in the range B2:B5.
48. Define the “rate” argument by dividing the APR in cell B2 by 12.
49. Define the “nper” argument by multiplying the lease duration in years in cell B3 by 12.
50. Define the “pv” argument with cell B4. This cell reference must be preceded by a minus
sign.
51. Define the “fv” argument with cell B5.
52. Define the “type” argument with number 1. This lease will assume payments are
required at the beginning of the month. To complete the function, type a closing
parenthesis and tap the ENTER key on your keyboard.
53. Open the Cash Flow worksheet and click cell B4.
54. Enter a cell reference that will display the value in cell E8 on the AP worksheet. Type an
equal sign =, click the AP worksheet tab, click cell E8, and tap the ENTER key on your
keyboard.
55. Copy cell B4 on the Cash Flow worksheet and paste into the range C4:D4.
56. Click cell B5 and enter a cell reference that will display the value in cell B5 on the Loan
worksheet.
57. Click cell C5 on the Cash Flow worksheet and enter a cell reference that will display the
value in cell B5 on the Cash Flow worksheet. Since the monthly payments are the same
every month, what every value is displayed in cell B5 can also be displayed in cell C5.
58. Copy cell C5 and paste it into cell D5.
59. Click cell B6 and enter a cell reference that will display the value in cell B6 on the Lease
worksheet.
60. Enter a cell reference in cell C6 that will display the value in cell B6.
61. Copy cell C6 and paste it into cell D6.
62. Enter a SUM function into cell B10 that will sum the values in the range B4:B9.
63. Copy cell B10 and paste it into the range C10:D10.
64. The total cash outflow in cell D10 should be $81,534 which exceeds the limit of $80,000.
Use Goal Seek to change the purchase value in cell A7 on the AP worksheet so the
total spend in cell D10 is equal to $80,000. Begin by clicking cell D10 on the Cash Flow
worksheet.
65. Click the What-If Analysis button in the Data tab of the Ribbon and select Goal Seek
from the dropdown menu.
66. Click the “To value” input box in the Goal Seek dialog box and type the number “80000”.
67. Click the Collapse dialog box button next to the “By changing cell” input box.
68. Click the AP worksheet tab and then click cell A7.
69. Tap the ENTER key on your keyboard and then click the OK button on the Goal Seek
dialog box.
70. Click the OK button on the Goal Seek Status dialog box. Goal Seek should have
reduced the amount in cell A7 on the AP worksheet in order to achieve the $80,000 in
total cash outflow for the month of March.
71. Save and close your workbook.
Update Details for this Workbook
Last Update by:
Scenarios Updated on:
Scenarios for fiscal year-end:
Cash Outflows Fiscal Year:
First Quarter
Month End
Paid During
AP
Loans
Leases
Salaries
Utilities
Supplies
Total Outflow
$
$
$
25,000
850
600
$
$
$
25,000
850
600
$
$
$
25,000
850
600
Cash Payment Schedule for Accounts Payable
Purchase Amount
$
50,000 November
$
40,000 December
$
30,000 January
$
40,000 February
$
60,000 March
Total Cash Payments
November
December
January
August 29,2023
ccounts Payable
February
March
AP Balance
Payment Model for Accounts Payable
First Month Second Month
Third Month
60%
30%
10%
Loan for Borrowing Cash
APR
Repayment in Years
Amount Borrowed
Monthly Payment
$
3.75%
5
100,000
Lease for New Equipment
APR
Lease Duration in Years
Asset Cost
Residual Value
Monthly Payment
$
$
2.90%
10
250,000
20,000
Chapter 5, Problem 4
Answer the following questions by executing the required skills on the starter file listed
above. Answer each question in the order it appears. Questions that are preceded with the
letters KO indicate you must only use your keyboard and not your mouse to execute the
required skill.
1. Open the Investments worksheet. In cell B5, enter a function that will calculate the present
value of an investment using the data in the range B2:B4. Assume this investment will be
made at the beginning of the first year.
2. Consider the present value calculation in cell B5. Assume that there is only $275,000 that
can be invested today, but you still need to achieve a future value of $1,550,000. Use Goal
Seek to set the present value calculation to $269,000 by changing the Annual Rate of
Return.
3. In cell B11, enter a function that will calculate the present value of an investment that will
provide funding for the cost in cell B10. Use the data in the range B8:B10 to set up the
function. Assume that payments are made at the beginning of each year.
4. Enter a function in cell B17 that will calculate the monthly payment on an auto loan. Use the
data in the range B14:B16 to create the function. Assume the monthly payment are made at
the end of each month.
5. Enter a function in cell B24 that will calculate the future value of a recurring investment. Use
the data in the range B20:B23 to create the function. Assume that investments are made at
the end of each month.
6. Open the Loan worksheet. Use the necessary data in the range B2:B5 to create a formula
in cell B6 that calculates the Loan Principal.
7. Enter the date 12/17/2022 into cell B7.
8. Enter a formula into cell B8 that displays a date 90 days from the date entered into cell B7.
9. Enter a function into cell B9 that shows the date that occurs on the last day of the month,
one month after the date in cell B8.
10. Enter a function into cell B10 that shows just the year of the date that is in cell B9.
11. Enter a function into cell B11 that calculates the monthly payments for the loan. Use the
data in the range B2:B6 to create the function. Assume that payments are made at the end
of each month.
12. Open the Lease worksheet. Enter a formula into cell B6 that calculates the monthly lease
payments. Use the data in the range B2:B5 to create the function. Assume that payments
are made at the end of each month.
13. Apply protection to the Lease worksheet such that users can edit the range B2:B5. Name
this range Lease Data.
14. Open the Summary worksheet. Use a cell reference in cell B2 to display the value in cell B4
on the Lease worksheet.
15. Use a cell reference in cell B3 to display the value in cell B6 on the Loan worksheet.
16. Enter a formula in cell B4 that adds the values in cells B5 and B11 on the Investments
worksheet.
17. Enter a formula in cell B5 that adds the values in cells B17 and B24 on the Investments
worksheet.
18. Save and close your workbook.
Chapter 5, Problem 1
Evaluating scenarios to determine the amount of cash that will be paid or become an
outflow for a business is a critical exercise. If cash outflows are more than what is planned,
a company runs the risk of not being able to pay its obligations. In addition, it may be very
difficult to borrow additional funds to pay for these obligations which may put the company
at risk entering bankruptcy. The purpose of this exercise is to construct a basic cash outflow
schedule to help a company determine how much cash will be spent during the first quarter.
The company’s total cash outflow for any given month must not exceed $80,000.
Begin the exercise by opening the file named Chapter 5, Problem 1. Questions that are
preceded with the letters KO indicate you must only use your keyboard and not your mouse
to execute the required skill.
1.
2.
Type your name into cell B2 in the Update Status worksheet. This worksheet will serve
as a data internal control as it provides information regarding who is making changes to
the workbook, the date the changes were made, and the fiscal year that is being
estimated.
Type today’s date into cell B3.
3.
This exercise will assume that the company’s fiscal year ends on December 31 every
year. In cell B4, type the December 31 date using one year later from the date entered
into cell B3. For example, if the date typed into cell B3 is 10/20/2020, then the date in
cell B4 should be 12/31/2021.
4.
Open the Cash Flow worksheet. Type the date 1/31/2022 into cell B2. This cash outflow
schedule will show total cash payments for the first quarter. The first quarter for this
company’s fiscal year is January, February, and March which is the same as a calendar
year.
5.
Use the EOMONTH function in cell C2 to show the last day of the next month which is
February. Begin the function by typing an equal sign =, the function name “eomonth”,
and an open parenthesis.
6.
Click cell B2 to define the “start_date” argument. Then type a comma to advance to the
“months” argument.
7.
Type the number 1 to define the “months” argument. Then, type a closing parenthesis
and tap the ENTER key on your keyboard.
8.
The output of the EOMONTH function should be the number 44620. Click cell C2 and
change the format by clicking the DOWN arrow for the number format dropdown menu
in the Home tab of the Ribbon. Select the Short Date option from the number dropdown
list.
9.
Copy cell C2 and paste it into cell D2. Row 2 should now show the last day for each
month in the first quarter.
10. Click cell D1. Enter the YEAR function to show just the year for the date that is entered
into cell B2. Begin the function by typing an equal sign =, followed by the function name
“year”, and an open parenthesis.
11. Click cell B2, type a closing parenthesis and tap the ENTER key on your keyboard.
12. Click cell B3. Use the TEXT function to show the full month name for the date entered
into cell B2. Begin the function by typing an equal sign =, the function name “text”, and
an open parenthesis.
13. Click cell B2 to define the “value” argument. Type a comma to advance to the
“format_text” argument.
14. Type an open quotation followed by the letter “m” four times and then a closing
quotation. The “format_text” argument should appear exactly as shown here: “mmmm”.
It is important to remember to include the quotations as shown.
15. Type a closing parenthesis and then tap the ENTER key on your keyboard.
16. Copy cell B3 and paste it into the range C3:D3.
17. Open the AP worksheet. Accounts payable (AP) is a significant source of cash outflows
for a company. This account includes items that were purchased on credit where the
company is given an invoice and has a certain amount of time to pay it. For example, a
company may purchase inventory from a supplier to sell to customers. The supplier may
allow the company to pay the invoice in sixty days. The amount owed on this invoice will
appear in the company’s accounts payable (AP) account until the invoice is paid.
18. Click cell C3 and start a formula by typing an equal sign =.
19. Click cell A3 and then tap the F4 key on your keyboard to add an absolute reference to
this cell. This is the value of all the items purchased in the month of November.
20. Type an asterisk * for multiplication.
21. Click the AP Pay Model worksheet tab. This worksheet shows a model of when and how
much the company pays for purchases. Since invoices can vary in how long a company
has to pay, this worksheet shows what the expected pay percent is for all purchases in
any given month.
22. Click cell A3 on the AP Pay Model worksheet. This cell shows that 60% of the
purchases in any given month will be paid in that same month.
23. Complete the formula by tapping the ENTER key on your keyboard. Since there were
$50,000 of items purchased in November, 60% or $30,000 will be paid in November.
24. Copy cell C3 and paste it into the range D3:E3. Notice that relative referencing changes
the cell reference that was used from the AP Pay Model worksheet. Also, you will see
that $5,000 of purchases in November will be paid in January. This is why November is
included in the cash outflow schedule for the first quarter.
25. Click cell D4. Start a formula by typing an equal sign =, click cell A4, and tap the F4 key
on your keyboard to add an absolute reference to this cell.
26. Type an asterisk * and then click cell A3 on the AP Pay Model worksheet.
27. Tap the ENTER key on your keyboard and then copy cell D4 and paste it into the range
E4:F4.
28. Start a formula in cell E5 by typing an equal sign =, click cell A5 and add an absolute
reference.
29. Type an asterisk * and then click cell A3 on the AP Pay Model worksheet. Tap the
ENTER key to complete the formula.
30. Copy cell E5 and paste it into the range F5:G5.
31. Enter a formula in cell F6 that multiplies cell A6 by cell A3 on the AP Pay Model
worksheet. Put an absolute reference on cell A6 in your formula.
32. Copy cell F6 and paste it into cell G6.
33. Enter a formula in cell G7 that multiplies cell A7 by cell A3 on the AP Pay Model
worksheet.
34. Enter a SUM function in cell C8 on the AP worksheet that sums the values in the range
C3:C7.
35. Copy cell C8 and paste it into the range D8:H8.
36. Enter a formula in cell H3 that subtracts from cell A3 the sum of the values in the range
C3:G3. When constructing the formula, add cell A3 first, type a minus sign -, and then
add the SUM function that will sum the values in the range C3:G3. The output should be
zero. This formula is calculating how much of the purchase amount in cell A3 has not
been paid. Since all purchases from November will have been paid by the end of
January, the unpaid amount that would be left in the AP account at the end of the first
quarter would be zero.
37. Copy cell H3 and paste it into the range H4:H7.
38. Open the Loan worksheet.
39. Click cell B5. Begin the PMT function by typing an equal sign =, the function name
“pmt”, and an open parenthesis. The purpose of this function is to determine the monthly
payments for the loan details in the range B3:B4.
40. To define the “rate” argument, click cell B2, type a front slash / for division, and type the
number 12. Since the PMT function is calculating monthly payments, the APR is divided
by 12.
41. Type a comma to advance to the “nper” argument.
42. Click cell B3, type an asterisk *, and then type the number 12. This will convert the
duration of the loan from years to months.
43. Type a comma to advance to the “pv” argument.
44. Type a minus sign – and then click cell B4.
45. Complete the function by typing a closing parenthesis and then tap the ENTER key on
your keyboard. Since there will not be a lump-sum payment at the end of the loan, and
since payments will be made at the end of the month, the last two arguments do not
have to be defined. Excel will assume zero for both.
46. Open the Lease worksheet.
47. Click cell B6. Begin the PMT function by typing an equal sign =, the function name
“pmt”, and an open parenthesis. The purpose of this function is to determine the monthly
payments for the equipment lease details in the range B2:B5.
48. Define the “rate” argument by dividing the APR in cell B2 by 12.
49. Define the “nper” argument by multiplying the lease duration in years in cell B3 by 12.
50. Define the “pv” argument with cell B4. This cell reference must be preceded by a minus
sign.
51. Define the “fv” argument with cell B5.
52. Define the “type” argument with number 1. This lease will assume payments are
required at the beginning of the month. To complete the function, type a closing
parenthesis and tap the ENTER key on your keyboard.
53. Open the Cash Flow worksheet and click cell B4.
54. Enter a cell reference that will display the value in cell E8 on the AP worksheet. Type an
equal sign =, click the AP worksheet tab, click cell E8, and tap the ENTER key on your
keyboard.
55. Copy cell B4 on the Cash Flow worksheet and paste into the range C4:D4.
56. Click cell B5 and enter a cell reference that will display the value in cell B5 on the Loan
worksheet.
57. Click cell C5 on the Cash Flow worksheet and enter a cell reference that will display the
value in cell B5 on the Cash Flow worksheet. Since the monthly payments are the same
every month, what every value is displayed in cell B5 can also be displayed in cell C5.
58. Copy cell C5 and paste it into cell D5.
59. Click cell B6 and enter a cell reference that will display the value in cell B6 on the Lease
worksheet.
60. Enter a cell reference in cell C6 that will display the value in cell B6.
61. Copy cell C6 and paste it into cell D6.
62. Enter a SUM function into cell B10 that will sum the values in the range B4:B9.
63. Copy cell B10 and paste it into the range C10:D10.
64. The total cash outflow in cell D10 should be $81,534 which exceeds the limit of $80,000.
Use Goal Seek to change the purchase value in cell A7 on the AP worksheet so the
total spend in cell D10 is equal to $80,000. Begin by clicking cell D10 on the Cash Flow
worksheet.
65. Click the What-If Analysis button in the Data tab of the Ribbon and select Goal Seek
from the dropdown menu.
66. Click the “To value” input box in the Goal Seek dialog box and type the number “80000”.
67. Click the Collapse dialog box button next to the “By changing cell” input box.
68. Click the AP worksheet tab and then click cell A7.
69. Tap the ENTER key on your keyboard and then click the OK button on the Goal Seek
dialog box.
70.
