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. 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" 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? ok Complete this question by entering your answers in the tabs below. ht Req 1 Req 2 Reg 3 Reg 4 Reg 5 Reg 6 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? 1-a. July's budgeted unit sales - selling price per unit July's budgeted unit sales x selling price per unit July's budgeted unit sales + selling price per unit July's budgeted unit sales - selling price per unit 1-b. Expected cash collected from August's credit sales + expected cash collected from July's credit sales August's sales + expected cash collected from July's credit sales OExpected cash collected from August's credit sales - cash collected from July's credit sales O Expected cash collected from August's credit sales + July's sales Req 1 Reg 2 Req3 Reg 4 Reg 5 Reg 6 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? 2-a. Total needs + ending merchandise inventory Total needs - ending merchandise inventory Total needs - beginning merchandise inventory Total needs + beginning merchandise inventory 2-b. May's purchases * percentage of merchandise purchases that are paid for in the month after purchase May's purchases x percentage of merchandise purchases that are paid for in the month of purchase Cash payments related to May's purchases - cash payments related to April's purchases Cash payments related to May's purchases + cash payments related to April's purchases Reg 1 Reg 2 Reg 3 Reg 4 Reg 5 Reg 6 Go to the "Selling & Admin Budget" tab: a. How are the total selling and administrative expenses for September (cell 316) being calculated? b. How are the cash disbursements for selling and administrative expenses for November (cell L18) being calculated? |3-a. Total variable selling and administrative expense = total fixed selling and administrative expense Total variable selling and administrative expense x total fixed selling and administrative expense Total variable selling and administrative expense + total fixed selling and administrative expense Total variable selling and administrative expense - total fixed selling and administrative expense 3-b. Total selling and administrative expense + depreciation Total selling and administrative expense - depreciation Total selling and administrative expense depreciation Total selling and administrative expense - depreciation Reg 1 Reg 2 Reg 3 Req 4 Reg 5 Reg 6 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? 4-a. Total cash available - total cash disbursements Total cash available + total cash disbursements Total cash available x total cash disbursements Total cash available + total cash disbursements 4-b. OExcess (deficiency) of cash available over disbursements total financing OExcess (deficiency) of cash available over disbursements x total financing OExcess (deficiency) of cash available over disbursements + total financing OExcess (deficiency) of cash available over disbursements = total financing Reg 1 Reg 2 Reg 3 Req 4 Reg 5 Reg 6 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? 5-a. February's sales = cost of goods sold as a percentage of sales OFebruary's sales Reg 1 Req 2 Reg 3 Reg 4 Reg 5 Req 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? Show less 6-a. December sales x percentage of December sales collected in December December sales percentage of December sales collected in January November sales x percentage of November sales collected in December November sales - percentage of November sales collected in December 6-b. September cost of goods sold * percentage of next month's cost of goods sold in ending merchandise inventory October cost of goods sold * "percentage of next month's cost of goods sold in ending merchandise inventory" November cost of goods sold "percentage of next month's cost of goods sold in ending merchandise inventory" October cost of goods sold * (1 "percentage of next month's cost of goods sold in ending merchandise inventory") 6-C. December merchandise purchases * "percentage of merchandise purchases that are paid for in the month of purchase" January merchandise purchases * "percentage of merchandise purchases that are paid for in the month of purchase" January merchandise purchases * percentage of merchandise purchases that are paid for in the month after purchase December merchandise purchases * "percentage of merchandise purchases that are paid for in the month after purchase" 6-d. Retained earnings on April 1st + April's net income ORetained earnings on April 31st + April's net income ORetained earnings on April 1st - April's net income Retained earnings on April 31st - April's net income