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 brlef 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 (Note that for all questions below you may select more than one answer. Single click the box with the question mark to produce a check mark for a correct answer and double click the box with the question mark to empty the box for a wrong answer. Any boxes left with a question mark will be automatically graded as incorrect.) a. Go to the "Sales Budget" tab and create a line chart that provides a monthly sales trend analysis. b. Which of the following statements are true based on the line chart that you created in requirement 7a? | The company's sales peak during the summer months. The company's lowest sales occur in February 2 The company's sales are relatively consistent throughout the year, 2 The monthly sales continuously drop from August through December Wiams Company Sales Budget For This You 500 Det Being February 700 1000 6.00 0001 3.0001 A My 1000 100 1650 2500 LO 90001 0.00 10.00 0 000 SOOS 20.000 25000 August 3200 3000 3.000 0.00 310000 2100 0.001 100 0006 1OOOOO Decem Total 100 29 100 1000 20.00 Otto 000 W HIS DOO Cashion from Caledon oma To whom Frey HODS 12.000 12:00 30.000 200 300 90400 1000 400 che Cash Coco A 12.000 70.00 100.000 100.000 25700 000 S000 0.00 108.400 145 2340001 2000 5240 230,00 2100 2000 OOK De 200 20 100 000 22 Am August 200.000 158 400 Williams Company Budgeted income Statements For This Year (Absorption conting) May June 126.000 225.000 69 300 123750 101,250 65.200 14000 500 405 January Feb $ 01 000 63,000 44.500 34.650 36,450 28.350 51200 9.500 24,750 01.250) Cost of goods sold es margin Selling and strative expenses Nat operating to come test expense income Mart 90.000 40.500 40.500 62000 1600 45 1143 A 99,000 54450 44.550 2000 118,250) 110 (1860 September 169.000 103,950 25,000 10.09 54250 270 000 148,500 121.500 78.000 43,800 Se 700 October T44000 79200 64800 0600 2.000) November December 335,000 $ DO OOC 74250 54450 60.750 44,560 68.000 62 800 (5.250 18.250 129.500 79.000 50.000 21250 UNT CVE CP 97 (5509 BATO 20 2009 13 AOS 055 1055 2005 (1095 Williams Company Cashbudget For This Year Jay Pay 5 58.000 5 542915 March 510335 April 300001 May 30 000 June 30.000 JU 30,000 August BOOTS 112.00 October TL 9 5 2009345 To 214471 3 000 Bebe AC Caleches Tons 000 6209 4200 17400 14250 1,400 11043) 1100 121.000 145.00 175.00 234000 25400 372.00 134 360 200 SITO 142200 2014 12700009 200 271000 134400 WE 621 OP re Menu Being $3200 000 1330 20.000 200 64 20 21214 100 10485 110 3.000 0100 155.9 18 57200 11.07 11133 2010 11033 137.734 2.000 200634 TO 7600 32141 20 100 12 2002 . 14100 712100 214701450 2206 LE 10000 000 1300 2141 Eesti - agreget= 11 Rapema 4 13811 10.100 144 HT 4448 2011 3701 19 126 30.000 14 30.000 30,000 30 000 10. Engine Chen 1122000 20.000 30.000 1 30.000 30.000 1 30.000 30.000 30.000 3001 30.000 30,000 30,000 30.000 Wiliams Company Budgeted Balance Sheets For Each Month This Year my Foy March A Candar Now 1 10000 CON 51.6335 50.400 12.34 113 0000 72 000 13613 115,012 112.440 79200 30.000 200 175 128 525 30 000 1000 30 30 000 10.00 1129 247,125 216 000 . 116.01 112 200 23400 25 20 100,5 206 906 $ 2144713 151,200 115200 ton 1900 185 13612 140.0 D 21 19 101 GINE 2 900.000 As Cunun Cash Account cette Merchandise inventory 1 Total Curon Pandum songs doqueen 1 Ace depreciation 15 Prand and To TY Stocksuit Cews Accounts payable or 22 spa 000000 100 300.000 100 000 (200 000 00000 738 461 500.000 0.00 592.000 $ TO 000000 000910 000 C G 100 000 (2000) 5.000 OOO 100.000 OOOO 000) 000 72973 51 OO 00000 10,000 CH 0000 12000 3507 0,000 24 1000 S2000 00000 w 520000 30 2000 . 546000 913248 3 S 5 wa $ $ 5 $ 1 $ 1101 3D 42013 4 7040 34 312 30005 444 121009 4470 102 4107 49438 10 ar 246 50 319 1447 1261 4470 44 1.00 174.784 16.268 470 21 11) 48 2011 47 1200 SEE SOL 461 253,000 250.000 253,000 398059 DOW ONE 25000 451 40 7040 3000 SO Stockholm Common stock x Autosedang 2 Total story 2 and so 000 DE 09 2000 420.00 673.200 70 23 253000 253,000 371 141223 MO 04223 2011 SOV 755 253,000 4044 1419 30 255.0 GOS CIUS 1 100 7025 7037 3240 -- 5 Was Company Cash Flow vs. Net Income For Each Month This Yem A My (148515 (128) (10.560) (18.446) (8.43 26.0055 server A A Operating cu flow Tutcome (1002) February March (13.05) S510) 5 312501 21.560) 30.007 13.0555 Other 67.5 2000 $1953 49.5555 Net Decante To 7.5371 2.454 1040 15.690) c. Go to the "Sales Budget" tab and create a line chart that includes the monthly sales trend analysis from requirement 7a plus another trendline pertaining to the monthly cash collections from customers. d. Which of the following statements are true based on the line chart that you created in requirement 7c? The company's soles are seasonal, but its cash collections are relatively constant throughout the year. The company's cash collections lag its sales The company's sales lag its cash collections. The company's cash collections are seasonal, but its sales are relatively constant throughout the year. 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? 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 itsekin disbursements during that same month ? The company's excess deficiency of cash available over disbursements peaks in December 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 chant 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 2 The company's excese (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. Accounts receivable comprise the largest portion of August's current assets. The cath balance peaks in November 7 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 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 2 The net income Ings operating cash flown. 2 The operating cash flows lng net income