9-17a Tutorial 9 Practice Review Assignment Data File needed for the Review Assignments: Restaurant.xlsx Sylvia has some new figures for the business plan for Jerel's. She has received slightly better conditions on the business loan, which means that she needs less money from investors to fund the restaurant. She has also modified the depreciation schedule for the restaurant's tangible assets. She wants you to make the necessary changes in the workbook to calculate the restaurant's financial data for the next five years. Complete the following: 1. 1. Open the Restaurant workbook located in the Excel9 ? Review folder included with your Data Files, and then save the workbook as Restaurant Plan in the location specified by your instructor. 2. 2. In the Documentation worksheet, enter your name and the date. 3. 3. In the Loan Analysis worksheet, in cell C5, enter the 6.85% annual interest rate that the restaurant has secured for a business loan. 4. 4. In rows 8 through 11, calculate the following possible loan scenarios: 1. In row 8, for a $450,000 business loan that is repaid in 10 years at a 6.85 percent interest rate with quarterly payments, use the PMT function to calculate the quarterly payments. 2. In row 9, for a $450,000 loan at a 6.85 percent interest rate with quarterly payments of $12,000 made over 10 years, use the FV function to calculate the principal at the end of 10 years. 3. In row 10, for a $450,000 loan at a 6.85 percent interest rate that is completely repaid with quarterly payments of $12,000, use the NPER function to calculate the number of quarterly payment periods and then calculate the number of years required to repay the loan. 4. In row 11, with quarterly payments of $18,000 for 10 years at a 6.85 percent interest rate, use the PV function to calculate the largest loan the restaurant could completely repay in 10 years. 5. 5. In the Startup Plan worksheet, in cell C26, enter a business loan amount of $400,000. 6. 6. In the Amortization Schedule worksheet, in the range B6:G6, enter the conditions for a $400,000 loan at a 6.85 percent interest rate with quarterly payments to be repaid in 10 years. Reference the loan value from cell C26 in the Startup Plan worksheet. In cell H6, use the PMT function to calculate the amount of the quarterly payments required to repay the loan. 7. 7. In the range D10:G49, complete the amortization schedule using absolute references to the loan conditions in row 6 of the worksheet. Use the PPMT function to calculate the principal payment for each quarter, and use the IPMT function to calculate the interest payment for each quarter. Reduce the principal owed for each new quarter by the amount paid in the previous quarter. Verify that the loan is completely repaid by displaying the sum of the remaining principal and the last principal payment in cell D50. 8. 8. In the range C55:G59, calculate the cumulative interest and principal payments per year as follows: 1. In the range C57:G57, use the CUMPRINC function to calculate the cumulative principal payments in each of the first five years of the loan. Include absolute references to the loan conditions in row 6 as part of your calculations. 2. In the range C58:G58, use the CUMIPMT function to calculate the cumulative interest payments in each of the first five years of the loan. 3. In cells H57 and H58, calculate the total principal payments and interest payments in the first five years of the loan. 4. In the range C59:G59, calculate the remaining principal at the end of each of the first five years of the loan. 9. 9. In the Income Statement worksheet, in the range D8:F9, interpolate the Year 2 through Year 4 food and beverage revenue values assuming a growth trend. In the range C10:G10, calculate the total revenue for each year. 10. 10. In cell G4, enter 37% as the Percent Cost of Sales for the restaurant products. 11. 11. In the range D18:G25, extrapolate the general expenses from the provided Year 1 through Year 5 values assuming a 4 percent growth trend. In the range C26:G26, calculate the total expenses for each year. 12. 12. In the range C29:G29, calculate the restaurant's initial earnings for each year, which is equal to the gross profit minus the total general expenses. 13. 13. In the Startup Plan worksheet, in cell C12, enter $240,000 as the long-term tangible assets that will need to be depreciated. 14. 14. In the Depreciation Schedule worksheet, in cell C5, reference the tangible assets' value from cell C12 in the Startup Plan worksheet. In cell C6, enter 120,000 as the assets' salvage value. In cell C7, enter 20 as the useful lifetime of the assets. 15. 15. In the range C11:G11, calculate the yearly straight-line depreciation of the long-term assets using the SLN function. In the range C12:G12, calculate the cumulative depreciation through the first five years. In the range C13:G13, calculate the depreciated value of the assets at the end of each of the first five years. 16. 16. In the range C17:G17, use the DB function to calculate the yearly declining balance of the assets. In the range C18:G18, calculate the cumulative depreciation of the assets. In the range C19:G19, calculate the depreciated value of the assets at the end of each year. 17. 17. In the Income Statement worksheet, in the range C30:G30, enter formulas to reference the declining balance depreciation values in the range C17:G17 of the Depreciation Schedule worksheet. Calculate the restaurant's operating profit by subtracting the yearly depreciation from the yearly initial earnings. 18. 18. In the range C33:G33, enter formulas for the yearly interest expenses that reference the cumulative interest payments in the range C58:G58 of the Amortization Schedule worksheet. Enter the interest expenses as positive values. In the range C34:G34, calculate the restaurant's pretax profit by subtracting the interest expenses from the operating profit. 19. 19. In cell G5, enter 32% as the assumed tax rate. 20. 20. In the range C36:G36, use an IF statement to calculate the restaurant's tax liability for each of the first five years assuming a 32 percent tax rate. If the restaurant's pretax profit is negative, set the tax burden to $0. In the range C37:G37, calculate the restaurant's after-tax profit by subtracting the taxes owed from the pretax profit. 21. 21. In the Startup Plan worksheet, in cell C31, enter $150,000 as the amount Sylvia hopes to attract from investors. 22. 22. In the Investment Proposal worksheet, in cell C7, enter a reference to cell C31 in the Startup Plan worksheet as a negative cash flow. Sylvia proposes that the restaurant repay the investors $35,000 per year for five years. Enter these values into cells C8 and C9. In cell C10, use the RATE function to calculate the interest of the proposed repayment schedule. 23. 23. Investors will be repaid with a combination of yearly payments and dividends. 1. In the range C13:G13, enter a reference to the value in cell C8 to display $35,000. 2. In the range C14:G14, enter the following dividend schedule: $0 in Year 1, $5,000 in Year 2, $10,000 in Year 3 and Year 4, and $15,000 in Year 5. 3. In the range C15:G15, use the SUM function to calculate the total money investors will receive in Years 1 through 5. 24. 24. In the range C19:D24, determine the payback period and calculate the net cash flow to the investors. 25. 25. In cell D26, enter 12% as the desired rate of return. In cell D27, use the NPV function to calculate the present value of the payments made to the investors from the range C20:C24 using the desired rate of return specified in cell D26. In cell D28, enter a formula to calculate the net present value of their investment in the restaurant given their initial payment of $150,000. 26. 26. In cell D29, use the IRR function to calculate the internal rate of return of their investment. 27. 27. In the Income Statement worksheet, in the range C39:G39, enter a reference to the yearly dividend values paid to the shareholders as specified in the range C14:G14 of the Investment Proposal worksheet. In the range C41:G41, calculate the restaurant's retained earnings by subtracting the dividends from the after-tax profit. 28. 28. In the Balance Sheet worksheet, starting with cell G17, trace the #REF error in the workbook back to its source and correct it. 29. 29. Save the workbook, and then close it.

05/09/2015 Jerel's Author Date Purpose To perform a second financial analysis on a proposed restaurant in Gresham, Oregon qattachments_71ced9af44a7beb49237e3837d07a9994b6e7306.xlsx Page 1 Documentation 05/09/2015 Jerel's Start-Up Requirements Required Expenses Consulting Fees Architectural Design Building Expenses Miscellaneous Operations Total Required Expenses 15,000 25,000 200,000 150,000 $390,000 Required Assets Cash Long-Term Assets Total Required Assets TOTAL REQUIRED EXPENSES AND ASSETS 140,000 $140,000 $530,000 Start-Up Assets Non-Cash Assets Cash Assets Additional Available Cash Cash Balance on Starting Date TOTAL START-UP ASSETS 140,000 (240,000) (100,000) $(100,000) Liabilities and Capital Start-Up Liabilities Long-Term Business Loan Outstanding Debts Total Liabilities $- Start-up Capital Owners Investors Gresham Redevelopment Grant Total Available Capital 190,000 100,000 $290,000 Project Summary START-UP INVESTMENT START-UP EXPENSES INITIAL EQUITY TOTAL LIABILITIES AND EQUITY TOTAL START-UP FUNDING 290,000 (390,000) (100,000) $(100,000) $290,000 qattachments_71ced9af44a7beb49237e3837d07a9994b6e7306.xlsx Page 2 Startup Plan 05/09/2015 Jerel's Pro Forma Income Statement Percent Cost of Sales Assumed Tax Rate Income Food Beverages Total Revenue Direct Cost of Food Direct Cost of Beverages Total Cost of Sales Year 1 $900,000 300,000 Payroll Utilities Marketing Occupancy Costs Fees and Banking Insurance Leased Equipment Music & Entertainment TOTAL GENERAL EXPENSES Earnings Year 3 Year 4 Year 5 $1,400,000 450,000 - - - - - - GROSS PROFIT Expenses Year 2 - - - - Year 1 $450,000 60,000 40,000 90,000 35,000 25,000 15,000 10,000 Year 2 Year 3 Year 4 Year 5 Year 1 Year 2 Year 3 Year 4 Year 5 Initial Earnings Depreciation Operating Profit Interest Expense Pretax Profit Taxes Liability After Tax Profit Dividends to Shareholders Retained Earnings qattachments_71ced9af44a7beb49237e3837d07a9994b6e7306.xlsx 3 Page Income Statement 05/09/2015 Jerel's Pro Forma Balance Sheet Assets Current Assets Cash Accounts Receivable Inventory Total Current Assets Year 1 (100,000) (100,000) Property and Equipment Long-Term Assets Less Accumulated Depreciation Net Property & Equipment TOTAL ASSETS Liabilities and Equity Year 2 #REF! #REF! - #REF! Year 2 Year 4 #REF! #REF! - (100,000) Year 1 Year 3 #REF! #REF! #REF! Year 3 Year 5 #REF! #REF! #REF! Year 4 #REF! Year 5 Current Liabilities Total Current Liabilities - - - - - Long-Term Liabilities Total Long-Term Liabilities - - - - - Equity Initial Stock Less Start-Up Expenses Less Investor Repayment Accumulated Retained Earnings Total Equity 290,000 (390,000) (100,000) 290,000 (390,000) (100,000) 290,000 (390,000) (100,000) 290,000 (390,000) (100,000) 290,000 (390,000) (100,000) TOTAL LIABILITIES AND EQUITY (100,000) (100,000) (100,000) (100,000) (100,000) NET WORTH (100,000) #REF! #REF! #REF! #REF! qattachments_71ced9af44a7beb49237e3837d07a9994b6e7306.xlsx 4 Page Balance Sheet 05/09/2015 Jerel's Pro Forma Cash Flow Cash Inflows Year 1 Operating Profit Depreciation Add-Back Additional Cash Received New Borrowing Sales of Other Current Assets TOTAL CASH INFLOW Cash Outflows Loan Payment to Investors Dividends Estimated Tax Interest Payment Debt Payment TOTAL CASH OUTFLOW NET CASH FLOW CASH BALANCE Year 2 Year 3 Year 4 Year 5 - #REF! - - - - $- #REF! $- $- $- Year 1 Year 2 $- Year 4 Year 5 Page 5 $- $- $- #REF! #REF! $$(100,000) qattachments_71ced9af44a7beb49237e3837d07a9994b6e7306.xlsx Year 3 $- $#REF! $#REF! $#REF! Cash Flow 05/09/2015 Jerel's Loan Analysis Annual Interest Rate Financial Value Payments per Year Years Payments (NPER) Annual Rate Rate per Quarter (RATE) Business Loan (PV) Future Value (FV) Quarterly Payments (PMT) Quarterly Payment (PMT) Future Value (FV) Payments (NPER) Business Loan (PV) qattachments_71ced9af44a7beb49237e3837d07a9994b6e7306.xlsx Page 6 Loan Analysis 05/09/2015 Jerel's Loan Schedule Loan (PV) Payments per Year Years Payments (NPER) Annual Rate Rate per Period (RATE) Remaining Principal Principal Payment Interest Payment Total Payment Year 4 13 16 Year 5 17 20 Payment (PMT) Amortization Schedule Year Period 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 6 6 6 7 7 7 7 8 8 8 8 9 9 9 9 10 10 10 10 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Final Balance Cumulative Interest and Principal Payments per Year Quarters Year 1 1 4 Year 2 5 8 Year 3 9 12 Total Principal Interest Principal Remaining qattachments_71ced9af44a7beb49237e3837d07a9994b6e7306.xlsx Page 7 Amortization Schedule 05/09/2015 Jerel's Depreciation Long-Term Assets Salvage Value (Salvage) Life of Asset (Life) Straight-Line (SLN) 1 2 Year 3 4 5 2 Year 3 4 5 Yearly Depreciation Cumulative Depreciation Depreciated Asset Value Declining Balance (DB) 1 Yearly Depreciation Cumulative Depreciation Depreciated Asset Value qattachments_71ced9af44a7beb49237e3837d07a9994b6e7306.xlsx Page 8 Depreciation Schedule 05/09/2015 Jerel's Investment Analysis Proposed Repayment Schedule to Investors Investment (PV) Yearly Payments (PMT) Payments (NPER) Interest Rate (RATE) Year 1 Year 2 Payments Year 3 Year 4 Year 5 Net Cash Flow Payments Dividends Total Return on the Investment Startup Year 1 Year 2 Year 3 Year 4 Year 5 Desired Rate of Return Present Value (NPV) Net Present Value Internal Rate of Return (IRR) qattachments_71ced9af44a7beb49237e3837d07a9994b6e7306.xlsx 9 Page Investment Proposal