The OHP A DELOW! Square Corp. Square Corp. lecon Statement Balance Sheet For the Year Ended Dec 31, 2017 (5 in 600's) As of Dec. 21. 2017 (5 in o's) 2017 2016 Axts 2017 2016 Sales 7.250.000 0.750,000 Cash 149.970 100,000 Coul of Cold 5.400,000 5,330,000||Accounts Receivable 370,1x00 347.000 Grass 7 Inventory 870,000 $15.000 Selling and GEA Expe%65,000 $32,000 Total Carrer de > Depreciation 550,000 Plant quipment 6.5 TODO 5.010.000 EBIT 335.000 Accumulated Depreciate 1.930,000 1,380,000 Interestixpense 110,000 Net Med Ass Earnings Before Taver 205.000 Totales Taxes Den and we'Equity Net Inc 133.250 79.100 counts Payable 420,000 121.440 Nets Payable 166,625 Note: Nul Curt Liabilir Tax Rule Lone bi 1.130,000918400 Shares tanding 75.000 65. Litex 2 Lamins per Share 9 Comm Stock 2.520,000 2,043,440 Deidende per Share Additional Pain Capital 772.000 551.00 Addition to por Share Retained ning 734,720 734,720 Dividend your Ratio 50% Survholder's Eyly Total Lial and Charity a) Recreate the income statement and balance sheet by filling in the question marks with formulas. Each statement should be on a separate worksheet. Try to duplicate the formatting exactly. Note that in 2016, 60% of earnings were paid to shareholders as dividends. b) On another worksheet, create a statement of cash flows for 2017. Use formulas linked directly to the source on previous worksheets instead of numbers. e) Create is common-size income statement and balance sheet for 2017 and 2016. These statements should be created on a separate worksheet with all formulas linked directly to the income statement and balance sheet Q2) The Right Target, Inc., a marketing research and consulting firm, is working on a cash budget for July to December 2017. The staff has projected the following cash collections and payments: Month Collections Payments Cash Sale Credit Sale Purch Wages Miscellaneous Expenses Daly SID, $20,000 $15.000 5600 $1.500 AN $12.000 $24.000 $15.000 57.200 $2.000 September $14.000 $28,000 $21,000 5,000 $2.500 October $16.000 532.000 $24.000 59,600 $3.000 November 20.000 540,000 530.000 $12,000 54.000 December 525,000 550.000 517.500 15.000 $4,500 a) If the ending cash balance as of June 30, 2017 was $10,000, determine the firm's forecasted monthly cash balance b) The staff at The Right Tarpet, Inc. wants to know how much they would need to borrow each month if the minimum ending cash balance is $30,000 and the annual interest rate is 7% c) Determine the impact on the ending cash balance if the firm uses any cash surplus above the required minimum cash balance to pay off its short-term borrowing monthly The OHP A DELOW! Square Corp. Square Corp. lecon Statement Balance Sheet For the Year Ended Dec 31, 2017 (5 in 600's) As of Dec. 21. 2017 (5 in o's) 2017 2016 Axts 2017 2016 Sales 7.250.000 0.750,000 Cash 149.970 100,000 Coul of Cold 5.400,000 5,330,000||Accounts Receivable 370,1x00 347.000 Grass 7 Inventory 870,000 $15.000 Selling and GEA Expe%65,000 $32,000 Total Carrer de > Depreciation 550,000 Plant quipment 6.5 TODO 5.010.000 EBIT 335.000 Accumulated Depreciate 1.930,000 1,380,000 Interestixpense 110,000 Net Med Ass Earnings Before Taver 205.000 Totales Taxes Den and we'Equity Net Inc 133.250 79.100 counts Payable 420,000 121.440 Nets Payable 166,625 Note: Nul Curt Liabilir Tax Rule Lone bi 1.130,000918400 Shares tanding 75.000 65. Litex 2 Lamins per Share 9 Comm Stock 2.520,000 2,043,440 Deidende per Share Additional Pain Capital 772.000 551.00 Addition to por Share Retained ning 734,720 734,720 Dividend your Ratio 50% Survholder's Eyly Total Lial and Charity a) Recreate the income statement and balance sheet by filling in the question marks with formulas. Each statement should be on a separate worksheet. Try to duplicate the formatting exactly. Note that in 2016, 60% of earnings were paid to shareholders as dividends. b) On another worksheet, create a statement of cash flows for 2017. Use formulas linked directly to the source on previous worksheets instead of numbers. e) Create is common-size income statement and balance sheet for 2017 and 2016. These statements should be created on a separate worksheet with all formulas linked directly to the income statement and balance sheet Q2) The Right Target, Inc., a marketing research and consulting firm, is working on a cash budget for July to December 2017. The staff has projected the following cash collections and payments: Month Collections Payments Cash Sale Credit Sale Purch Wages Miscellaneous Expenses Daly SID, $20,000 $15.000 5600 $1.500 AN $12.000 $24.000 $15.000 57.200 $2.000 September $14.000 $28,000 $21,000 5,000 $2.500 October $16.000 532.000 $24.000 59,600 $3.000 November 20.000 540,000 530.000 $12,000 54.000 December 525,000 550.000 517.500 15.000 $4,500 a) If the ending cash balance as of June 30, 2017 was $10,000, determine the firm's forecasted monthly cash balance b) The staff at The Right Tarpet, Inc. wants to know how much they would need to borrow each month if the minimum ending cash balance is $30,000 and the annual interest rate is 7% c) Determine the impact on the ending cash balance if the firm uses any cash surplus above the required minimum cash balance to pay off its short-term borrowing monthly