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. 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? Williams Company Sales Budget For This Year Budgeted unit sales Selling price per unit Sales January February 900 700 $ $ 90.00 $ 90.00 $ $ 81.000 $ 63.000 $ March 1,000 90.00 $ 90.000 $ April 1,100 90.00 $ 99.000 $ May 1,400 90.00 $ 126,000 $ June 2,500 90.00 $ 225.000 $ July 3,000 90.00 $ 270.000 $ August September 3,200 2,100 90.00 $ 90.00 $ 288.000 $ 189,000 $ October 1,600 90.00 $ 144.000 $ November December Total 1,500 1,100 20,100 90.00 $ 90.00 $ 90.00 135,000 $ 99.000 $ 1.809.000 March 50,400 $ Cash collections from prior month's sales Cash collections from current month's sales Total cash collections January $ 80,000 $ 16,200 $ 96.200 $ February 64,800 $ 12,600 77.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 151,200 $ 28.800 180.000 $ November December 115,200 $ 108,000 $ 1,448,000 27.000 19.800 361.800 142.200 $ 127.800 $ 1.809.800 18.000 68.400 $ 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 February 81,000 $ 63,000 $ 44,550 34,650 36,450 28,350 61,200 59,600 (24.750) (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 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) 15.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) 26.805