Williams Company

Cash Budget

For This Year

January February March April May June July August September October November December Total

Beginning cash balance $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

Add cash receipts:

Collections from customers 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 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

Less cash disbursements:

Merchandise purchases 46,002 39,765 40,219 51,851 61,875 89,966 133,093 150,047 137,734 94,793 76,663 66,846 988,853

Selling and administrative 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

Total cash disbursements 99,202 91,365 94,219 106,651 119,075 155,966 203,093 221,647 200,534 153,593 134,663 121,646 1,701,653

Excess (deficiency) of cash available over disbursements 64,998 51,033 25,214 15,149 15,325 19,834 60,907 112,860 180,526 206,934 214,471 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) - - - - - - - - - - - (3,701) (3,701)

Total financing - - 4,786 14,851 14,675 10,166 - - - - - (48,179) (3,701)

Ending cash balance $64,998 $51,033 $30,000 $30,000 $30,000 $30,000 $60,907 $112,860 $180,526 $206,934 $214,471 $172,446 $172,446

Mininmum Cash Requirement $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 Balance Sheets

For Each Month This Year

January February March April May June July August September October November December

Assets

Current assets:

Cash $64,998 $51,033 $30,000 $30,000 $30,000 $30,000 $60,907 $112,860 $180,526 $206,934 $214,471 $172,446

Accounts receivable 64,800 50,400 72,000 79,200 100,800 180,000 216,000 230,400 151,200 115,200 108,000 79,200

Merchandise inventory 8,663 12,375 13,613 17,325 30,938 37,125 39,600 25,988 19,800 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

Accumulated depreciation (300,000) (308,000) (316,000) (324,000) (332,000) (340,000) (348,000) (356,000) (364,000) (372,000) (380,000) (388,000)

Plant and equipment, net 600,000 592,000 584,000 576,000 568,000 560,000 552,000 544,000 536,000 528,000 520,000 512,000

Total assets $738,461 $705,808 $699,613 $702,525 $729,738 $807,125 $868,507 $913,248 $887,526 $868,696 $856,083 $775,746

Liabilities and Stockholders Equity

Current liabilities:

Accounts payable $34,011 $32,608 $43,127 $49,438 $70,476 $110,447 $128,329 $123,069 $83,098 $66,268 $58,905 $44,997

Short-term note payable - - 4,786 19,637 34,312 44,478 44,478 44,478 44,478 44,478 44,478 -

Interest payable - - 48 244 587 1,032 1,477 1,922 2,366 2,811 3,256 -

Total liabilities 34,011 32,608 47,960 69,319 105,375 155,957 174,284 169,469 129,943 113,558 106,639 44,997

Stockholders equity:

Common stock 253,000 253,000 253,000 253,000 253,000 253,000 253,000 253,000 253,000 253,000 253,000 253,000

Retained earnings 451,450 420,200 398,652 380,206 371,363 398,168 441,223 490,778 504,584 502,139 496,444 477,749

Total stockholders equity $704,450 $673,200 $651,652 $633,206 $624,363 $651,168 $694,223 $743,778 $757,584 $755,139 $749,444 $730,749

Total liabilities and stockholders equity $738,461 $705,808 $699,613 $702,525 $729,738 $807,125 $868,507 $913,248 $887,526 $868,696 $856,083 $775,746

Cash Flow vs. Net Income

For Each Month This Year

January February March April May June July August September October November December Total

Operating cash flows $(3,002) $(13,965) $(25,819) $(14,851) $(14,675) $(10,166) $30,907 $51,953 $67,666 $26,408 $7,537 $2,454 $104,446

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

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. 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 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? 5 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 The company's excess (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. 7 Accounts receivable comprise the largest portion of August's current assets 2 The cash balance peaks in November. 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 The net income and operating cash flows peak in September. The net income lags operating cash flows. The operating cash flows lag net income. 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. 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 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? 5 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 The company's excess (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. 7 Accounts receivable comprise the largest portion of August's current assets 2 The cash balance peaks in November. 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 The net income and operating cash flows peak in September. The net income lags operating cash flows. The operating cash flows lag net income