In the Expansion worksheet, type in the following terms of the loan, using the Accounting format for
Question:
In the Expansion worksheet, type in the following terms of the loan, using the Accounting format for the loan
amount and Percent Style formatting with a single decimal showing for the interest rate.
Amount of Loan $ 200,000
Period (years) 15
Interest Rate (per year) 4.9%
3. In cell B6, create a PMT() function that calculates the monthly payment for the loan as a positive value. (Hint: Be
sure arguments in your function are converted into months to calculate the monthly payment.)
4. Format the Payment using Accounting Number Format.
5. In B7 of the Summary worksheet, create a cell reference linking to the monthly payment from the Expansion
worksheet.
6. In B8 of the Summary worksheet, create a formula that calculates the total cost of warehouse space.
Project Update: Now that you know the costs associated with expanding, you decide to talk with a financial advisor
about your plans. After looking at your sales history, he advises you that your total costs for warehouse space should not
exceed 5% of the total value of your inventory. You are going to use this ratio to decide whether or not to expand at this
time. First, you need to determine the total value of your inventory. Then you will determine the ratio of warehouse
space to inventory on hand.
Analyze the Data
1. In column D of the Inventory worksheet under the Value header, calculate the value of the items in your
inventory, using the information you have about Price and Quantity.
2. In cell D73, calculate the total value of all items in your inventory.
3. Return to the Summary worksheet, and create a reference in B3 that links it to the cell containing the total value
of all items from your Inventory worksheet.
4. In B10 of the Summary worksheet, write a formula that divides the Total Cost of your warehouse space by the
value of your Inventory.
Follow-up: If your Warehouse Ratio is above 5%, you shouldn’t expand. However, loans can be negotiated, and you decided not to give up on your project. Instead, you follow up with the bank and ask that they change the terms of the loan from a 15-year payback period to a 30-year payback period. Adjust your work to reflect this change.
1. Return to the Expansion worksheet and change the Period from 15 years to 30.
2. Go to the Summary worksheet, and see if you are below the 5% threshold for your Warehouse Ratio.
3. Preview all three worksheets and make any changes needed so they each print on one page.
Expert Answer:
