Required information [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. e. 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. f. Which of the following statements are true based on the line chart that you created in requirement 7e? 2 The sales peak during the winter months and net income peaks during the summer months. ? The sales peak during the summer months and net income peaks during the winter months. ? The sales and net income both peak during the summer months. ? The sales and net income both peak during the winter months. 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. 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 partsthe 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? ? 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. B D E F G H I J K L M N Williams Company Budgeted Income Statements For This Year (Absorption costing basis) $ $ $ Sales Cost of goods sold Gross margin Selling and administrative expenses Net operating Income Interest expense Net Income January 81,000 44,550 36,450 61,200 (24,750) February $ 63,000 $ 34,650 28,350 59,600 (31,250) March 90,000 49,500 40,500 62,000 (21,500) 48 (21,548) April 99,000 $ 54,450 44,550 62,800 (18,250) 196 (18,446) May 126,000 $ 69,300 56,700 65,200 (8,500) 343 (8,843) June 225,000 123,750 101,250 74,000 27,250 445 26,805 July 270,000 $ 148,500 121,500 78,000 43,500 445 43,055 August 288,000 158,400 129,600 79,600 50,000 445 49,555 September $ 189,000 $ 103,950 85,050 70,800 14,250 445 13,805 October November 144,000 $ 135,000 79,200 74,250 64,800 60,750 66,800 66,000 (2,000) (5,250) 445 445 (2,445) (5,695) December Total $ 99,000 $ 1,809,000 54,450 994,950 44,550 814,050 62,800 808,800 (18,250) 5,250 445 3,701 (18,695) 1,549 (24,750) (31,250) For This Year February June January 68,000 $ March 51,033 $ Apr 30,000 $ May 30,000 $ July 30,000 $ $ 64,998 $ 30,000 $ August September October November December Total 60,907 $ 112,880 $ 180,526 $ 206,934 $ 214,471 $ 68,000 96.200 164,200 77.400 142,398 68,400 119,433 91,800 121,800 104,400 134,400 145,800 175,800 234.000 264.000 273.600 334,507 268,200 381,050 180.000 360.526 142.200 349.134 127,800 1,809,800 342.271 1.877,800 Heginning cash balance Add cash neoslots Collections from customers Total cash avalable Less cash disbursements: Merchandise purchases Seiling and administrative Tatal cash disbursements Excess (deficiency) of cash avalable over disbursements Fnancing: Borowings at the beginnings of months) Repayments fat end of the year) Interest at 1% per month) Total financing Ending cash balance Minimum Cash Requirement 46,002 53,2010 99,202 64,998 39,785 51,600 91,305 51,033 40,219 54,000 94,219 25.214 51,851 54,800 106,651 15,149 61,876 57,200 119,075 15,325 89.986 66,000 155,086 19.834 133,093 70,000 203,093 60.907 150,047 71,600 221,847 112.850 137,734 62,800 200,534 180.526 94,793 58.800 153,593 206.934 78,883 58.000 134,883 214.471 66,646 988,653 54,800 712,800 121,846 1,701,653 220.625 176,147 4,786 14,851 14,675 10.166 . . (44.4783 44,478 (44,478) 2,701) 3,701) 148,179) 13,701 $ 172.446 $ 172,446 4,786 30,000 $ 14,851 30,000 $ 14,675 30,000 $ 10,166 30,000 $ 51,033 $ 64,998 $ 5 60.907 112,850 $ $ 180,526 $ 206.934 $ $ 214,471 5 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 Beginning Balance Sheet Budgeting Assumptions Sales Budget Merchandise Purchases Budget Selling & Admin Budget Cash Budget Budgeted Income Statements Budgeted Bala Illallis 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 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 (308,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 (348,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 (380,000) 520,000 856,083 $ 900,000 (388,000) 512,000 775,746 Total assets $ $ $ $ $ 34,011 $ 32,608 $ 44,997 43,127 | $ 4,786 48 47,960 49,438 $ 19,637 244 70,476 $ 34,312 587 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 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 66,268 $ 44,478 2,811 113,558 58,905 $ 44,478 3,256 106,639 34,011 32,608 69,319 105,375 129,943 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 May Operating cash flows Vet income $ $ January February (3,002) $ (13,965) $ (24,750) $ (31,250) $ March (25,819) $ (21,548) $ April (14,851) $ (18,446) $ (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