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. Which of the following statements are true based on the bar chart that you created in requirement 8a? The company's cash collections in February are greater than its cash disbursements during that same month. The company's excess (deficiency) of cash available over disbursements peaks in December. 7 The company's excess (deficiency) of cash available over disbursements hits its lowest point in March. The company needs to borrow money during the year including borrowings in May and June. c. 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 parts-the portion of the overall balance that resides in cash, accounts receivable, and inventory. d. Which of the following statements are true based on the bar chart that you created in requirement 8c? The total current assets are highest in June and July. 7 Accounts receivable comprise the largest portion of August's current assets. c. 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 parts-the portion of the overall balance that resides in cash, accounts receivable, and inventory. d. Which of the following statements are true based on the bar chart that you created in requirement 8c? The total current assets are highest in June and July. ? Accounts receivable comprise the largest portion of August's current assets. The cash balance peaks in November. Inventory comprises the largest portion of the total current assets in June. e. 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. f. Which of the following statements are true based on the line chart that you created in requirement 8e? 2 The net income and operating cash flows peak in August. The net income and operating cash flows peak in September. The net income lags operating cash flows. The operating cash flows lag net income. For This Year January eginning cash balance Collection ham cum sch debursements ,000 $ February GLO March Ma June My Aupar 3000 S 30.000$ 30000 30000$ September 1120 Ochter November D 180526 $ 2015041 214471 200 77400 8400 100 104400 145400 164.200 17840 121800 134400 17500 234.000 254.000 273800 2200 180000 142200 34034 30.27 Marchandise purch 39.766 40219 SUBST e1875 137.7M 4750 78063 Selling and admin 53.200 $1.400 $4000 54800 57300 6000 70000 62300 5000 54300 Teh disbursement 91.300 M4215 106401 11907 1559 Exes (deficiency) of cash available over dishurteners 64300 $1000 25214 15.325 221647 1120 153.593 2004 134 STA 214.471 2002 Franking Borrowings (at the beginings of month) M 14475 106 Repayments (and of the year) (MAN) Tring Ending cash beance Mine Cash 476 14861 14675 $ $103 30.000 30000 30.000 30.000 07 1120 214471 1724 30.000 30.000 30000 30.0001 30.000 30.000 30,000 3000 3000 Chart Title Horizontal (Category) Axis Willams Company Budgeted Income Statements For This Year (Absorption costing bass) January February March April Ma June My Avga September Ochter November December Cost of goods sold B1000 F 44550 50.000 126.000 2800 270000 28.000 F 144000 15000 $2000 34550 4500 54450 00300 121750 146.500 158400 103.950 79,200 74,250 54.450 Criss margin 450 28.350 40.500 44550 56,700 101,200 121,000 5050 6430 60.750 44.550 Selling and administrative expenses 61,200 50.000 Net operating income (24750) (31,250) 2,000 (21,500) 2800 05,200 74.000 78.000 7600 70,000 600 6.000 2800 (1250) (8500) 27.250 43,500 50,000 14250 2,000) (250) (1250) terest expens 4 Net Income (24.750 (31,25 (21.548) 196 (18,440 343 (843) 445 445 445 445 445 445 21806 41095 11805 (2445) 15060 (1800) Chart Title Budgeted Balance Sheets For Each Month This Year January February March Apr May Je Judy Augal September October November December A Carent Cast $ Accounts receive Marchandse invertory 64,800 8403 $1000 $ 50400 30,000 $ 72,000 30,000 $ 79200 30.0005 10000 30.000 5 180,000 112.00 $ 21000 230,400 1805205 151200 32375 13013 3008 37125 30400 25,000 15.800 200904 1 115200 18.563 2144715 108.000 STEAM 79.200 13013 12100 Tit current as 131401 113 115413 136525 161738 247,125 356507 309248 351.326 340.000 3300 2374 Part and equipmat Buildings and equa Accund d hat and egment, 900,000 900000 100.000 500.000 (300.000) DOR000 0000 800000 92,000 4000 (304000 SPRAID 00000 (3000) 900000 900.000 900.000 100000 500,000 900,000 900000 340000 (48.000 4000) (2000) 30000) 50000 552.000 544000 5.000 528000 $30,000 T $ 70005 797 3 807.125 MA507 $ 13248 $12,000 776746 Lab and Sock Current abes Accounts payable ' 3011 SLAM S 43127 $ 470 3030 11047 $ PLAY 128329 123009 64479 30 $ 28 S LATE 5905 $ MATE 44967 1922 34011 3240 47900 319 10535 155 957 1743M 12993 113M 106630 44967 253,000 253000 253 000 251.000 253.000 253.000 253.000 251.000 253,000 253000 451450 Total stackers $ 2044501 0300 671300 $ 371.303 441223 Tours and $ 70808 1 631200 700 105 1 OLX) 1 85198 2) 1 Mam 1 1. 9132485 S045 757 5 M71 253.000 477749 756330 74444 5 856083 ' 775740 Chart Title Je MY Amer A 26 9058 420558 495553 For Each Marth The Year Mer DARTE HIS Mach 0002 ARTES 04750 0125001 111111-11 September 780 139059 October 2400 244 November 7,537 $ Decender 2454 Total 4 1.349