# Step-by-Step Solution Structure ## Part 1: Loan Amortization Spreadsheet Design ### **Step 1: Setting Up Loan Amortization Table in Excel** **Inputs (User-Adjustable in Excel):** - Loan Amount (`Principal`) - Number of payments per year (`nper_year`) - Number of years (`years`) - Nominal annual interest rate (`r`) **Calculations:** - Total number of payments: ``` nper = nper_year * years ``` - Periodic interest rate: ``` rate = r / nper_year ``` - Payment per period (using PMT function): ``` =PMT(rate, nper, -Principal) ``` - For each period (row): - **Interest payment:** ``` =Previous Balance * rate ``` - **Principal payment:** ``` =Payment - Interest Payment ``` - **Balance due:** ``` =Previous Balance - Principal Payment ``` **Explanation:** This step sets up the formulas required to compute each period’s interest, principal, and ending balance. The PMT function calculates the fixed payment. You use the output of each period as the starting value for the next. --- ### **Step 2: Applying to Specific Cases and Summarizing Results** #### **a. $45,600 Loans (36, 50, 72 months)** - **Sheet 1:** 4.36%, 36 months (3 years) - **Sheet 2:** 5.36%, 50 months (4.17 years) - **Sheet 3:** 6.34%, 72 months (6 years) **For each loan option:** 1. Use the formulas from Step 1 to fill out the full amortization table. 2. To answer: - **(1) Total interest paid for each period:** Sum the interest in each period. - **(2) Total interest paid for each year:** Sum interest payments grouped by year. - **(3) Total interest paid:** Sum all interest payments for the loan. - **(4) Comparison:** Tabulate results for all three scenarios, noting how lower rates and shorter terms reduce total interest. **Explanation:** This step applies the general formulas to specific input values for each loan scenario and summarizes the results for analysis. --- #### **b. $189,500 Loans (15, 20, 30 years at 4.1%)** - **Sheet 4:** 15 years - **Sheet 5:** 20 years - **Sheet 6:** 30 years **For each loan option:** - Build the amortization table as in Step 1. - Calculate: - **(1) Total interest paid for each year:** Sum interest per year. - **(2) Total interest paid:** Sum all interest payments. - **(3) Comparison:** Shorter terms = higher payment but less total interest. **Explanation:** This step demonstrates the impact of loan term length on interest paid, using the same calculation method as in Step 1. --- ### **Final Answer: Excel Spreadsheet Structure** Each scenario should be placed in a separate tab: - **Inputs area** at the top for easy data entry - **Amortization table** for each case - **Summary calculations** (total interest, yearly breakdown) - **Comparison sheet** to discuss results and trade-offs --- ## Part 2: Case 9 - Recycling Analysis (Excel Setup) ### **Step 1: Data Setup and Assumptions** **Inputs (on separate Excel sheets):** - **General:** - Number of containers hauled per day (average) - Tons per container (use 11 tons for all scenarios) - Hauling cost per container - Rebate per ton ($5/ton, constant) - **Cardboard:** - Cardboard specific costs/revenues - **Wood Pallet:** - Wood pallet specific costs/revenues **Assumptions:** - $5/ton rebate baseline is constant - Cardboard and pallet are mutually exclusive - Use 11 tons/container for conservative savings **Explanation:** This step ensures all calculations use correct, consistent data and that the scope matches the case requirements. --- ### **Step 2: Calculation and Reporting** **For both Cardboard and Pallet options:** - Calculate annual hauling costs, rebates, and net savings for each scenario - Compare in-house vs. non-in-house scenarios **Create a summary report (in Excel):** - General information tab (data and assumptions) - Cardboard tab (detailed analysis) - Wood Pallet tab (detailed analysis) - Summary tab (side-by-side comparison and recommendation) **Explanation:** This step provides a clear, structured analysis for management, with all assumptions and results transparently displayed. --- ## **Final Answer: Deliverables** - **One Excel file with separate tabs** for each loan scenario and recycling analysis. - **Each tab includes:** - Inputs section - Amortization or cost/savings table - Yearly and total summaries - Explanation of results - **A summary/comparison tab** for each part, discussing pros and cons. --- **Note:** The actual spreadsheet must be created in Excel software using the formulas and structure above. This answer lays out the steps and structure for implementation.