(The following information applies to the questions displayed below.) Williams Company's accounting department has finished preparing the master budget for this year. The chief financial officer (CFO) would like your assistance in creating data visualizations that she can use to better explain the master budget to the company's senior management team. You decide to break down your assignment into two parts. First, you will review the master budget to ensure that you understand all of its schedules and their interrelationships. Second, you will prepare the data visualizations that have been requested by the CFO. Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a a brief tutorial on Charts in Excel. 8. The CFO would also like you to prepare some data visualizations that depict monthly trends in the cash balance, current assets, and net income. Accordingly, use Charts to do the following: a. Go to the "Cash Budget" tab and create a bar chart that includes one bar for each of 12 months. Each month's bar will show the excess (deficiency) of cash available over disbursements for that month and (where appropriate) the borrowings for that month. Use different colors to distinguish the excess (deficiency) of cash available over disbursements from any borrowings. Also, insert a horizontal line within your chart to depict the company's minimum cash balance of $30,000. b. Go to the Budgeted Balance Sheets" tab and create bar chart that depicts each month's ending total current assets. Each bar within the chart will sub-divide into three parts-the portion of the overall balance that resides in cash, accounts receivable, and inventory. c. Go to the Cash Flow vs. Net Income" tab and create a line chart that includes a total of two lines. The first line will depict monthly trends in operating cash flows (cash collections from customers minus total cash disbursements, including interest payments) and the second line will depict net income. d. What insights are revealed by the charts in requirements 8a, 8b, and 8c? Williams Company Cash Budget For This Year January February 68,000 $ 64,998 S $ March 51,033 $ April 30,000 $ May 30,000 $ June 30,000 $ July 30,000 $ August September 60,907 S 112,860 S October November December Total 180,526 S 206,934 $ 214,471 $ 68,000 $ 96.200 164.200 77.400 142.398 68.400 119.433 91.8.00 121.8.00 104.400 134.400 145.800 175.8.00 234.000 264.000 273.6.00 334.507 26.9.200 381.060 180.000 360.526 142.200 349.134 127.9.00 342.271 1.809.800 1.877.800 Beginning cash balance Add cash receipts: Collections from customers Total cash available Less cash disbursements: Merchandise purchases Selling and administrative Total cash disbursements Excess (deficiency) of cash available over disbursements Financing: Borrowings (at the beginnings of months) Repayments (at end of the year) Interest (at 1% per month) Total financing Ending cash balance 46.002 53.200 99.202 64.999 39,765 51.6.00 91.365 51.033 40,219 54.000 94.219 25.214 51,851 54.8.00 106.651 15.149 61,875 57.200 119,075 15.325 89,966 66.000 155.966 19.834 133,093 70,000 203.093 60,907 150,047 71.6.00 221.647 112.860 137,734 62.800 200.534 180.526 94,793 58.800 153.593 206,934 76,663 58.000 134.663 214.471 66,846 54.8.00 121.646 220.625 988,853 712.800 1.701.653 176.147 4,786 14.851 14,675 10,166 - - - . - . . 44,478 (44,478) (44,478) (3.701 (3.701) (48,179) 13.701) 172,446 $ 172.446 14.851 30.000 $ 4.786 30,000 $ 10.166 30.000 $ 64.998s 14.675 30.000 S $ 51,033 s 60.907 S 112,860 S 180.526 s 206.934 S 214.471 s Mininmum Cash Requirement $ 30,000 $ 30,000 $ 30,000 $ 30,000 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 Williams Company Budgeted Balance Sheets For Each Month This Year January February March April May June July August September October November December $ Assets Current assets: Cash Accounts receivable Merchandise inventory Total current assets Plant and equipment: Buildings and equipment Accumulated depreciation Plant and equipment, net Total assets 64,998 $ 64.800 8.663 138,461 51,033 $ 50,400 12.375 113,808 30,000 $ $ 72,000 13.613 115,613 30,000 $ 79,200 17.325 126,525 30,000 $ 100,800 30.938 161,738 30,000 $ 180,000 37.125 247,125 60.907 $ 216,000 39.600 316,507 112.860 $ 230,400 25.988 369.248 180.526 $ 151,200 19.800 351,526 206,934 $ 115,200 18.563 340,696 214.471 $ 108,000 13.613 336,083 172,446 79,200 12.100 263,746 900.000 (300.000) 600.000 738.461 $ 900.000 130.8.000 592.000 705.808 $ 900.000 (316.000) 584.000 699.613 $ 900.000 (324.000) 576.000 702.525 $ 900.000 (332.000) 568.000 729.738 $ 900.000 (340.000) 560.000 807.125 $ 900.000 1348.000) 552.000 868.507 $ 900.000 (356.000) 544.000 913.248 $ 900.000 (364.000) 536.000 887.526 $ 900.000 (372.000) 528.000 868.696 $ 900.000 (3.80.000) 520.000 856.083 $ 900,000 (388.000) 512.000 775.746 $ $ 34,011 $ 32.608 $ 44,997 Liabilities and Stockholders' Equity Current liabilities: Accounts payable Short-term note payable Interest payable Total liabilities Stockholders' equity Common stock Retained earnings Total stockholders' equity Total liabilities and stockholders' equity 43,127 $ 4,786 48 47,960 49.438 $ 19,637 244 69,319 70.476 $ 34,312 587 105,375 110.447 $ 44.478 1.032 155,957 128.329 $ 44,478 1.477 174.284 123,069 $ 44.478 1.922 169,469 83,098 $ 44,478 2.366 129,943 66.268 $ 44.478 2.811 113.558 58.905 $ 44.478 3.256 106,639 34,011 32,608 44,997 253,000 451.450 704,450 $ 738.461 $ 253,000 420.200 673.200 $ 705.808 $ 253,000 398.652 651.652 $ 699.613 $ 253,000 380.206 633.206 $ 702.525 $ 253,000 371.363 624.363 $ 729.738 $ 253,000 398.168 651.168 $ 807.125 $ 253,000 441.223 694.223 $ 868.507 $ 253,000 490.778 743.778 $ 913.248 $ 253,000 504,584 757.584 $ 887.526 $ 253,000 502.139 755.139 $ 868.696 $ 253,000 496.444 749.444 $ 856.083 $ 253,000 477.749 730.749 775.746 $ $ $ Williams Company Cash Flow vs. Net Income For Each Month This Year Operating cash flows Net income $ $ January (3,002) $ (24,750) $ February (13,965) $ (31,250) $ March (25,819) $ (21,548) $ April (14,851) $ (18,446) $ May (14,675) $ (8,843) $ June (10,166) $ 26,805 $ July 30.907 $ 43.055 $ August September 51.953 $ 67.666 $ 49,555 $ 13,805 $ October November December Total 26.408 $ $ 7.537 $ 2.454 $ 104.446 (2,445) $ (5,695) $ (18,695) $ 1,549 (The following information applies to the questions displayed below.) Williams Company's accounting department has finished preparing the master budget for this year. The chief financial officer (CFO) would like your assistance in creating data visualizations that she can use to better explain the master budget to the company's senior management team. You decide to break down your assignment into two parts. First, you will review the master budget to ensure that you understand all of its schedules and their interrelationships. Second, you will prepare the data visualizations that have been requested by the CFO. Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a a brief tutorial on Charts in Excel. 8. The CFO would also like you to prepare some data visualizations that depict monthly trends in the cash balance, current assets, and net income. Accordingly, use Charts to do the following: a. Go to the "Cash Budget" tab and create a bar chart that includes one bar for each of 12 months. Each month's bar will show the excess (deficiency) of cash available over disbursements for that month and (where appropriate) the borrowings for that month. Use different colors to distinguish the excess (deficiency) of cash available over disbursements from any borrowings. Also, insert a horizontal line within your chart to depict the company's minimum cash balance of $30,000. b. Go to the Budgeted Balance Sheets" tab and create bar chart that depicts each month's ending total current assets. Each bar within the chart will sub-divide into three parts-the portion of the overall balance that resides in cash, accounts receivable, and inventory. c. Go to the Cash Flow vs. Net Income" tab and create a line chart that includes a total of two lines. The first line will depict monthly trends in operating cash flows (cash collections from customers minus total cash disbursements, including interest payments) and the second line will depict net income. d. What insights are revealed by the charts in requirements 8a, 8b, and 8c? Williams Company Cash Budget For This Year January February 68,000 $ 64,998 S $ March 51,033 $ April 30,000 $ May 30,000 $ June 30,000 $ July 30,000 $ August September 60,907 S 112,860 S October November December Total 180,526 S 206,934 $ 214,471 $ 68,000 $ 96.200 164.200 77.400 142.398 68.400 119.433 91.8.00 121.8.00 104.400 134.400 145.800 175.8.00 234.000 264.000 273.6.00 334.507 26.9.200 381.060 180.000 360.526 142.200 349.134 127.9.00 342.271 1.809.800 1.877.800 Beginning cash balance Add cash receipts: Collections from customers Total cash available Less cash disbursements: Merchandise purchases Selling and administrative Total cash disbursements Excess (deficiency) of cash available over disbursements Financing: Borrowings (at the beginnings of months) Repayments (at end of the year) Interest (at 1% per month) Total financing Ending cash balance 46.002 53.200 99.202 64.999 39,765 51.6.00 91.365 51.033 40,219 54.000 94.219 25.214 51,851 54.8.00 106.651 15.149 61,875 57.200 119,075 15.325 89,966 66.000 155.966 19.834 133,093 70,000 203.093 60,907 150,047 71.6.00 221.647 112.860 137,734 62.800 200.534 180.526 94,793 58.800 153.593 206,934 76,663 58.000 134.663 214.471 66,846 54.8.00 121.646 220.625 988,853 712.800 1.701.653 176.147 4,786 14.851 14,675 10,166 - - - . - . . 44,478 (44,478) (44,478) (3.701 (3.701) (48,179) 13.701) 172,446 $ 172.446 14.851 30.000 $ 4.786 30,000 $ 10.166 30.000 $ 64.998s 14.675 30.000 S $ 51,033 s 60.907 S 112,860 S 180.526 s 206.934 S 214.471 s Mininmum Cash Requirement $ 30,000 $ 30,000 $ 30,000 $ 30,000 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 Williams Company Budgeted Balance Sheets For Each Month This Year January February March April May June July August September October November December $ Assets Current assets: Cash Accounts receivable Merchandise inventory Total current assets Plant and equipment: Buildings and equipment Accumulated depreciation Plant and equipment, net Total assets 64,998 $ 64.800 8.663 138,461 51,033 $ 50,400 12.375 113,808 30,000 $ $ 72,000 13.613 115,613 30,000 $ 79,200 17.325 126,525 30,000 $ 100,800 30.938 161,738 30,000 $ 180,000 37.125 247,125 60.907 $ 216,000 39.600 316,507 112.860 $ 230,400 25.988 369.248 180.526 $ 151,200 19.800 351,526 206,934 $ 115,200 18.563 340,696 214.471 $ 108,000 13.613 336,083 172,446 79,200 12.100 263,746 900.000 (300.000) 600.000 738.461 $ 900.000 130.8.000 592.000 705.808 $ 900.000 (316.000) 584.000 699.613 $ 900.000 (324.000) 576.000 702.525 $ 900.000 (332.000) 568.000 729.738 $ 900.000 (340.000) 560.000 807.125 $ 900.000 1348.000) 552.000 868.507 $ 900.000 (356.000) 544.000 913.248 $ 900.000 (364.000) 536.000 887.526 $ 900.000 (372.000) 528.000 868.696 $ 900.000 (3.80.000) 520.000 856.083 $ 900,000 (388.000) 512.000 775.746 $ $ 34,011 $ 32.608 $ 44,997 Liabilities and Stockholders' Equity Current liabilities: Accounts payable Short-term note payable Interest payable Total liabilities Stockholders' equity Common stock Retained earnings Total stockholders' equity Total liabilities and stockholders' equity 43,127 $ 4,786 48 47,960 49.438 $ 19,637 244 69,319 70.476 $ 34,312 587 105,375 110.447 $ 44.478 1.032 155,957 128.329 $ 44,478 1.477 174.284 123,069 $ 44.478 1.922 169,469 83,098 $ 44,478 2.366 129,943 66.268 $ 44.478 2.811 113.558 58.905 $ 44.478 3.256 106,639 34,011 32,608 44,997 253,000 451.450 704,450 $ 738.461 $ 253,000 420.200 673.200 $ 705.808 $ 253,000 398.652 651.652 $ 699.613 $ 253,000 380.206 633.206 $ 702.525 $ 253,000 371.363 624.363 $ 729.738 $ 253,000 398.168 651.168 $ 807.125 $ 253,000 441.223 694.223 $ 868.507 $ 253,000 490.778 743.778 $ 913.248 $ 253,000 504,584 757.584 $ 887.526 $ 253,000 502.139 755.139 $ 868.696 $ 253,000 496.444 749.444 $ 856.083 $ 253,000 477.749 730.749 775.746 $ $ $ Williams Company Cash Flow vs. Net Income For Each Month This Year Operating cash flows Net income $ $ January (3,002) $ (24,750) $ February (13,965) $ (31,250) $ March (25,819) $ (21,548) $ April (14,851) $ (18,446) $ May (14,675) $ (8,843) $ June (10,166) $ 26,805 $ July 30.907 $ 43.055 $ August September 51.953 $ 67.666 $ 49,555 $ 13,805 $ October November December Total 26.408 $ $ 7.537 $ 2.454 $ 104.446 (2,445) $ (5,695) $ (18,695) $ 1,549