Required information

Skip to question

[The following information applies to the questions displayed below.] Williams Companys 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 companys 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.

Required: To improve your understanding of all schedules included in the master budget and their interrelationships, answer the following questions: 1. Go to the Sales Budget tab:

a. How are the sales for July (cell H8) being calculated?

b. How are the cash collections for August (cell I15) being calculated?

2. Go to the Merchandise Purchases Budget tab:

a. How are the required merchandise purchases for April (cell E10) being calculated?

b. How are the cash disbursements for merchandise purchases for May (cell F17) being calculated?

3. Go to the Selling & Admin Budget tab:

a. How are the total selling and administrative expenses for September (cell J16) being calculated?

b. How are the cash disbursements for selling and administrative expenses for November (cell L18) being calculated?

4. Go to the Cash Budget tab:

a. How is the excess (deficiency) of cash available over disbursements for March (cell D14) being calculated?

b. How is the ending cash balance for June (cell G20) being calculated?

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: (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?

check all that apply 1

• The company's sales peak during the summer months.
• The company's lowest sales occur in February.unanswered
• The company's sales are relatively consistent throughout the year.unanswered
• The monthly sales continuously drop from August through December.unanswered

c. 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.

d. Which of the following statements are true based on the line chart that you created in requirement 7c?

check all that apply 2

• The company's sales are seasonal, but its cash collections are relatively constant throughout the year.unanswered
• The company's cash collections lag its sales.unanswered
• The company's sales lag its cash collections.unanswered
• The companys cash collections are seasonal, but its sales are relatively constant throughout the year.unanswered

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?

check all that apply 3

• The sales peak during the winter months and net income peaks during the summer months.unanswered
• The sales peak during the summer months and net income peaks during the winter months.unanswered
• The sales and net income both peak during the summer months.unanswered
• The sales and net income both peak during the winter months.unanswered

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 months 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 companys 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?

check all that apply 4

• The company's cash collections in February are greater than its cash disbursements during that same month.unanswered
• The company's excess (deficiency) of cash available over disbursements peaks in December.unanswered
• The company's excess (deficiency) of cash available over disbursements hits its lowest point in March.unanswered
• The company needs to borrow money during the year including borrowings in May and June.unanswered

c. Go to the Budgeted Balance Sheets tab and create a bar chart that depicts each months 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.

d. Which of the following statements are true based on the bar chart that you created in requirement 8c?

check all that apply 5

• The total current assets are highest in June and July.unanswered
• Accounts receivable comprise the largest portion of Augusts current assets.unanswered
• The cash balance peaks in November.unanswered
• Inventory comprises the largest portion of the total current assets in June.unanswered

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?

check all that apply 6

• The net income and operating cash flows peak in August.unanswered
• The net income and operating cash flows peak in September.unanswered
• The net income lags operating cash flows.unanswered
• The operating cash flows lag net income.unanswered

Williams Company
Balance Sheet
Beginning of the Year
Assets
Current assets:
Cash	$ 68,000
Accounts receivable	80,000
Merchandise inventory	13,200
Total current assets		$ 161,200
Plant and equipment:
Buildings and equipment	900,000
Accumulated depreciation	(292,000)
Plant and equipment, net		608,000
Total assets		$ 769,200
Liabilities and Stockholders' Equity
Current liabilities:
Accounts payable		$ 40,000
Stockholders' equity:
Common stock	$ 253,000
Retained earnings	476,200
Total stockholders' equity		729,200
Total liabilities and stockholders' equity		$ 769,200

Williams Company
Budgeting Assumptions
For This Year
	All 12 Months	January	February	March	April	May	June	July	August	September	October	November	December
Sales Budget:
Budgeted unit sales		900	700	1,000	1,100	1,400	2,500	3,000	3,200	2,100	1,600	1,500	1,100
Selling price per unit	$ 90.00
Percentage of sales that are collected in the month of sale	20%
Percentage of sales that are collected in the month after sale	80%
Merchandise Purchases Budget:
Cost of goods sold as a percent of sales	55%
Percentage of next month's cost of goods sold in ending merchandise inventory	25%
Percentage of merchandise purchases that are paid for in the month of purchase	15%
Percentage of merchandise purchases that are paid for in the month after purchase	85%
Selling and Administrative Expense Budget:
Variable selling and administrative expense per unit	$ 8.00
Fixed selling and administrative expense per month:
Advertising	$ 15,000
Executive salaries	$ 20,000
Insurance	$ 6,000
Property tax	$ 5,000
Depreciation	$ 8,000
Cash Budget
Minimum cash balance	$ 30,000
Simple interest rate per month	1%

Williams Company
Sales Budget
For This Year
	January	February	March	April	May	June	July	August	September	October	November	December	Total
Budgeted unit sales	900	700	1,000	1,100	1,400	2,500	3,000	3,200	2,100	1,600	1,500	1,100	20,100
Selling price per unit	$ 90.00	$ 90.00	$ 90.00	$ 90.00	$ 90.00	$ 90.00	$ 90.00	$ 90.00	$ 90.00	$ 90.00	$ 90.00	$ 90.00	$ 90.00
Sales	$ 81,000	$ 63,000	$ 90,000	$ 99,000	$ 126,000	$ 225,000	$ 270,000	$ 288,000	$ 189,000	$ 144,000	$ 135,000	$ 99,000	$ 1,809,000
Schedule of Expected Cash Collections
	January	February	March	April	May	June	July	August	September	October	November	December
Cash collections from prior month's sales	$ 80,000	$ 64,800	$ 50,400	$ 72,000	$ 79,200	$ 100,800	$ 180,000	$ 216,000	$ 230,400	$ 151,200	$ 115,200	$ 108,000	$ 1,448,000
Cash collections from current month's sales	16,200	12,600	18,000	19,800	25,200	45,000	54,000	57,600	37,800	28,800	27,000	19,800	361,800
Total cash collections	$ 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

Williams Company
Merchandise Purchases Budget
For This Year
	January	February	March	April	May	June	July	August	September	October	November	December	Total
Budgeted cost of goods sold	$ 44,550	$ 34,650	$ 49,500	$ 54,450	$ 69,300	$ 123,750	$ 148,500	$ 158,400	$ 103,950	$ 79,200	$ 74,250	$ 54,450	$ 994,950
Add desired ending 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	12,100
Total needs	53,213	47,025	63,113	71,775	100,238	160,875	188,100	184,388	123,750	97,763	87,863	66,550	1,007,050
Less beginning merchandise inventory	13,200	8,663	12,375	13,613	17,325	30,938	37,125	39,600	25,988	19,800	18,563	13,613	13,200
Required merchandise purchases	$ 40,013	$ 38,363	$ 50,738	$ 58,163	$ 82,913	$ 129,938	$ 150,975	$ 144,788	$ 97,763	$ 77,963	$ 69,300	$ 52,938	$ 993,850
Schedule of Expected Cash Disbursements for Merchandise Purchases
	January	February	March	April	May	June	July	August	September	October	November	December	Total
Cash disbursements related to prior month's purchases	$ 40,000	$ 34,011	$ 32,608	$ 43,127	$ 49,438	$ 70,476	$ 110,447	$ 128,329	$ 123,069	$ 83,098	$ 66,268	$ 58,905	$ 839,776
Cash disbursements related to current month's purchases	6,002	5,754	7,611	8,724	12,437	19,491	22,646	21,718	14,664	11,694	10,395	7,941	149,078
Total cash disbursements for 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
December ending inventory based on assumption of January sales = $88,000

Williams Company
Merchandise Purchases Budget
For This Year
	January	February	March	April	May	June	July	August	September	October	November	December	Total
Budgeted cost of goods sold	$ 44,550	$ 34,650	$ 49,500	$ 54,450	$ 69,300	$ 123,750	$ 148,500	$ 158,400	$ 103,950	$ 79,200	$ 74,250	$ 54,450	$ 994,950
Add desired ending 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	12,100
Total needs	53,213	47,025	63,113	71,775	100,238	160,875	188,100	184,388	123,750	97,763	87,863	66,550	1,007,050
Less beginning merchandise inventory	13,200	8,663	12,375	13,613	17,325	30,938	37,125	39,600	25,988	19,800	18,563	13,613	13,200
Required merchandise purchases	$ 40,013	$ 38,363	$ 50,738	$ 58,163	$ 82,913	$ 129,938	$ 150,975	$ 144,788	$ 97,763	$ 77,963	$ 69,300	$ 52,938	$ 993,850
Schedule of Expected Cash Disbursements for Merchandise Purchases
	January	February	March	April	May	June	July	August	September	October	November	December	Total
Cash disbursements related to prior month's purchases	$ 40,000	$ 34,011	$ 32,608	$ 43,127	$ 49,438	$ 70,476	$ 110,447	$ 128,329	$ 123,069	$ 83,098	$ 66,268	$ 58,905	$ 839,776
Cash disbursements related to current month's purchases	6,002	5,754	7,611	8,724	12,437	19,491	22,646	21,718	14,664	11,694	10,395	7,941	149,078
Total cash disbursements for 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
December ending inventory based on assumption of January sales = $88,000

Williams Company
Selling and Administrative Expense Budget
For This Year
	January	February	March	April	May	June	July	August	September	October	November	December	Total
Budgeted units sales	900	700	1,000	1,100	1,400	2,500	3,000	3,200	2,100	1,600	1,500	1,100	20,100
Variable selling and administrative expense per unit	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00	$ 8.00
Variable selling and administrative expense	$ 7,200	$ 5,600	$ 8,000	$ 8,800	$ 11,200	$ 20,000	$ 24,000	$ 25,600	$ 16,800	$ 12,800	$ 12,000	$ 8,800	$ 160,800
Fixed selling and administrative expenses:
Advertising	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	15,000	180,000
Executive salaries	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	20,000	240,000
Insurance	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	72,000
Property taxes	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	60,000
Depreciation	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	96,000
Total fixed selling and administrative expenses	54,000	54,000	54,000	54,000	54,000	54,000	54,000	54,000	54,000	54,000	54,000	54,000	648,000
Total selling and administrative expenses	61,200	59,600	62,000	62,800	65,200	74,000	78,000	79,600	70,800	66,800	66,000	62,800	808,800
Less depreciation	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	96,000
Cash disbursements for selling and administrative expenses	$ 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

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 Income Statements
For This Year
(Absorption costing basis)
	January	February	March	April	May	June	July	August	September	October	November	December	Total
Sales	$ 81,000	$ 63,000	$ 90,000	$ 99,000	$ 126,000	$ 225,000	$ 270,000	$ 288,000	$ 189,000	$ 144,000	$ 135,000	$ 99,000	$ 1,809,000
Cost of goods sold	44,550	34,650	49,500	54,450	69,300	123,750	148,500	158,400	103,950	79,200	74,250	54,450	994,950
Gross margin	36,450	28,350	40,500	44,550	56,700	101,250	121,500	129,600	85,050	64,800	60,750	44,550	814,050
Selling and administrative expenses	61,200	59,600	62,000	62,800	65,200	74,000	78,000	79,600	70,800	66,800	66,000	62,800	808,800
Net operating Income	(24,750)	(31,250)	(21,500)	(18,250)	(8,500)	27,250	43,500	50,000	14,250	(2,000)	(5,250)	(18,250)	5,250
Interest expense	-	-	48	196	343	445	445	445	445	445	445	445	3,701
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

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

Williams Company
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