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. c. 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? 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 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. 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 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. 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? Current assets: Cash Accounts receivable Merchandise inventory Total current assets Plant and equipment: Buildings and equipment Accumulated depreciation Plant and equipment, net Total assets Assets $ 68,000 80,000 13,200 $ 161,200 900,000 (292,000) 608,000 $ 769,200 Current liabilities: Liabilities and Stockholders' Equity Accounts payable Stockholders' equity: Common stock Retained earnings Total stockholders' equity Total liabilities and stockholders' equity $ 40,000 $ 253,000 476,200 729,200 $ 769,200 Sales Budget: Williams Company Budgeting Assumptions For This Year Budgeted unit sales Selling price per unit Percentage of sales that are collected in the month of sale Percentage of sales that are collected in the month after sale Merchandise Purchases Budget: Cost of goods sold as a percent of sales Percentage of next month's cost of goods sold in ending merchandise inventory Percentage of merchandise purchases that are paid for in the month of purchase Percentage of merchandise purchases that are paid for in the month after purchase Selling and Administrative Expense Budget: Variable selling and administrative expense per unit Fixed selling and administrative expense per month: Advertising Executive salaries Insurance Property tax Depreciation Cash Budget Minimum cash balance Simple interest rate per month All 12 Months January February March April May June July August September October November December 900 700 1,000 1,100 1,400 2,500 3,000 3,200 2,100 1,600 1,500 1,100 $ 90.00 20% 80% 55% 25% 15% 85% $ 8.00 $ 15,000 $ 20,000 $ 6,000 $ 5,000 $ 8,000 $ 30,000 1% Williams Company Sales Budget For This Year January February March April May June July Budgeted unit sales 900 Selling price per unit Sales $ 90.00 $ $ 81,000 $ 700 90.00 $ 63,000 $ 1,000 90.00 $ 90,000 $ 1,100 1,400 2,500 90.00 $ 90.00 $ 90.00 $ 99,000 $ 126,000 $ 225,000 $ August 3,000 3,200 2,100 90.00 $ 90.00 $ 90.00 September October November $ 1,600 90.00 $ 1,500 90.00 $ December 1,100 90.00 Total $ 20,100 90.00 270,000 $ 288,000 $ 189,000 $ 144,000 $ 135,000 $ 99,000 $1,809,000 January February Cash collections from prior month's sales $ 80,000 $ 64,800 $ Cash collections from current month's sales 16,200 12,600 Total cash collections $ 96,200 $ 77,400 $ March 50,400 $ 18,000 April 72,000 $ 19,800 79,200 $ 25,200 Schedule of Expected Cash Collections May June 100,800 $ 45,000 July 180,000 $ August 216,000 $ September 230,400 $ October November December 54,000 57,600 37,800 151,200 $ 28,800 115,200 $ 27,000 108,000 $1,448,000 19,800 361,800 68,400 $ 91,800 $ 104,400 $ 145,800 $ 234,000 $ 273,600 $ 268,200 $ 180,000 $ 142,200 $ 127,800 $1,809,800 Budgeted cost of goods sold Add desired ending merchandise inventory Total needs Less beginning merchandise inventory Required merchandise purchases Williams Company Merchandise Purchases Budget For This Year January February March April May June July August $ 44,550 $ 34,650 $ 49,500 $ 54,450 $ 69,300 $ 8,663 12,375 13,613 17,325 30,938 123,750 $ 37,125 148,500 $ September October 158,400 $ 103,950 $ 39,600 25,988 19,800 18,563 53,213 47,025 63,113 71,775 100,238 160,875 188,100 184,388 123,750 97,763 13,200 8,663 12,375 13,613 17,325 30,938 37,125 39,600 25,988 19,800 $ 40,013 $ 38,363 $ 50,738 $ 58,163 $ 82,913 $ 129,938 $ 150,975 $ 144,788 $ 97,763 $ November December Total 79,200 $ 74,250 $ 54,450 $ 994,950 12,100 12,100 66,550 1,007,050 13,613 13,200 13,613 87,863 18,563 77,963 $ 69,300 $ 52,938 $ 993,850 January Cash disbursements related to prior month's purchases Cash disbursements related to current month's purchases Total cash disbursements for merchandise purchases $ $ February 40,000 $ 34,011 $ 6,002 5,754 46,002 $ 39,765 $ April May Schedule of Expected Cash Disbursements for Merchandise Purchases March June 32,608 $ 7,611 40,219 $ 43,127 $ 8,724 51,851 $ 49,438 $ 12,437 61,875 $ 70,476 $ 19,491 July 110,447 $ 22,646 August 128,329 $ 21,718 September 123,069 $ 14,664 October 89,966 $ 133,093 $ 150,047 $ 137,734 $ November December 83,098 $ 66,268 $ 58,905 $ 11,694 94,793 $ Total 839,776 10,395 76,663 $ 7,941 66,846 $ 988,853 149,078 December ending inventory based on assumption of January sales = $88,000 Williams Company Selling and Administrative Expense Budget For This Year January February March April May 900 700 1,000 1,100 $ 8.00 $ 8.00 $ 8.00 $ $ 7,200 $ 5,600 $ 8,000 $ 8.00 $ 8,800 $ 1,400 8.00 11,200 $ $ June 2,500 8.00 $ 20,000 $ July 3,000 8.00 $ 24,000 $ August September October 3,200 2,100 8.00 $ 25,600 $ 8.00 $ 16,800 $ 1,600 8.00 $ 12,800 $ November 1,500 December Total 1,100 8.00 $ 12,000 $ 20,100 8.00 $ 8.00 8,800 $160,800 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 15,000 180,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 20,000 240,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 72,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 5,000 60,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 96,000 54,000 54.000 54.000 54,000 54,000 54.000 54,000 54,000 54,000 54.000 54.000 54,000 648,000 61,200 59,600 62,000 62,800 65.200 74,000 78,000 79,600 70,800 66,800 66,000 62,800 808,800 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 8,000 96,000 $ 53,200 $ 51,600 $ 54,000 $ 54,800 $ 57,200 $ 66,000 $ 70,000 $ 71,600 $ 62,800 $ 58,800 $ 58,000 $ 54,800 $712,800 Williams Company Cash Budget For This Year January February March April May June July August September October November December Total Beginning cash balance Add cash receipts: Collections from customers $ 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 96,200 77,400 68,400 91,800 104,400 145,800 234,000 273,600 268,200 180,000 142,200 127,800 1,809,800 Total cash available Less cash disbursements: 164,200 142,398 119,433 121,800 134,400 175,800 264,000 334,507 381,060 360,526 349,134 342,271 1,877,800 Merchandise purchases Selling and administrative Total cash disbursements 46,002 39,765 40,219 51,851 61,875 89,966 133,093 150,047 137,734 94,793 76,663 53,200 51,600 54,000 54,800 57,200 66,000 70,000 71,600 62,800 58,800 58,000 99,202 91,365 94,219 106,651 119,075 155,966 Excess (deficiency) of cash available over disbursements 64,998 51,033 25,214 15,149 15,325 19,834 203,093 60,907 221,647 112,860 200,534 180,526 153,593 206,934 134,663 214,471 66,846 988,853 54,800 712,800 121,646 1,701,653 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) Total financing Ending cash balance Mininmum Cash Requirement (3,701) (3,701) 4,786 14,851 14,675 $ 64,998 $ 51,033 $ 30,000 $ 30,000 $ 30,000 $ 10,166 30,000 $ (48,179) (3,701) 60,907 $ 112,860 $ 180,526 $ 206,934 $ 214,471 $ 172,446 $ 172,446 $ 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 Income Statements For This Year (Absorption costing basis) Sales January $ February 81,000 $ 63,000 $ March April May June July 90,000 $ 99,000 $ 126,000 $ 225,000 $ August 270,000 $ 288,000 $ September 189,000 $ October November December Total 144,000 $ 135,000 $ 99,000 $1,809,000 Cost of goods sold 44,550 34,650 49,500 54,450 69,300 123,750 148,500 158,400 103,950 79,200 74,250 54,450 994,950 Gross margin 36,450 28,350 40,500 44,550 56,700 101,250 121,500 129,600 85,050 64,800 60,750 44,550 814,050 Selling and administrative expenses 61,200 59,600 62,000 62,800 65,200 74,000 78,000 79,600 70,800 66,800 66,000 62,800 808,800 Net operating Income (24,750) (31,250) (21,500) (18,250) (8,500) 27,250 43,500 50,000 14,250 (2,000) (5,250) (18,250) 5,250 Interest expense 48 196 343 445 445 445 445 445 445 445 3,701 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 January February March April Williams Company Budgeted Balance Sheets For Each Month This Year May June July August September October November December Accumulated depreciation Plant and equipment, net Total assets Assets Current assets: Cash $ 64,998 $ 51,033 $ Accounts receivable 64,800 50,400 30,000 $ 72,000 30,000 $ 79,200 Merchandise inventory 8,663 12,375 13,613 17,325 30,000 $ 100,800 30,938 30,000 $ 180,000 37,125 60,907 $ 216,000 39,600 112,860 $ 180,526 $ 206,934 $ 214,471 $ 172,446 230,400 25,988 151,200 19,800 115,200 108,000 79,200 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 900,000 900,000 900,000 900,000 900,000 900,000 900,000 900,000 900,000 900,000 (300,000) (308,000) (316,000) (324,000) (332,000) (340,000) (348,000) (356,000) (364,000) 600,000 592,000 $ 738,461 $ 705,808 $ 584,000 699,613 $ 576,000 702,525 $ 568,000 560,000 552,000 544,000 729,738 $ 807,125 $ 868,507 $ 913,248 $ 536,000 887,526 $ (372,000) 528,000 (380,000) (388,000) 520,000 512,000 868,696 $ 856,083 $ 775,746 Liabilities and Stockholders' Equity Current liabilities: Accounts payable $ 34,011 $ 32,608 $ Short-term note payable Interest payable Total liabilities 34,011 32,608 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 128,329 $ 44,478 1,477 123,069 $ 44,478 1,922 83,098 $ 44,478 2,366 66,268 $ 44,478 2,811 155,957 174,284 169,469 129,943 113,558 58,905 $ 44,478 3,256 106,639 44,997 44,997 Stockholders' equity: Common stock Retained earnings 253,000 451,450 253,000 420,200 253,000 398,652 253,000 380,206 253,000 371,363 Total stockholders' equity $ 704,450 $ 673,200 $ 651,652 $ 633,206 $ 624,363 $ Total liabilities and stockholders' equity $ 738,461 $ 705,808 $ 699,613 $ 702,525 $ 729,738 $ 253,000 398,168 651,168 $ 807,125 $ 253,000 441,223 253,000 490,778 253,000 504,584 253,000 502,139 253,000 496,444 253,000 477,749 694,223 $ 868,507 $ 743,778 $ 757,584 $ 755,139 $ 749,444 $ 730,749 913,248 $ 887,526 $ 868,696 $ 856,083 $ 775,746 Operating cash flows Net income Williams Company Cash Flow vs. Net Income For Each Month This Year January February March April May June July August September October November $ (3,002) $ (13,965) $ (25,819) $ (14,851) $ (14,675) $ (10,166) $ 30,907 $ 51,953 $ 67,666 $ 26,408 $ 7,537 $ December Total 2,454 $ 104,446 $ (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