! 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 5. Go to the "Budgeted Income Statements" tab: a. How is the cost of goods sold for February (cell C8) being calculated? b. How is the net income for April (cell E13) being calculated? 6. Go to the Budgeted Balance Sheets" tab: a. How are the accounts receivable for December (cell M9) being calculated? b. How is the ending merchandise inventory for October (cell K10) being calculated? c. How is the accounts payable for January (cell B20) being calculated? d. How is the retained earnings for April (cell E26) being calculated? 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 70, 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 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? Ar Anary 1.000 O OVOS Wiliams Company Budited income Statements For This Year Absorption during hos A May Ane 9.2005 126.000 1 225.000 3 14450 300 123.750 450 56700 101.350 02.100 45200 74000 (10.200 100 343 10446) 20 Novem 144.000 5 13.000 79200 14.300 0.750 OSETZ febowy 9,000 34650 20,350 1,400 1,250) Saias Castgods sold ons may Being an inte sperses Natoperating home expense Nation M 0.000 49500 4900 62.000 500 41 CUMA 35450 01200 04700 270.000 148.500 12 500 300 43.500 445 4305 Agu September O 260 000 $ SE DOOS 16800 120.000 25.050 900 58.000 14250 445 495 120 December 000 1.100.000 04450 194999 46.500 14050 200 5.250 445 DOVOL 00000 Co 15.2501 co 12.000 445 ! SYP WP VOEG 2470) 02600 INOR 5451 721 SELI Williams Company Budgeted Balance Sheets For Each oth This Year Konuer RY WAVES AN My way September Oe November Depenter 54995 54800 16 10900 51.091 50,400 30.000 180.000 6. 216.000 172.448 79200 10.000 72.000 1363 115.613 79200 17 20.000 $ 10000 300 16 11310S 730400 25. 309240 206934 115.200 1842 140.00 1805265 151200 1900 3515 003 SIECI A Cura CE Azercelable 10 Merchandise Tout Mastanderent Building and past Nowe Pantander TO 2144713 108.000 1360 330.00) SEE 12.00 10 247.25 3165 26374 We 13.461 100.000 200.000 000000 300,000 000 000 too ooo 0.000 900.000 . 44000 1000 FD 000 0.000 2000 208.30 000.000 (124.000 38000 POU 95 QUY ww 000.000 IVRE 500 72.00 120.000 16 000000 000 144.000 01324 HA 121115 50.000 ART 201 100.000 40 $20.000 616083 300.000 (5000) 013000 TY SOY # OR 30.000 05 447 SELT 41438 1 $ 704168 34312 47 16 LY 110 47 8 4418 1032 1997 125.000 H48 19:12 1640 120329 4416 14 174284 83.0 4410 2 MB 2943 RE 5.1 4448 3026 106.65 244 69,319 44478 2011 113.550 and Stoholder Curl 20 AG payable 21 Shote pale 22 Western 23 Totales 24 Sequity Camino OCH Retained 27 Todos equity Total abilities and stockholm 449 3401 105.375 32.600 47960 253000 253.000 477749 CHE 2:53.000 451/450 7044905 738.4813 253000 420 290 673.2003 705.08 250.000 30652 681062 69976135 5.000 380.206 633 2001 702.5251 250.000 441223 69422) 366.3871 253,000 490226 743 778 913200 253,000 304.534 7511 1926 1 253000 1993 155,11 500.000 5 253000 406.444 749 4445 16.000 | 5 GLOR 6231 72.7361 65116 01251 775 746 5 Witam Company Sales Budget For This Year January 005 A 1,100 February 700 3000 1000 Dudged it faling ties 99.00 My une 1400 2.500 09.00 120.000 220.000 0006 July 3.000 00.00 220.000 000 Som October November 3200 2.100 1.000 1500 $0.00 $ 09001 8.8005 0.0005 144.000 DOO Decor Te 1.100 20.00 DO 1 99.000 100.00 0006 $ 0000 0006 E 10000 0000 OO 2009 000 Ger A Man 10400 230.400 Cachecompro 14 Cashlessionamentos Yoshow 151.00 February 0.0001 400 10200 00200 77400 Schedule of Cash Colecione A May Ane 2000 20 100.00 10000 12800 25.200 45.000 4000 010 104400 145.00 234000 000 009 Aga 216.000 67600 272.000 1 OVE Now De 16.2001 106.000 45.00 21000 15.000 36300 142200 12700 GO DE 440 204 200 100.000 eco Wilms Com Chudo For This Year Corte September Fy 0001 441 Mary 51.00 5 1 AN 30.05 My 30.000 A 10.000 30.000 Ag OF Miver Drwer 2005 T! Ofer 1805 To 000 $ See Athe Collection to 9529 11840 OR 21 200 1422 BER 400 12 68.400 119,00 WE WE MORE ROOR 2 0047 OLEN 70,000 M 0.001 132 6161 1.00 110 133033 2009 37.744 2.00 be 10.219 200 29 71.294 Leder "Merchandise puhates Selgene Toda Desenhver Francing Repayment of the restat per month 120.00 WE A RE RO 104.5 11 15 1334 RE 390 2014 1 7443 513 WAL 2012 71 100 - atureous - 4. 14 100 44 444 MET TO 3,18 12 IN 30.800 10051 2009 10614 200 112 20 EL 20 dng cash 31 22 Mr Calm 23 30,000 0 1 30.000 0.0001 0.0001 00 00 30.01 30.000 30,000 $ 20.000 30.000 Williams Company Cash Flow vs. Net Income For Each Month The Year April Ane (1485) 114.675) (10.) (18.416) (343) 20.3055 anny $ Operatowa Neller January 0.000) 124,750 February (1305) 5 01250) $ () March 25813 21548) Ay 30 SOT + 430555 September October November 5135 67.6665 26.4083 75375 49,5555 13.3055 12445) 15.00 $ 1 2454 3 104440 (165) 1 1.40 ! 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 5. Go to the "Budgeted Income Statements" tab: a. How is the cost of goods sold for February (cell C8) being calculated? b. How is the net income for April (cell E13) being calculated? 6. Go to the Budgeted Balance Sheets" tab: a. How are the accounts receivable for December (cell M9) being calculated? b. How is the ending merchandise inventory for October (cell K10) being calculated? c. How is the accounts payable for January (cell B20) being calculated? d. How is the retained earnings for April (cell E26) being calculated? 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 70, 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 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? Ar Anary 1.000 O OVOS Wiliams Company Budited income Statements For This Year Absorption during hos A May Ane 9.2005 126.000 1 225.000 3 14450 300 123.750 450 56700 101.350 02.100 45200 74000 (10.200 100 343 10446) 20 Novem 144.000 5 13.000 79200 14.300 0.750 OSETZ febowy 9,000 34650 20,350 1,400 1,250) Saias Castgods sold ons may Being an inte sperses Natoperating home expense Nation M 0.000 49500 4900 62.000 500 41 CUMA 35450 01200 04700 270.000 148.500 12 500 300 43.500 445 4305 Agu September O 260 000 $ SE DOOS 16800 120.000 25.050 900 58.000 14250 445 495 120 December 000 1.100.000 04450 194999 46.500 14050 200 5.250 445 DOVOL 00000 Co 15.2501 co 12.000 445 ! SYP WP VOEG 2470) 02600 INOR 5451 721 SELI Williams Company Budgeted Balance Sheets For Each oth This Year Konuer RY WAVES AN My way September Oe November Depenter 54995 54800 16 10900 51.091 50,400 30.000 180.000 6. 216.000 172.448 79200 10.000 72.000 1363 115.613 79200 17 20.000 $ 10000 300 16 11310S 730400 25. 309240 206934 115.200 1842 140.00 1805265 151200 1900 3515 003 SIECI A Cura CE Azercelable 10 Merchandise Tout Mastanderent Building and past Nowe Pantander TO 2144713 108.000 1360 330.00) SEE 12.00 10 247.25 3165 26374 We 13.461 100.000 200.000 000000 300,000 000 000 too ooo 0.000 900.000 . 44000 1000 FD 000 0.000 2000 208.30 000.000 (124.000 38000 POU 95 QUY ww 000.000 IVRE 500 72.00 120.000 16 000000 000 144.000 01324 HA 121115 50.000 ART 201 100.000 40 $20.000 616083 300.000 (5000) 013000 TY SOY # OR 30.000 05 447 SELT 41438 1 $ 704168 34312 47 16 LY 110 47 8 4418 1032 1997 125.000 H48 19:12 1640 120329 4416 14 174284 83.0 4410 2 MB 2943 RE 5.1 4448 3026 106.65 244 69,319 44478 2011 113.550 and Stoholder Curl 20 AG payable 21 Shote pale 22 Western 23 Totales 24 Sequity Camino OCH Retained 27 Todos equity Total abilities and stockholm 449 3401 105.375 32.600 47960 253000 253.000 477749 CHE 2:53.000 451/450 7044905 738.4813 253000 420 290 673.2003 705.08 250.000 30652 681062 69976135 5.000 380.206 633 2001 702.5251 250.000 441223 69422) 366.3871 253,000 490226 743 778 913200 253,000 304.534 7511 1926 1 253000 1993 155,11 500.000 5 253000 406.444 749 4445 16.000 | 5 GLOR 6231 72.7361 65116 01251 775 746 5 Witam Company Sales Budget For This Year January 005 A 1,100 February 700 3000 1000 Dudged it faling ties 99.00 My une 1400 2.500 09.00 120.000 220.000 0006 July 3.000 00.00 220.000 000 Som October November 3200 2.100 1.000 1500 $0.00 $ 09001 8.8005 0.0005 144.000 DOO Decor Te 1.100 20.00 DO 1 99.000 100.00 0006 $ 0000 0006 E 10000 0000 OO 2009 000 Ger A Man 10400 230.400 Cachecompro 14 Cashlessionamentos Yoshow 151.00 February 0.0001 400 10200 00200 77400 Schedule of Cash Colecione A May Ane 2000 20 100.00 10000 12800 25.200 45.000 4000 010 104400 145.00 234000 000 009 Aga 216.000 67600 272.000 1 OVE Now De 16.2001 106.000 45.00 21000 15.000 36300 142200 12700 GO DE 440 204 200 100.000 eco Wilms Com Chudo For This Year Corte September Fy 0001 441 Mary 51.00 5 1 AN 30.05 My 30.000 A 10.000 30.000 Ag OF Miver Drwer 2005 T! Ofer 1805 To 000 $ See Athe Collection to 9529 11840 OR 21 200 1422 BER 400 12 68.400 119,00 WE WE MORE ROOR 2 0047 OLEN 70,000 M 0.001 132 6161 1.00 110 133033 2009 37.744 2.00 be 10.219 200 29 71.294 Leder "Merchandise puhates Selgene Toda Desenhver Francing Repayment of the restat per month 120.00 WE A RE RO 104.5 11 15 1334 RE 390 2014 1 7443 513 WAL 2012 71 100 - atureous - 4. 14 100 44 444 MET TO 3,18 12 IN 30.800 10051 2009 10614 200 112 20 EL 20 dng cash 31 22 Mr Calm 23 30,000 0 1 30.000 0.0001 0.0001 00 00 30.01 30.000 30,000 $ 20.000 30.000 Williams Company Cash Flow vs. Net Income For Each Month The Year April Ane (1485) 114.675) (10.) (18.416) (343) 20.3055 anny $ Operatowa Neller January 0.000) 124,750 February (1305) 5 01250) $ () March 25813 21548) Ay 30 SOT + 430555 September October November 5135 67.6665 26.4083 75375 49,5555 13.3055 12445) 15.00 $ 1 2454 3 104440 (165) 1 1.40
