Please help me with this assignment! Thank you so much!!!!!!!

Excel Analytics Project: Master Budgeting i Saved 1 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. 100 points Skipped 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. eBook 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, Print References 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 perta 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 partsthe 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? F35 fx B D E F G H I J L M N 1 2 Williams Company Budgeting Assumptions For This Year 3 4 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% 5 6 Sales Budget: 7 Budgeted unit sales 8 Selling price per unit 9 Percentage of sales that are collected in the month of sale 10 Percentage of sales that are collected in the month after sale 11 12 Merchandise Purchases Budget: 13 Cost of goods sold as a percent of sales 14 Percentage of next month's cost of goods sold in ending merchandise inventory 15 Percentage of merchandise purchases that are paid for in the month of purchase 16 Percentage of merchandise purchases that are paid for in the month after purchase 17 18 Selling and Administrative Expense Budget: 19 Variable selling and administrative expense per unit 20 Fixed selling and administrative expense per month: 21 Advertising 22 Executive salaries 23 Insurance 24 Property tax 25 Depreciation 26 27 Budget 28 Minimum cash balance 29 Simple interest rate per month 30 $ 8.00 $ $ $ $ $ 15,000 20,000 6,000 5,000 8,000 $ 30,000 1% 31 D6 fx ='Budgeting Assumptions'!E7 A B C D E H M N o P Q 1 F G Williams Company Sales Budget For This Year 2 3 4 5 May 6 Budgeted unit sales 7 Selling price per unit 8 Sales January 900 $ 90.00 $ 81,000 February 700 $ 90.00 $ 63,000 March 1,000 90.00 90,000 April 1,100 90.00 99,000 July 3,000 90.00 270,000 August 3,200 90.00 288,000 1,400 90.00 126,000 September 2,100 $ 90.00 June 2,500 90.00 225,000 October 1,600 $ 90.00 $ 144,000 $ November 1,500 $ 90.00 $ 135,000 $ $ December 1,100 $ 90.00 $ 99,000 $ $ $ $ Total 20,100 $ 90.00 $ 1,809,000 $ $ $ $ $ $ 189,000 9 10 11 12 13 Cash collections from prior month's sales 14 Cash collections from current month's sales 15 Total cash collections January $ 80,000 16,200 $ 96,200 February $ 64,800 $ 12,600 $ 77,400 $ March 50,400 $ 18,000 68,400 $ Schedule of Expected Cash Collections April May June July 72,000 $ 79,200 $ 100,800 $ 180,000 $ 19,800 25,200 45,000 54,000 91,800 $ 104,400 $ 145,800 $ 234,000 $ August September 216,000 $ 230,400 $ 57,600 37,800 273,600 $ 268,200 $ October November 151,200 $ 115,200 28,800 27,000 180,000 $ 142,200 December $ 108,000 19,800 $ 127,800 $ 1,448,000 361,800 $ 1,809,800 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 B M N o Williams Company Merchandise Purchases Budget For This Year July $ February $ 34,650 12,375 $ $ $ $ $ Budgeted cost of goods sold Add desired ending merchandise inventory Total needs Less beginning merchandise inventory Required merchandise purchases January 44,550 8,663 53,213 13,200 40,013 March 49,500 13,613 63,113 12,375 50,738 April 54,450 17,325 71,775 13,613 58,163 May 69,300 $ 30,938 100,238 17,325 82,913 $ June 123,750 37,125 160,875 30,938 129,938 August 158,400 25,988 184,388 39,600 144,788 148,500 $ 39,600 188,100 37,125 150,975 $ September $ 103,950 19,800 123,750 25,988 $ 97,763 October 79,200 18,563 97,763 19,800 77,963 November $ 74,250 13,613 87,863 18,563 $ 69,300 December $ 54,450 $ 12,100 66,550 13,613 $ 52,938 $ Total 994,950 12,100 1,007,050 13,200 993,850 47,025 8,663 38,363 $ $ $ $ $ $ $ $ Cash disbursements related to prior month's purchases Cash disbursements related to current month's purchases Total cash disbursements for merchandise purchases January February 40,000 $ 34,011 6,002 5,754 46,002 $ 39,765 Schedule of Expected Cash Disbursements for Merchandise Purchases March April May June July $ 32,608 $ 43,127 $ 49,438 $ 70,476 $ 110,447 $ 7,611 8,724 12,437 19,491 22,646 $ 40,219 $ 51,851 $ 61,875 $ 89,966 $ 133,093 $ August September 128,329 $ 123,069 $ 21,718 14,664 150,047 $ 137,734 $ October November December 83,098 $ 66,268 $ 58,905 $ 11,694 10,395 7,941 94,793 $ 76,663 $ 66,846 $ Total 839,776 149,078 988,853 $ December ending inventory based on assumption of January sales = $88,000 = K36 fx B D E F G H M N o 1 2 Williams Company Selling and Administrative Expense Budget For This Year 3 4 January 900 8.00 7,200 $ February 700 $ 8.00 $ $ 5,600 $ March 1,000 8.00 $ 8,000 $ April 1,100 8.00 8,800 May 1,400 8.00 11,200 June 2,500 8.00 20,000 $ July 3,000 8.00 24,000 August 3,200 8.00 $ September 2,100 $ 8.00 $ $ 16,800 $ October 1,600 8.00 12,800 November December Total 1,500 1,100 20,100 $ 8.00 $ 8.00 $ 8.00 $ 12,000 $ 8,800 $ 160,800 $ $ $ $ $ $ 25,600 5 6 Budgeted units sales 7 Variable selling and administrative expense per unit 8 Variable selling and administrative expense 9 Fixed selling and administrative expenses: 10 Advertising 11 Executive salaries 12 Insurance 13 Property taxes 14 Depreciation 15 Total fixed selling and administrative expenses 16 Total selling and administrative expenses 17 Less depreciation 18 Cash disbursements for selling and administrative expenses 19 20 21 15,000 20,000 6,000 5,000 8,000 54,000 61,200 8,000 53,200 $ 15,000 20,000 6,000 5,000 8,000 54,000 59,600 8,000 51,600 15,000 20,000 6,000 5,000 8,000 54,000 62,000 8,000 54,000 15,000 20,000 6,000 5,000 8,000 54,000 62,800 8,000 54,800 15,000 20,000 6,000 5,000 8,000 54,000 65,200 8,000 57,200 15,000 20,000 6,000 5,000 8,000 54,000 74,000 8,000 66,000 15,000 20,000 6,000 5,000 8,000 54,000 78,000 8,000 70,000 $ 15,000 20,000 6,000 5,000 8,000 54,000 79,600 8,000 71,600 15,000 20,000 6,000 5,000 8,000 54,000 70,800 8,000 62,800 15,000 20,000 6,000 5,000 8,000 54,000 66,800 8,000 58,800 15,000 20,000 6,000 5,000 8,000 54,000 66,000 8,000 58,000 15,000 180,000 20,000 240,000 6,000 72,000 5,000 60,000 8,000 96,000 54,000 648,000 62,800 808,800 8,000 96,000 54,800 $ 712,800 $ $ $ $ $ $ $ $ $ $ C12 fx ='Selling & Admin Budget'!C18 A B D E F G H I K L M N 0 P Q R S T U V 1 2 Williams Company Cash Budget For This Year 3 4 May January 68,000 $ February 64,998 March 51,033 $ April 30,000 June 30,000 July 30,000 August 60,907 September $ 112,860 October 180,526 November December $ 206,934 $ 214,471 Total 68,000 $ $ 30,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,060 180,000 360,526 142,200 349,134 127,800 342,271 1,809,800 1,877,800 46,002 53,200 99,202 64,998 39,765 51,600 91,365 51,033 40,219 54,000 94,219 25,214 51,851 54,800 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,600 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,800 121,646 220,625 988,853 712,800 1,701,653 176,147 5 6 Beginning cash balance 7 Add cash receipts: 8 Collections from customers 9 Total cash available 10 Less cash disbursements: 11 Merchandise purchases 12 Selling and administrative 13 Total cash disbursements 14 Excess (deficiency) of cash available over disbursements 15 Financing: 16 Borrowings (at the beginnings of months) Repayments (at end of the year) 18 Interest (at 1% per month) 19 Total financing 20 Ending cash balance 21 22 Mininmum Cash Requirement 23 24 25 26 27 4,786 14,851 14,675 10,166 17 44,478 (44,478) (3,701) (3,701) $ 172,446 (44,478) (3,701) (48,179) 172,446 4,786 30,000 14,851 30,000 14,675 30,000 $ 10,166 30,000 $ $ 64,998 $ 51,033 $ $ $ 60,907 $ 112,860 $ 180,526 $ 206,934 $ 214,471 $ $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 $ 30,000 G12 fx =('Cash Budget'!$B$16+'Cash Budget'!$C$16+'Cash Budget'!$D$16+'Cash Budget'!$E$16+'Cash Budget'!$F$16+'Cash Budget'!$G$16)*0.01 B D E F G H J K M N O P Q 1 2 Williams Company Budgeted Income Statements For This Year (Absorption costing basis) 3 4 5 $ $ $ $ 6 7 Sales 8 Cost of goods sold 9 Gross margin 10 Selling and administrative expenses 11 Net operating Income 12 Interest expense 13 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 September 288,000 $ 189,000 158,400 103,950 129,600 85,050 79,600 70,800 50,000 14,250 445 445 49,555 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) 14 15 16 D20 XV.fx =' Merchandise Purchases Budget'ID10Budgeting Assumptions ISR$16 B c D E L M N 0 1 1 Williams Company Bulgated Balance Sheets For Each Month This Year January February March Aon May June August September October November December s 3 64.988 $ 64800 0.663 138 461 51,053 $ 5 50,400 12,375 113,809 30,000 S 72,000 13,812 115,613 30,000 $ 79.200 17.325 126.525 30.000 100 000 30,92 161,738 30,000 $ 100,000 37,125 247,125 60,907 3 5 216,000 39,800 316.5017 112 860 $ 230 400 25.908 369 248 180,525 S 151,200 19,800 381,52 206,831 $ 115,200 18,562 340,0196 214,171 $ 100,000 13,613 330.03 172416 79200 12,100 263.746 900 000 (200,000) 800.000 738 461 $ 900,000 (306,000 592,000 7015,HDH S 900,000 (316,000) 514.000 699,613 $ 900.000 (224.000 578,000 702.525 BOD 900,000 (232.000 568,000 720.728 900,000 (340,000 ) 580,000 807,125 S 900,000 (349,000) 552,000 868.5517 $ 900 000 (368 000) 544 000 900,000 (264,000 536,000 887,520 S 900,000 (372,000) 528,000 868,696 $ 900,000 (380,000 520.000 854.03 $ 900 OLO (280.000) 512.000 775,746 $ $ S 913 248 $ | Assets 7 Current Assets: Cash Accounts receivable 10 Merchandise inventory 11 Total CurTANT ASSAS 12 plant and equipment: 13 Buildings and equipment 14 Accumulated depreciation 16 Plant and equipment, nel 16 Total assels 17 12 Liabilities and Stockholders' Equity 19 Current liabilities Accounts payable 21 Short-terin nore payable - 22 Interesi payable 28 Total liabilities 24 Stockholders' equity 25 Common slock 28 Relained earrings 27 Total stockholders' equily 22 Total liabilities and stockholders' equity 20 $ 34.011 $ 32,609 $ 44.997 43,127 S 4,786 48 47,960 49.4383 19,637 244 69.319 70.476 S 34 312 587 105,375 110,447 S 44,478 1,032 156,967 128,3295 44,178 1,477 174,284 123 069 $ 41.478 1922 169.489 83,0395 44478 2,368 129,943 66,268 S 44.478 2,811 113,568 58.905 S 44,178 3,256 108,639 34.011 32,808 44.997 253.000 451 450 704450 $ 736 481 $ 253,000 420,200 673,200 $ 705,808 S 253,000 398,652 651,652 3 699,813 $ 3 5 253,000 380,206 633,206 702.626 253,000 371.363 624.363 S 729,729 s 253,000 398,16 651,1685 807,125 S 253,000 441,223 694.223 5 889,507 $ 253.000 490.778 743.778 5 $ 913 248 $ 253,000 504,584 757,584 S B97.520 S 253,000 502,138 755,139 3 888,898S 253,000 498,444 749,444 5 858,083 3 253000 477.749 730.749 775,746 H26 fx A B D E F H M N o Q 1 2 Williams Company Cash Flow vs. Net Income For Each Month This Year 3 4 A January February (3,002) $ (13,965) $ (24,750) $ (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 $ 5 6 Operating cash flows 7 Net income 8 9 10 11 12 13 14 15