1 102 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 Excellerate which you will answer the sestions that follow Click here for a brief tutorial on Charts in Excel look D 1 1 Click here for a brief tutorial on Charts in Excel Part 1 of 2 10 Doint 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 115) 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 tabs 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? References 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 | The company's sales are relatively consistent throughout the year. The monthly sales continuously drop from August through December 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? The company's sales 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 yeat 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 peok during the winter months and net income peaks during the summer months The sales penk 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 botn 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 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 dimbursements 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 cash balance peaks in November 2nventory comprises the targest 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 Be? The net income and operating cash flows peak in August, The net income and operating cosh flows peak in September The net income lags operating cash flows. 2 The operating cash flows tag net income 1 102 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 Excellerate which you will answer the sestions that follow Click here for a brief tutorial on Charts in Excel look D 1 1 Click here for a brief tutorial on Charts in Excel Part 1 of 2 10 Doint 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 115) 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 tabs 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? References 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 | The company's sales are relatively consistent throughout the year. The monthly sales continuously drop from August through December 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? The company's sales 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 yeat 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 peok during the winter months and net income peaks during the summer months The sales penk 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 botn 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 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 dimbursements 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 cash balance peaks in November 2nventory comprises the targest 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 Be? The net income and operating cash flows peak in August, The net income and operating cosh flows peak in September The net income lags operating cash flows. 2 The operating cash flows tag net income