2 For the calendar year 2018 (in $ millions) 3 4 This exam requires you to forecast the Domestic Auto Parts statements, based on your determination of changes in the ratios. 5 STEP 1: Compute all the ratios for the historical year 2018, in the ligh blue area of the spreadsheet. 6 STEP 2: Forecast all the ratlos in the dark blue area for the next two forecasted years, based on facts from the text of the case. You only need to forecast changes for those ratios you will write about others may be kept at their 2018 value. 7 STEP 3: Complete the forecast of the Income Statement and Balance sheet in the ligh blue area, balancing the Balance Sheet using either the Extra Bank Loan or Extra Cash accounts. B STEP 4: In the green box arca, give a fact from the text of the case used in forecasting the ratio on that row, and explain how that fact relates to the changed ratio value. Write about three different ratias anly, and choose only ratios with a green bax on their row. 9 Remember: Writing about more than 3 will penalize your grade, because the 3 worst written responses will be used. Do not write about more than 3. 10 71 Added Assumptions 12 DAP also performs consulting services, not mentioned in the case, with the units sold per year and the average price per unit shown below. 13 Income Taxes Payable equals 100% of the Income Tax Expense for that year. 14 The Mortgage Loan starts January 1, 2019, with a starting balance of $50,000 (in $ millions). It is a 15 year loan, with an 8% interest rate and monthly payments. The Mortgage Loan Table provided is NOT set up for that loan, you will need to set it up for that loan. 15 There are several numbers in the forecast (Selling, General & Administrative Expense, Minimum Cash, Land, Buildings, Common Stock) that are bolded numbers, and should not be changed. 16 77 Historical Farecast Forecast 2018 2019 2020 19 FORECASTING RATIOS 20 Auto Parts solc per year (millions of units) 21 Carsulting Units sold per year (millions of units) 22 23 Average Price of Auto Parts sold (per unit) $ 5,200.00 24 Average Price of Consulting Units sold (per unit) $ Facts from the text of the case used in forecasting the ratio write about three different ratias, and only ratios with a green 25 26 Auto Parts Cast of Goods Sold, as percent of Auto Parts Sales 27 Maintenance & Repairs Expense, as percent of Auto Parts sales 28 Labar Expense, as percent of combined sales 29 Reasearch & Development Expense, as percent of combined sales 265.00 81 Days of Receivables (Auto Parts sales only! 32 Days of inwentary 23 Days of Accounts Payable (Auto Parts Cost of Goods Sold 34 45 Average Depreciation Years - Buildings 38 Average Tax Rate 7 Average Interest Rate - Extra Bank Loan 38 29 INCOME STATEMENT H0 Auto Parts Sales Revenue 41 Consulting Sales Revenue 12 $ $ 155,000 5,000 Auto Parts Cost of Goods Sold $ 65,000 $ $ Maintenance & Repairs Expense Labor Expense Reasearch & Development Expense Selling, General & Administrative Expense 11,000 14,000 15,000 10,000 $ $ 12,000 15,000 5,000 Depreciation - Buildings Mortgage Interest Expense Extra Bank Loan Interest Expense $ $ $ 7,000 Profit Before Taxes Income Tax Expense Net Income $ $ $ 33,000 7,000 26,000 5,000 $ 4,000 $ 3,000 BALANCE SHEET Assets Minimum Cash Extra Cash Accounts Receivable (Auto Parts sales only) Inventory $ $ $ $ 10,000 30,000 $ Land Buildings Less: Accumulated Depreciation 60,000 150,000 $ 20,000 62,000 $ 180,000 $ 60,000 100,000 $ $ Total Assets $ 235,000 Liabilities and Equity Accounts Payable (Auto Parts Cost of Goods Sold) Income Tax Payable $ $ 3,000 7,000 Mortgage Loan Extra Bank Loan isu 190,000 3,000 $ 190,000 Common Stock Retained Earnings (net of dividends, if any) $ $ 20,000 $ 15,000 $ 235,000 Total Liabilities and Equity DFN End Balance $ 0.00% 0.00% monthly Beg Balance $ $ $ $ S 1 Annual Rate Rate per period Years Periods PV FV 12 months $ $ Payment $ S $ $ $ $ $ $ $ Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec TOTALS $ Principal $ $ $ $ $ S $ $ $ $ $ s $ $ Interest $ $ $ $ $ $ $ S $ $ $ $ $ $ $ S $ $ $ $ 50.00 Monthly Payment $0.00 $ $ $ $ $ $ $ $ $ $ $ $ $ Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec TOTALS $ $ $ $ $ $ $ S S S $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ S $ $ $ $ $ $ $ $ $ $ S $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ S Jan Feb Mar Apr May Jun $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ S S $ $ $ $ $ $ $ $ $ $ $ $ S $ $ S S $ $ $ $ S S $ $ $ $ $ $ $ $ $ $ $ S $ $ $ $ $ $ $ $ $ $ S Aug Sep Oct Nov Dec TOTALS , - , , 2 For the calendar year 2018 (in $ millions) 3 4 This exam requires you to forecast the Domestic Auto Parts statements, based on your determination of changes in the ratios. 5 STEP 1: Compute all the ratios for the historical year 2018, in the ligh blue area of the spreadsheet. 6 STEP 2: Forecast all the ratlos in the dark blue area for the next two forecasted years, based on facts from the text of the case. You only need to forecast changes for those ratios you will write about others may be kept at their 2018 value. 7 STEP 3: Complete the forecast of the Income Statement and Balance sheet in the ligh blue area, balancing the Balance Sheet using either the Extra Bank Loan or Extra Cash accounts. B STEP 4: In the green box arca, give a fact from the text of the case used in forecasting the ratio on that row, and explain how that fact relates to the changed ratio value. Write about three different ratias anly, and choose only ratios with a green bax on their row. 9 Remember: Writing about more than 3 will penalize your grade, because the 3 worst written responses will be used. Do not write about more than 3. 10 71 Added Assumptions 12 DAP also performs consulting services, not mentioned in the case, with the units sold per year and the average price per unit shown below. 13 Income Taxes Payable equals 100% of the Income Tax Expense for that year. 14 The Mortgage Loan starts January 1, 2019, with a starting balance of $50,000 (in $ millions). It is a 15 year loan, with an 8% interest rate and monthly payments. The Mortgage Loan Table provided is NOT set up for that loan, you will need to set it up for that loan. 15 There are several numbers in the forecast (Selling, General & Administrative Expense, Minimum Cash, Land, Buildings, Common Stock) that are bolded numbers, and should not be changed. 16 77 Historical Farecast Forecast 2018 2019 2020 19 FORECASTING RATIOS 20 Auto Parts solc per year (millions of units) 21 Carsulting Units sold per year (millions of units) 22 23 Average Price of Auto Parts sold (per unit) $ 5,200.00 24 Average Price of Consulting Units sold (per unit) $ Facts from the text of the case used in forecasting the ratio write about three different ratias, and only ratios with a green 25 26 Auto Parts Cast of Goods Sold, as percent of Auto Parts Sales 27 Maintenance & Repairs Expense, as percent of Auto Parts sales 28 Labar Expense, as percent of combined sales 29 Reasearch & Development Expense, as percent of combined sales 265.00 81 Days of Receivables (Auto Parts sales only! 32 Days of inwentary 23 Days of Accounts Payable (Auto Parts Cost of Goods Sold 34 45 Average Depreciation Years - Buildings 38 Average Tax Rate 7 Average Interest Rate - Extra Bank Loan 38 29 INCOME STATEMENT H0 Auto Parts Sales Revenue 41 Consulting Sales Revenue 12 $ $ 155,000 5,000 Auto Parts Cost of Goods Sold $ 65,000 $ $ Maintenance & Repairs Expense Labor Expense Reasearch & Development Expense Selling, General & Administrative Expense 11,000 14,000 15,000 10,000 $ $ 12,000 15,000 5,000 Depreciation - Buildings Mortgage Interest Expense Extra Bank Loan Interest Expense $ $ $ 7,000 Profit Before Taxes Income Tax Expense Net Income $ $ $ 33,000 7,000 26,000 5,000 $ 4,000 $ 3,000 BALANCE SHEET Assets Minimum Cash Extra Cash Accounts Receivable (Auto Parts sales only) Inventory $ $ $ $ 10,000 30,000 $ Land Buildings Less: Accumulated Depreciation 60,000 150,000 $ 20,000 62,000 $ 180,000 $ 60,000 100,000 $ $ Total Assets $ 235,000 Liabilities and Equity Accounts Payable (Auto Parts Cost of Goods Sold) Income Tax Payable $ $ 3,000 7,000 Mortgage Loan Extra Bank Loan isu 190,000 3,000 $ 190,000 Common Stock Retained Earnings (net of dividends, if any) $ $ 20,000 $ 15,000 $ 235,000 Total Liabilities and Equity DFN End Balance $ 0.00% 0.00% monthly Beg Balance $ $ $ $ S 1 Annual Rate Rate per period Years Periods PV FV 12 months $ $ Payment $ S $ $ $ $ $ $ $ Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec TOTALS $ Principal $ $ $ $ $ S $ $ $ $ $ s $ $ Interest $ $ $ $ $ $ $ S $ $ $ $ $ $ $ S $ $ $ $ 50.00 Monthly Payment $0.00 $ $ $ $ $ $ $ $ $ $ $ $ $ Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec TOTALS $ $ $ $ $ $ $ S S S $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ S $ $ $ $ $ $ $ $ $ $ S $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ S Jan Feb Mar Apr May Jun $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ S S $ $ $ $ $ $ $ $ $ $ $ $ S $ $ S S $ $ $ $ S S $ $ $ $ $ $ $ $ $ $ $ S $ $ $ $ $ $ $ $ $ $ S Aug Sep Oct Nov Dec TOTALS