Instruction: Complete a Master Budget in Excel using the following assumptions and template using Excel formulas

SHOW ME ALL THE FORMULAS PLEASE! THAT'S THE MOST IMPORTANT PART. THANK YOU.

Sales Budget Expected sales volume: 4,100 units in the first quarter with 500-unit increases in each succeeding quarter. Sales price: $65 per unit. Production Budget Company can meet future sales needs with an ending inventory of 20% of next quarter's budgeted sales volume. Direct Materials Budget Company maintains an ending inventory of raw materials equal to 10% of the next quarter's production requirements. The manufacture of each unit requires 2 pounds of raw materials, and the expected cost per pound is $4. Assume that the desired ending direct materials amount is 1,200 pounds for the fourth quarter of 2020. Direct Labor Budget Two hours of direct labor are required to produce each unit of finished goods. The anticipated hourly wage rate is $8. Manufacturing OH Budget Company expects variable costs to fluctuate with production volume on the basis of the following rates per direct labor hour: indirect materials $1.15, indirect labor $1.40, utilities $0.45, and maintenance $0.25. Supervisory salaries are $18,000 per quarter. Depreciation is $3,600 per quarter. Property taxes for the year are $44,100, paid evenly over each quarter. Flat maintenance fee of $4,500 is paid each quarter. Selling and Admin Expense Budget Variable expense rates per unit of sales are sales commissions $2 and freight-out $1. Variable expenses per quarter are based on the unit sales from the sales budget. Advertising expense for the year is $14,000 paid evenly each quarter. Sales salaries are $10,000 each quarter The office manager is paid a salary of $7,500 each quarter. Depreciation for office equipment is $1,400 each quarter. Property taxes are $5,000 for the year, paid evenly each quarter. Budgeted Income Statement Additional items: Interest expense is expected to be $150 Income taxes are estimated to be $10,000 Cash Budget Assumptions 1. The January 1, 2020, cash balance is expected to be $32,000. The Company wishes to maintain a balance of at least $15,000. 2. Sales: 60% are collected in the quarter sold and 40% are collected in the following quarter. Accounts receivable of $70,000 at December 31, 2019, are expected to be collected in full in the first quarter of 2020. 3. Short-term investments are expected to be sold for $2,500 cash in the first quarter. 4. Direct materials: 50% are paid in the quarter purchased and 50% are paid in the following quarter. Accounts payable of $11,700 at December 31, 2019, are expected to be paid in full in the first quarter of 2020. 5. Direct labor: 100% is paid in the quarter incurred. 6. Manufacturing overhead and selling and administrative expenses: All items except depreciation are paid in the quarter incurred. 7. Management plans to purchase equipment in the third quarter for $7,000 cash. 8. The Company makes equal quarterly payments of its estimated annual income taxes. 9. Loans are repaid in the earliest quarter in which there is sufficient cash (that is, when the cash on hand exceeds the $15,000 minimum required balance). Budgeted Balance Sheet Pertinent data from the budgeted balance sheet at December 31, 2019, are as follows. Buildings and equipment $182,000 Accumulated depreciation 28,800 Common stock 215,000 Retained earnings 65,670 A B C D E F G H K L M N o Q R s T U V w 1 My Company 2 Budgeted Income Statement 3 For the Year Ending December 31, 2020 4 5 Sales 6 Cost of good sold 7 Gross profit 8 Selling and administrative expense 9 Income from operations 10 Interest expense 11 Income before taxes 12 Income tax expense 13 Net income 14 15 16 17 18 19 20 21 22 23 24 25 Sales Budget Production Budget DM Budget DL Budget OH Budget Selling & Admin Budget Budgeted IS Sch of Exp Collections Sch... + A B D E G H L - M N o P Q R S U v 1 My Company 2 Schedule of expected collections 3 4 Quarter 5 Sales 1 IN mi 4 Year 6 A/R, 12/31/19 7 First quarter 8 Second quarter 9 Third quarter 10 Fourth quarter 11 Total collections 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Sales Budget Production Budget DM Budget DL Budget OH Budget Selling&Admin Budget Budgeted IS Sch of Exp Collections Sch... A B D E G H L - M N o P Q R S U v 1 My Company 2 Schedule of expected payments for DM 3 4 Quarter 5 Purchases 1 2 3 4 Year 6 A/P, 12/31/19 7 First quarter 8 Second quarter 9 Third quarter 10 Fourth quarter 11 Total payments 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 DM Budget DL Budget OH Budget Selling & Admin Budget Budgeted IS Sch of Exp Collections Sch of Exp payments DM Cash Budget + A B D E G H K L M o N P Q R s T U V w 1 My Company 2 Cash Budget 3 For the Year Ending December 31, 2020 4 5 Quarter 6 1 2 3 4 7 Beginning cash balance 8 Add Receipts 9 10 11 Total receipts 12 Total available cash 13 Less: Disbursements 14 15 16 17 18 19 OH Budget Selling & Admin Budget Budgeted IS Sch of Exp Collections Sch of Exp payments DM Cash Budget Budgeted BS A B D E G H K L M o N P Q R s T U V w 16 17 18 19 20 Total disbursements 21 Excess (deficiency) 22 Financing 23 Add: Borrowings 24 Less: Repayments (including interest) 25 Ending cash balance 26 27 28 29 30 31 32 33 34 85 36 37 38 39 40 41 42 43 OH Budget Selling&Admin Budget Budgeted is Sch of Exp Collections Sch of Exp payments DM Cash Budget Budgeted BS A B D E G H K L M o N P Q R s T U V w 1 My Company 2 Budgeted Balance Sheet 3 December 31, 2020 4 5 Assets 6 Current assets 7 Cash 8 Accounts receivable 9 Finished goods inventory 10 Raw materials inventory 11 Total current assets 12 Property, plant, and equipment 13 Buildings and equipment 14 Less: Accumulated depreciation 15 Total assets 16 Liabilities and Stockholders Equity 17 Liabilities 18 Accounts payable 19 Stockholders equity OH Budget Selling & Admin Budget Budgeted is Sch of Exp Collections Sch of Exp payments DM Cash Budget Budgeted BS 20 Common stock 21 Retained earnings 22 Total stockholders equity 23 Total liabilities and stockholders equity 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 OH Budget Selling&Admin Budget Budgeted IS Sch of Exp Collections Sch of Exp payments DM Cash Budget Budgeted BS + II Sales Budget Expected sales volume: 4,100 units in the first quarter with 500-unit increases in each succeeding quarter. Sales price: $65 per unit. Production Budget Company can meet future sales needs with an ending inventory of 20% of next quarter's budgeted sales volume. Direct Materials Budget Company maintains an ending inventory of raw materials equal to 10% of the next quarter's production requirements. The manufacture of each unit requires 2 pounds of raw materials, and the expected cost per pound is $4. Assume that the desired ending direct materials amount is 1,200 pounds for the fourth quarter of 2020. Direct Labor Budget Two hours of direct labor are required to produce each unit of finished goods. The anticipated hourly wage rate is $8. Manufacturing OH Budget Company expects variable costs to fluctuate with production volume on the basis of the following rates per direct labor hour: indirect materials $1.15, indirect labor $1.40, utilities $0.45, and maintenance $0.25. Supervisory salaries are $18,000 per quarter. Depreciation is $3,600 per quarter. Property taxes for the year are $44,100, paid evenly over each quarter. Flat maintenance fee of $4,500 is paid each quarter. Selling and Admin Expense Budget Variable expense rates per unit of sales are sales commissions $2 and freight-out $1. Variable expenses per quarter are based on the unit sales from the sales budget. Advertising expense for the year is $14,000 paid evenly each quarter. Sales salaries are $10,000 each quarter The office manager is paid a salary of $7,500 each quarter. Depreciation for office equipment is $1,400 each quarter. Property taxes are $5,000 for the year, paid evenly each quarter. Budgeted Income Statement Additional items: Interest expense is expected to be $150 Income taxes are estimated to be $10,000 Cash Budget Assumptions 1. The January 1, 2020, cash balance is expected to be $32,000. The Company wishes to maintain a balance of at least $15,000. 2. Sales: 60% are collected in the quarter sold and 40% are collected in the following quarter. Accounts receivable of $70,000 at December 31, 2019, are expected to be collected in full in the first quarter of 2020. 3. Short-term investments are expected to be sold for $2,500 cash in the first quarter. 4. Direct materials: 50% are paid in the quarter purchased and 50% are paid in the following quarter. Accounts payable of $11,700 at December 31, 2019, are expected to be paid in full in the first quarter of 2020. 5. Direct labor: 100% is paid in the quarter incurred. 6. Manufacturing overhead and selling and administrative expenses: All items except depreciation are paid in the quarter incurred. 7. Management plans to purchase equipment in the third quarter for $7,000 cash. 8. The Company makes equal quarterly payments of its estimated annual income taxes. 9. Loans are repaid in the earliest quarter in which there is sufficient cash (that is, when the cash on hand exceeds the $15,000 minimum required balance). Budgeted Balance Sheet Pertinent data from the budgeted balance sheet at December 31, 2019, are as follows. Buildings and equipment $182,000 Accumulated depreciation 28,800 Common stock 215,000 Retained earnings 65,670 A B C D E F G H K L M N o Q R s T U V w 1 My Company 2 Budgeted Income Statement 3 For the Year Ending December 31, 2020 4 5 Sales 6 Cost of good sold 7 Gross profit 8 Selling and administrative expense 9 Income from operations 10 Interest expense 11 Income before taxes 12 Income tax expense 13 Net income 14 15 16 17 18 19 20 21 22 23 24 25 Sales Budget Production Budget DM Budget DL Budget OH Budget Selling & Admin Budget Budgeted IS Sch of Exp Collections Sch... + A B D E G H L - M N o P Q R S U v 1 My Company 2 Schedule of expected collections 3 4 Quarter 5 Sales 1 IN mi 4 Year 6 A/R, 12/31/19 7 First quarter 8 Second quarter 9 Third quarter 10 Fourth quarter 11 Total collections 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Sales Budget Production Budget DM Budget DL Budget OH Budget Selling&Admin Budget Budgeted IS Sch of Exp Collections Sch... A B D E G H L - M N o P Q R S U v 1 My Company 2 Schedule of expected payments for DM 3 4 Quarter 5 Purchases 1 2 3 4 Year 6 A/P, 12/31/19 7 First quarter 8 Second quarter 9 Third quarter 10 Fourth quarter 11 Total payments 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 DM Budget DL Budget OH Budget Selling & Admin Budget Budgeted IS Sch of Exp Collections Sch of Exp payments DM Cash Budget + A B D E G H K L M o N P Q R s T U V w 1 My Company 2 Cash Budget 3 For the Year Ending December 31, 2020 4 5 Quarter 6 1 2 3 4 7 Beginning cash balance 8 Add Receipts 9 10 11 Total receipts 12 Total available cash 13 Less: Disbursements 14 15 16 17 18 19 OH Budget Selling & Admin Budget Budgeted IS Sch of Exp Collections Sch of Exp payments DM Cash Budget Budgeted BS A B D E G H K L M o N P Q R s T U V w 16 17 18 19 20 Total disbursements 21 Excess (deficiency) 22 Financing 23 Add: Borrowings 24 Less: Repayments (including interest) 25 Ending cash balance 26 27 28 29 30 31 32 33 34 85 36 37 38 39 40 41 42 43 OH Budget Selling&Admin Budget Budgeted is Sch of Exp Collections Sch of Exp payments DM Cash Budget Budgeted BS A B D E G H K L M o N P Q R s T U V w 1 My Company 2 Budgeted Balance Sheet 3 December 31, 2020 4 5 Assets 6 Current assets 7 Cash 8 Accounts receivable 9 Finished goods inventory 10 Raw materials inventory 11 Total current assets 12 Property, plant, and equipment 13 Buildings and equipment 14 Less: Accumulated depreciation 15 Total assets 16 Liabilities and Stockholders Equity 17 Liabilities 18 Accounts payable 19 Stockholders equity OH Budget Selling & Admin Budget Budgeted is Sch of Exp Collections Sch of Exp payments DM Cash Budget Budgeted BS 20 Common stock 21 Retained earnings 22 Total stockholders equity 23 Total liabilities and stockholders equity 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 OH Budget Selling&Admin Budget Budgeted IS Sch of Exp Collections Sch of Exp payments DM Cash Budget Budgeted BS + II