CREATE Case Problem 2 Data File needed for this Case Problem: Balance.xlsx Scott Kahne Tool & Die Cheryl Hippe is a financial officer at Scott Kahne Tool & Die, a manufacturing company located in Mankato, Minnesota. Every month the company publishes a balance sheet, a report that details the company's assets and liabilities. Cheryl asked you to create the workbook with the text and formulas for this report. Complete the following: 1. Open the Balance workbook located in the Excell > Case2 folder included with your Data Files. Save the workbook as Balance Sheet in the location specified by your instructor. 2. In the Documentation sheet, enter your name in cell B3 and the date in cell B4. 3. Go to the Balance Sheet worksheet. Set the font size of the title in cell A1 to 28 points. 4. In cell A2, enter the text Statement for March 2017. 5. Set the width of columns A and E to 30 characters. Set the width of columns B, C, F, and G to 12 characters. Set the width of column D to 4 characters. (Hint: Hold down the Ctrl key as you click the column headings to select both adjacent and nonadjacent columns.) 6. Set the font size of the text in cells A4, C4, E4, and G4 to 18 points. 7. Set the font size of the text in cells A5, E5, A11, E11, A14, E15, A19, E20, and A24 to 14 points, 8. Enter the values shown in Figure 1-45 in the specified cells. Module 1 Getting Started with Excel Excel EX 61 Figure 1-45 A ssets and liabilities Cell Cell B12 Cell B15 B16 B17 Cell Current Assets Cash Accounts Receivable Inventories Prepaid Insurance Long-Term Investments Available Securities Tangible Assets Land Building and Equipment Less Accumulated Depreciation Intangible Assets Goodwill Other Assets Current Liabilities Accounts Payable Salaries Interest Notes Payable Long-Term Liabilities Long-Term Notes Payable Mortgage Stockholders' Equity Capital Stock Retained Earnings Comprehensive Income/Loss B20 B22 Cell Value $123,000 $75,000 $58,000 $15,000 Value $29,000 Value $49,000 $188,000 -$48,000 Value $148,000 $14,000 Value $62,000 $14,000 $12,000 $38,000 Value $151,000 $103,000 Value $178,000 $98,000 $5,000 F6 F7 F8 Cell F12 F13 Cell F16 F17 F18 9. In cell C9, enter a formula to calculate the sum of the Current Assets in the range B6:39. 10. In cell C12, enter a formula to display the value of B12. 11. In cell C17, enter a formula to calculate the sum of the Tangible Assets in the range B15:B17. 12. In cells C20 and C22, enter formulas to display the values of cells B20 and B22, respectively. 13. In cell C24, enter a formula to calculate the total assets in the balance sheet by adding cells C9, C12, C17, C20, and C22. Set the font size of the cell to 14 points. 14. In cell G9, enter a formula to calculate the sum of the Current Liabilities in the range F6:F9. 15. In cell G13, enter a formula to calculate the sum of the Long-Term Liabilities in the range F12:F13. 16. In cell G18, enter a formula to calculate the sum of the Stockholders' Equity in the range F16:F18. 17. In cell G20, calculate the Total Liabilities and Equity for the company by adding the values of cells G9, G13, and G18. Set the font size of the cell to 14 points. 18. Check your calculations. In a balance sheet the total assets (cell C24) should equal the total liabilities and equity (cell G20). 19. Set the page layout orientation to landscape and the Balance Sheet worksheet to print to one page for both the width and height. 20. Preview the worksheet on the Print screen in Backstage view, and then save and close the workbook C D E F G H I A B 1 Scott Kahne Tool & Die Total 4 Assets Total 5 Current Assets 6 Cash 7 Accounts Receivable 8 Inventories 9 Prepaid Insurance Liabilities Current Liabilities Accounts Payable Salaries Interest Notes Payable 10 11 Long-term Investments 12 Available Securities Long-term Liabilities Long-term Notes Payable Mortgage 13 14 Tangible Assets 15 Land 16 Building and Equipment 17 Less Accumulated Depreciation 18 19 Intangible Assets 20 Goodwill Stockholders' Equity Capital Stock Retained Earnings Comprehensive Income/Loss Total Liabilities and Equity 22 Other Assets 24 Total Assets CREATE Case Problem 2 Data File needed for this Case Problem: Balance.xlsx Scott Kahne Tool & Die Cheryl Hippe is a financial officer at Scott Kahne Tool & Die, a manufacturing company located in Mankato, Minnesota. Every month the company publishes a balance sheet, a report that details the company's assets and liabilities. Cheryl asked you to create the workbook with the text and formulas for this report. Complete the following: 1. Open the Balance workbook located in the Excell > Case2 folder included with your Data Files. Save the workbook as Balance Sheet in the location specified by your instructor. 2. In the Documentation sheet, enter your name in cell B3 and the date in cell B4. 3. Go to the Balance Sheet worksheet. Set the font size of the title in cell A1 to 28 points. 4. In cell A2, enter the text Statement for March 2017. 5. Set the width of columns A and E to 30 characters. Set the width of columns B, C, F, and G to 12 characters. Set the width of column D to 4 characters. (Hint: Hold down the Ctrl key as you click the column headings to select both adjacent and nonadjacent columns.) 6. Set the font size of the text in cells A4, C4, E4, and G4 to 18 points. 7. Set the font size of the text in cells A5, E5, A11, E11, A14, E15, A19, E20, and A24 to 14 points, 8. Enter the values shown in Figure 1-45 in the specified cells. Module 1 Getting Started with Excel Excel EX 61 Figure 1-45 A ssets and liabilities Cell Cell B12 Cell B15 B16 B17 Cell Current Assets Cash Accounts Receivable Inventories Prepaid Insurance Long-Term Investments Available Securities Tangible Assets Land Building and Equipment Less Accumulated Depreciation Intangible Assets Goodwill Other Assets Current Liabilities Accounts Payable Salaries Interest Notes Payable Long-Term Liabilities Long-Term Notes Payable Mortgage Stockholders' Equity Capital Stock Retained Earnings Comprehensive Income/Loss B20 B22 Cell Value $123,000 $75,000 $58,000 $15,000 Value $29,000 Value $49,000 $188,000 -$48,000 Value $148,000 $14,000 Value $62,000 $14,000 $12,000 $38,000 Value $151,000 $103,000 Value $178,000 $98,000 $5,000 F6 F7 F8 Cell F12 F13 Cell F16 F17 F18 9. In cell C9, enter a formula to calculate the sum of the Current Assets in the range B6:39. 10. In cell C12, enter a formula to display the value of B12. 11. In cell C17, enter a formula to calculate the sum of the Tangible Assets in the range B15:B17. 12. In cells C20 and C22, enter formulas to display the values of cells B20 and B22, respectively. 13. In cell C24, enter a formula to calculate the total assets in the balance sheet by adding cells C9, C12, C17, C20, and C22. Set the font size of the cell to 14 points. 14. In cell G9, enter a formula to calculate the sum of the Current Liabilities in the range F6:F9. 15. In cell G13, enter a formula to calculate the sum of the Long-Term Liabilities in the range F12:F13. 16. In cell G18, enter a formula to calculate the sum of the Stockholders' Equity in the range F16:F18. 17. In cell G20, calculate the Total Liabilities and Equity for the company by adding the values of cells G9, G13, and G18. Set the font size of the cell to 14 points. 18. Check your calculations. In a balance sheet the total assets (cell C24) should equal the total liabilities and equity (cell G20). 19. Set the page layout orientation to landscape and the Balance Sheet worksheet to print to one page for both the width and height. 20. Preview the worksheet on the Print screen in Backstage view, and then save and close the workbook C D E F G H I A B 1 Scott Kahne Tool & Die Total 4 Assets Total 5 Current Assets 6 Cash 7 Accounts Receivable 8 Inventories 9 Prepaid Insurance Liabilities Current Liabilities Accounts Payable Salaries Interest Notes Payable 10 11 Long-term Investments 12 Available Securities Long-term Liabilities Long-term Notes Payable Mortgage 13 14 Tangible Assets 15 Land 16 Building and Equipment 17 Less Accumulated Depreciation 18 19 Intangible Assets 20 Goodwill Stockholders' Equity Capital Stock Retained Earnings Comprehensive Income/Loss Total Liabilities and Equity 22 Other Assets 24 Total Assets