Required information {The following information applies to the questions displayed below} Williams Company's accounting department has nished preparing the master budget for this year. The chief nancial ofcer {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. 7. The CFO would like you to prepare some data visualizations that depict trends in sales, net income, and cash collections. Accordingly. use Charts to do the following: a. Go to the \"Sales Budget\" tab and create a line chart that provides a monthly sales trend analysis. b. Go to the \"Sales Budget\" tab and create a line chart that includes the monthly sales trend analysis from requirement 7a plus another trend line pertaining to the monthly cash collections from customers. 1:. Go to the \"Budgeted Income Statements" tab and create a line chart that includes the monthly sales trend analysis from requirement 7a plus another trend line pertaining to monthly net income. d. What insights are revealed by the charts in requirements 7a, 7b, and 7c? perspective. it looks like sales leg collections. Since the cash collected during the yearis more than the sales. Specifically. they both follow the same overall trend. For the 6 months collections are ahead and the rest of the year on the graph the sales are now ahead and the trend line shows this. The trend line is pretty much the same shape, seasonal. A 8. The CFC!I 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 {deciency} of cash available over disbursements for that month and [where appropriate] the borrowings for that month. Use different colors to distinguish the excess [deciency] 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 a bar chart that depicts each month's ending total current assets. Each bar within the chart will sub-divide into three partsthe portion of the overall balance that resides in cash. accounts receivable, and inventory. 1:. Go to the \"Cash Flow vs. Net Income\" tab and create a line chart that includes a total oftwo lines. The rst line will depict monthly trends in operating cash ows (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? For This Year January February March April May June July August September October November December Total Beginning cash balance $ 68,000 $ 64,998 $ 51,033 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 60,907 $ 112,860 $ 180,526 $ 206,934 $ 214,471 $ 68,000 Add cash receipts: Collections from customers 96,20 77,400 68,40 91,800 104.40 145,800 234,000 273,600 268,200 180,000 142,200 127,800 1,809,800 Total cash available 164,200 142,398 119,433 121,80 134.400 175,800 264,000 334.507 381,060 360,526 349,134 342,271 1,877,800 Less cash disbursements Merchandise purchase 46,002 39,765 40,219 51,851 61,875 89,966 33,093 150,047 37,734 34,793 76,663 66,846 988,853 Selling and administrative 53,200 1,600 54,000 54,800 57,200 66,00 70,000 71,600 62,800 58,80 58,000 54,800 712,800 Total cash disbursements 99,202 91,365 94,219 106,651 19,075 155,966 03,093 221.647 200,534 153,593 134,663 121,646 1,701,653 Excess (deficiency) of cash available over disbursements 64,998 51,033 25,214 15,149 15,325 9,834 30,907 1 12,860 180,526 206,934 214,471 220,625 176,147 Financing: Borrowings (at the beginnings of months) 4,786 14,851 14,675 10,166 44,478 Repayments (at end of the year) 44,478 44,478) Interest (at 1% per month) (3.701) (3.701 Total financing 4,786 14,851 14,675 10,166 (48, 179) (3,701 Ending cash balance 64,998 51,033 30,000 30,000 30,000 30,000 60,907 112,860 180,526 206,934 214,471 172,446 $ 172,446 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 Chart Title 50,000 100 000 150,060 200 000 250,000 " Borrowings [at the beginnings of mantis) " Financing: " Excess [deficiency) of cash available over disbursementsWilliams 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 S 64,998 $ 51,033 $ 30,000 30,000 $ 30,000 $ 30,000 $ 60,907 $ 112,860 $ 180,526 $ 206,934 $ 214,471 $ 172,446 Accounts receivable 54,800 50,400 72,000 79,200 100,800 180,000 216,000 230,400 151,200 115,200 108,000 79,200 Merchandise inventory 8,663 12,375 13,613 17,325 30.938 37,125 39,600 25,988 19,800 18,563 13,613 12,100 Total current assets 138,461 113,808 115,613 126,525 161,738 247,125 316,507 369,248 351,526 340,696 336,083 263,746 Plant and equipment Buildings and equipment 900,000 900,000 00,000 900,000 900,000 900,000 100,000 900,00 900,000 900,000 900,000 900,000 Accumulated depreciation (300,000) 308,000 (316,000 (324,000) 332,000 340,000) 348,000) 356,000) 364,000) 372,000 (380,000 388,000) Plant and equipment, net 600,000 592,000 584,000 576,000 568,000 560,000 552,000 544,000 536,000 528,000 520,000 512,000 16 Total assets IS 738,461 S 705,808 699,613 S 702,525 S 729,738 S 807.125 S 868,507 S 913,248 S 887.526 | S 868,696 S 856,083 S 775,746 Liabilities and Stockholders' Equity Current liabilities: Accounts payable $ 34,011 $ 32,608 $ 43,127 $ 49,438 $ 70,476 $ 110,447 $ 128,329 $ 123,069 $ 83,098 $ 66,268 58,905 44,997 Short-term note payable 1 786 19.637 4.312 44,478 14,478 44.478 4,478 14.478 44.478 Interest payable 48 244 587 1,032 1,477 1,922 2,366 2,811 3,256 Total liabilities 34,011 32,608 17,960 69,319 05,375 155,957 174,284 69,469 29,943 13,558 06,639 44,997 Stockholders' equity: Common stock 253,00 253,000 253,000 253,000 253,000 253,000 253,000 253,000 253,000 253,000 253,000 253,000 Retained earnings 151,450 420,200 398,652 380,206 71,363 398,168 141,223 490,778 504,584 602,139 496,444 477,749 Total stockholders' equity $ 704,450 673,200 651,652 633,206 624,363 651,168 694,223 S 743,778 757,584 755, 139 749,444 730,749 Total liabilities and stockholders' equity $ 738,461 S 705,808 699,613 S 702,525 729,738 S 807,125 S 913,248 S 887,526 868,696 856,083 775,746 Total assets 2 $100,000 $200,000 $300,000 $400,000 $500,000 $600,000 $700,000 $800,000 $900,000 $1,000,00 0B C D E F G H K L M IN Williams Company Cash Flow vs. Net Income For Each Month This Year January February March April May June July August September October November December Total 6 Operating cash flows (3,002) $ (13,965) $ (25,819) $ (14,851) $ (14,675) $ (10,166) $ 30,907 $ 51,953 $ 67,666 $ 26,408 $ 7,537 $ 2,454 $ 104,446 Net income (24,750) $ (31,250) $ 21,548) $ (18,446) $ (8,843) $ 26,805 $ 43,055 $ 49,555 $ 13,805 $ (2,445) $ (5,695) $ (18,695) $ 1,549 Chart Title $120, 000 15 $100,000 18 $80,000 $60,000 $40,000 $20,000 S- February March April Ma June July August September October November December Total 36 $(20,000) $(40, 000) -Operating cash flows Net income