BUS105: Master Budgeting Case Instructions: Read the case below and complete the Master Budget Template in Excel. When you open the Master Budget Template, notice the first tab, Facts. When working with large Excel files, it is a best practice to link formulas to your fact pattern. This way, you can quickly evaluate how the entire budget would change if a certain variable were to change. For example, if you expect a price increase halfway through the year, you can see how your budget would change without having to remember all of the pieces of the budget that depend on that data. As you work through the Master Budget Template, remember to utilize the "Facts tab whenever possible. On the second tab, PY BS," the prior year balance sheet is included for your reference. After the prior year balance sheet, you will work through the remaining tabs in order. Do you recall the first step in creating the Master Budget? Notice the correlation to the next tab after the prior year balance sheet. The Excel workbook includes cues to indicate if you are completing the template correctly. Notice also that there is a legend on the Facts tab to identify cells that are input, linked, or calculation. Your Role You are the senior accountant for Sailor, Inc. The company manufactures toy sailboats. It is now September, and the Director of Finance has asked you to start preparing a quarterly budget for the coming year. Having taken Managerial Accounting, you know that you cannot prepare the budget by yourself, you will need help from several people in the company. Make a list of people you think you need to consult. Compare your list to the roles you find listed below. Manufacturing Cost Estimates As the current year, 20x0, has not closed yet, you decide to start by examining the projected year-end balance sheet (tab PY BS). You notice that the projected cash balance seems low and make a note to discuss ways to improve cash flow with the Production Manager and Controller. You also decide to evaluate the manufacturing cost components. CC BY The Saylor Foundation 1 Direct Materials Each toy sailboat consists of the following components: a wooden boat and two fabric sails. The wooden boat costs $2.00 and the sails cost $0.50 each. Direct Labor Direct labor is one-quarter hour, and workers are paid $8.25 per hour. Manufacturing Overhead You have evaluated manufacturing overhead and determined that variable overhead is $3.00 per unit and fixed overhead is $3,000 per quarter. Sales Budget The Marketing Manager is optimistic that demand for toy sailboats will continue to increase in the coming year. He predicts that his sales force will be able to sell 10,000 units in the first quarter, 12,000 units in the second quarter, 15,000 units in the third quarter, and 13,000 units in the fourth quarter. You notice that his projections are consistent with the increase in sales during the summer months. The sales price for a sailboat will remain the same at $25.00 per unit. Excel Tip: Notice that you only have to input the Projected Unit Sales in the Sales Budget. Link Sales Price to the Facts tab and calculate Projected Revenue. Production Budgets You remember that you needed to address the cash flow issue with the Production Manager. After reviewing past performance, she agrees that one way to reduce the cash outflow is to reduce the amount of inventory carried at any given point in time. You propose a just-in-time inventory system; however, she is hesitant due to the inconsistency of the company's vendors. Sometimes shipments arrive in three days; sometimes it takes a week. The two of you agree that she will work with vendors regarding consistent shipping. In the meantime, you aim to have enough raw material ending inventory at the end of the quarter to cover production for the following month. The Production Manager would like to have finished goods inventory of 20% of the next quarter's projected unit sales. Income Statement You meet with the Controller to evaluate operating expenses. Based on historical amounts, you expect variable operating expenses to be $4.00 per unit sold. Fixed expenses pertain primarily to salaries of officers and rent, and are expected to be $75,000 per quarter. Fixed expenses include $8,000 per quarter for depreciation. The company's tax rate is 25%. Income taxes are paid the quarter after they are incurred. Notes Payable You also discuss the notes payable with the Controller. The company owes its bank $36,000. The note will be repaid in four equal principal installments, one at the end of each quarter. The bank is charging 5% annual interest. Each payment will include the interest accrued during that period. Accounts Receivable Your Controller tells you that most of your customers purchase on credit. As a result, of the company's current quarter sales, 65% is usually collected in the same quarter and 35% is collected the following quarter. Accounts Payable Accounts payable represents amounts owed for direct materials. Direct materials are purchased one quarter in advance of when they are needed. Payments for direct materials purchases occur one quarter after purchase. Q1 Actual Results At the end of the first quarter, your company reports actual results. These are shown on the Flexible Budgets tab. Prepare the flexible budget for Q1 to evaluate why actual results differed from the budget. How much of the variance was due to production? BUS105-Unit 7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to do C7 fx A B D E F H J K L 1 Sailor, Inc. 2 Facts Input Cell Linked Cell Calculation 3 4 5 Variable costs per unit manufactured: 6 Direct materials 7 Wooden boat boat at each 8 Fabric sail sails at each 9 Direct labor hour at per hour 10 Variable manfacturing overhead per unit 11 Fixed manufacturing overhead per quarter 12 13 Sales price 14 15 Projected ending inventory units following quarter's sales 16 17 Operating expenses 18 Variable per unit sold 19 Fixed per quarter 20 21 Depreciation per quarter 22 23 Income tax rate 24 25 26 27 Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget: ... + BUS105-Unit 7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to d C2 B c D E F G H J 1 Sailor, Inc. 2 Prior Year End Projected Balance Sheet 3 4 20x0 5 Assets 6 Current assets: 7 Cash $ 50,000 8 Accounts receivable 107,000 9 Inventory: 10 Direct materials (5,000 units) 12,500 11 Finished goods (5,000 units) 60,000 12 Total current assets 229,500 13 Buildings and equipment 420,000 14 Less: Accumulated depreciation (20,000) 15 Total assets $ 629,500 16 17 Liabilities and Owners' Equity 18 Current liabilities: 19 Accounts payable $ 25,000 20 Notes payable 36,000 21 Income taxes payable 25,000 22 Total current liabilities 86,000 23 24 Owners' equity: 25 Capital stock 400,000 26 Retained earnings 143,500 27 Net income Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget: ... 28 Total owners' equity 543,500 29 Total liabilities and owners' equity $ 629,500 30 31 Check B5 B D E F I J 1 Sailor, Inc. 2 Sales Budget 3 4 5 Projected unit sales 6 Sales price 7 Projected revenue Q1 Q2 03 Q4 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget: ... BUS105-Unit7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to do G2 : fax A B D E F G H J K Next year 2,600 2,600 2,600 1 Sailor, Inc. 2 Production Budgets 3 4 Units of Production Q1 Q2 Q3 Q4 5 Projected unit sales 6 Add: Ending inventory 7 Units available for sale 8 Less: Beginning inventory 2,400 9 Budgeted production (2,400) 10 Try Again Try Again Try Again Try Again 11 Direct Materials Purchases Budget 12 Boats 13 Boats needed for production 14 Add: Ending inventory 15 Boats available for production 16 Less: Beginning inventory 17 Boats to purchase 19 Budgeted cost of boats $ $ $ $ 20 Try Again Try Again Try Again Try Again 21 Sails 22 Sails needed for production 14,800 23 Add: Ending inventory 24 Sails available for production 14,800 25 Less: Beginning inventory 26 Sails to purchase 14,800 Try Again Try Again Try Again Try Again 14,400 $ 14,400 $ $ Try Again Try Again Try Again $ Try Again 28 Budgeted cost of sails 29 Total budgeted cost of materials 30 31 32 Direct Materials Used Budget 33 Beginning inventory 34 Purchases 35 Less: Ending inventory boats 36 Less: Ending inventory sails 37 Direct materials used 38 39 40 Direct Labor Budget 41 Budgeted production 42 Total labor hours needed 44 Budgeted direct labor cost 45 46 Manufacturing Overhead 47 Budgeted production 49 Variable overhead cost 50 Fixed overhead cost 51 Total overhead cost 52 Try Again Try Again Try Again Try Again $ $ $ $ Try Again Try Again Try Again Try Again 53 54 Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to do A21 X f Cost of ending FG inventory A B D E F H I j K L M N O Q3 Q4 Hint: Complete this schedule up until this point, then work on Finished Goods Inventory below. 1 Sailor, Inc. 2 Budgeted Cost of Goods Manufactured and Sold 3 4 Q1 Q2 5 Finished goods, beginning inventory 6 Add: Cost of goods manufactured 7 Direct materials used 19,700 8 Direct labor used 9 Variable overhead 10 Fixed overhead 11 Total cost of goods manufactured 19,700 12 Total costs of goods available for sale 19,700 13 Less: Ending finished goods inventory $ 14 Cost goods sold $ 19,700 $ 15 Try Again Try Again 16 Finished Goods Inventory 17 Cost of goods manufactured 18 Divide by: Units of production 19 Production cost per unit 20 Multiply by: Ending inventory units 21 Cost of ending FG inventory 22 Try Again Try Again 23 $ $ Try Again $ $ Try Again Try Again Try Again 24 25 26 Facts PY BS Sales Production COGS Opex NP IS Cash BS Flexible Budget: ... BUS105-Unit 7-MasterBudgetCase-Template File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you B5 fi B D E F G H J A 1 Sailor, Inc. 2 Operating Expense Budget 3 4 5 Variable opex 6 Fixed opex 7 Total opex $ Q1 Q2 Q3 Q4 $ $ $ $ 00 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget: ... 5 BUS105-Unit7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to do B1 fi B C D E F G H 1 Sailor, Inc. 2 Notes Payable 3 4 NP-Bank 5 Principal 6 Interest rate 7 00 Principal Payment Cash Payment Interest Balance 9 10 Beg 11 Q1 $ 450 12 Q2 13 Q3 Try Again 14 Q4 15 16 17 18 19 20 21 22 23 24 25 26 Facts PY BS Sales Production COGS Opex NP IS Cash BS Flexible Budget: ... 5 BUS105-Unit7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to di A13 for Net income (loss) B D E F H Q1 Q2 Q3 Q4 99,198 (99,198) 1 Sailor, Inc. 2 Budgeted Income Statement 3 4 5 Revenue 6 Cost of goods sold 7 Gross profit 8 Operating expenses 9 Operating income 10 Interest expense 11 Income before taxes 12 Tax expense benefit) 13 Net income (loss) 14 (99,198) (99,198) $ $ Try Again $ (99,198) $ Try Again Try Again $ Try Again 15 16 17 18 19 20 21 22 23 24 25 26 27 Facts PY BS Sales Production COGS Opex NP IS Cash BS Flexible Budget: ... BUS105-Unit7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to do A25 Total cash outflows A B D E F G H 4 Q1 Q2 Q3 Q4 Next year 5 Cash inflows 6 Sales 00 65% $ 35% $ $ $ 8 Beg bal A/R 9 Quarter 10 Quarter following 11 Total cash inflows 12 13 Cash outflows 14 Direct materials purchased 15 Payments for direct materials 16 162,500 $ Try Again Try Again Try Again Try Again 17 $ $ $ $ 9,338 18 Direct materials 19 Direct labor 20 Mfg overhead 21 Debt service 22 Income taxes 23 Operating expenses 24 Less: Depreciation 25 Total cash outflows 26 Net change in cash 27 Beginning balance cash 28 Ending balance cash 29 $ $ $ 162,500 $ 9,338 $ (9,338) $ $ $ $ $ 162,500 $ Try Again (9,338) $ Try Again Try Again Try Again 30 Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget ... B Sos BUS105-Unit 7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to G14 E F G H 8 9 A B C D 4 Q1 Q2 Q3 Q4 5 Assets 6 Current assets: 7 Cash $ 257,734 Accounts receivable Inventory: 10 Direct materials 11 Finished goods 12 Total current assets 257,734 13 Buildings and equipment 14 Less: Accumulated depreciation (28,000) 15 Total assets $ (28,000) $ $ 257,734 $ 16 Try Again Try Again Try Again Try Again 17 Liabilities and Owners' Equity 18 Current liabilities: 19 Accounts payable 20 Notes payable 21 Income taxes payable 22 Total current liabilities 23 24 Owners' equity: 25 Capital stock 26 Retained earnings 227,023 27 Net income 28 Total owners' equity 227,023 29 Total liabilities and owners' equity $ $ $ 227,023 $ Facts PYBS Sales Production COGS Opex NP 15 Cash PY BS Flexible Budget: ... BUS105-Unit7-MasterBudgetCase-Template - Exc File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want G14 A B D F G H E Q4 Q1 Q2 Q3 4 17 Liabilities and Owners' Equity 18 Current liabilities: 19 Accounts payable 20 Notes payable 21 Income taxes payable 22 Total current liabilities 23 24 Owners' equity: 25 Capital stock 26 Retained earnings 27 Net income 28 Total owners' equity 29 Total liabilities and owners' equity $ 30 31 Check 32 227,023 227,023 227,023 $ $ $ (28,000) 30,711 33 34 35 36 37 38 39 40 41 42 Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget: 5. BUS105-Unit7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to F8 fi A F G H j B D E Q1 Budget 21 Actual Variance % Variance 4 5 5 Manufacturing costs: 6 Direct materials 7 Direct labor 8 Variable mfg OH 9 Fixed mfg OH 10 Total manufacturing costs 11 Units 12 $ 21,000 16,250 22,200 21,000 3,200 $ 22,200 $ 61,450 7,250 Volume Q1 Budget Variance % Variance Flexible Q1 Flexible Budget Budget Variance % Variance Q1 Actual 13 $ 21,750 14 Manufacturing costs: 15 Direct materials 16 Direct labor 17 Variable mfg OH 18 Fixed mfg OH 19 Total manufacturing costs 20 Units 21 22 23 $ $ 21,750 $ 24 25 26 27 PY BS Sales Production COGS Opex NP is Cash BS Flexible Budgets BUS105: Master Budgeting Case Instructions: Read the case below and complete the Master Budget Template in Excel. When you open the Master Budget Template, notice the first tab, Facts. When working with large Excel files, it is a best practice to link formulas to your fact pattern. This way, you can quickly evaluate how the entire budget would change if a certain variable were to change. For example, if you expect a price increase halfway through the year, you can see how your budget would change without having to remember all of the pieces of the budget that depend on that data. As you work through the Master Budget Template, remember to utilize the "Facts tab whenever possible. On the second tab, PY BS," the prior year balance sheet is included for your reference. After the prior year balance sheet, you will work through the remaining tabs in order. Do you recall the first step in creating the Master Budget? Notice the correlation to the next tab after the prior year balance sheet. The Excel workbook includes cues to indicate if you are completing the template correctly. Notice also that there is a legend on the Facts tab to identify cells that are input, linked, or calculation. Your Role You are the senior accountant for Sailor, Inc. The company manufactures toy sailboats. It is now September, and the Director of Finance has asked you to start preparing a quarterly budget for the coming year. Having taken Managerial Accounting, you know that you cannot prepare the budget by yourself, you will need help from several people in the company. Make a list of people you think you need to consult. Compare your list to the roles you find listed below. Manufacturing Cost Estimates As the current year, 20x0, has not closed yet, you decide to start by examining the projected year-end balance sheet (tab PY BS). You notice that the projected cash balance seems low and make a note to discuss ways to improve cash flow with the Production Manager and Controller. You also decide to evaluate the manufacturing cost components. CC BY The Saylor Foundation 1 Direct Materials Each toy sailboat consists of the following components: a wooden boat and two fabric sails. The wooden boat costs $2.00 and the sails cost $0.50 each. Direct Labor Direct labor is one-quarter hour, and workers are paid $8.25 per hour. Manufacturing Overhead You have evaluated manufacturing overhead and determined that variable overhead is $3.00 per unit and fixed overhead is $3,000 per quarter. Sales Budget The Marketing Manager is optimistic that demand for toy sailboats will continue to increase in the coming year. He predicts that his sales force will be able to sell 10,000 units in the first quarter, 12,000 units in the second quarter, 15,000 units in the third quarter, and 13,000 units in the fourth quarter. You notice that his projections are consistent with the increase in sales during the summer months. The sales price for a sailboat will remain the same at $25.00 per unit. Excel Tip: Notice that you only have to input the Projected Unit Sales in the Sales Budget. Link Sales Price to the Facts tab and calculate Projected Revenue. Production Budgets You remember that you needed to address the cash flow issue with the Production Manager. After reviewing past performance, she agrees that one way to reduce the cash outflow is to reduce the amount of inventory carried at any given point in time. You propose a just-in-time inventory system; however, she is hesitant due to the inconsistency of the company's vendors. Sometimes shipments arrive in three days; sometimes it takes a week. The two of you agree that she will work with vendors regarding consistent shipping. In the meantime, you aim to have enough raw material ending inventory at the end of the quarter to cover production for the following month. The Production Manager would like to have finished goods inventory of 20% of the next quarter's projected unit sales. Income Statement You meet with the Controller to evaluate operating expenses. Based on historical amounts, you expect variable operating expenses to be $4.00 per unit sold. Fixed expenses pertain primarily to salaries of officers and rent, and are expected to be $75,000 per quarter. Fixed expenses include $8,000 per quarter for depreciation. The company's tax rate is 25%. Income taxes are paid the quarter after they are incurred. Notes Payable You also discuss the notes payable with the Controller. The company owes its bank $36,000. The note will be repaid in four equal principal installments, one at the end of each quarter. The bank is charging 5% annual interest. Each payment will include the interest accrued during that period. Accounts Receivable Your Controller tells you that most of your customers purchase on credit. As a result, of the company's current quarter sales, 65% is usually collected in the same quarter and 35% is collected the following quarter. Accounts Payable Accounts payable represents amounts owed for direct materials. Direct materials are purchased one quarter in advance of when they are needed. Payments for direct materials purchases occur one quarter after purchase. Q1 Actual Results At the end of the first quarter, your company reports actual results. These are shown on the Flexible Budgets tab. Prepare the flexible budget for Q1 to evaluate why actual results differed from the budget. How much of the variance was due to production? BUS105-Unit 7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to do C7 fx A B D E F H J K L 1 Sailor, Inc. 2 Facts Input Cell Linked Cell Calculation 3 4 5 Variable costs per unit manufactured: 6 Direct materials 7 Wooden boat boat at each 8 Fabric sail sails at each 9 Direct labor hour at per hour 10 Variable manfacturing overhead per unit 11 Fixed manufacturing overhead per quarter 12 13 Sales price 14 15 Projected ending inventory units following quarter's sales 16 17 Operating expenses 18 Variable per unit sold 19 Fixed per quarter 20 21 Depreciation per quarter 22 23 Income tax rate 24 25 26 27 Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget: ... + BUS105-Unit 7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to d C2 B c D E F G H J 1 Sailor, Inc. 2 Prior Year End Projected Balance Sheet 3 4 20x0 5 Assets 6 Current assets: 7 Cash $ 50,000 8 Accounts receivable 107,000 9 Inventory: 10 Direct materials (5,000 units) 12,500 11 Finished goods (5,000 units) 60,000 12 Total current assets 229,500 13 Buildings and equipment 420,000 14 Less: Accumulated depreciation (20,000) 15 Total assets $ 629,500 16 17 Liabilities and Owners' Equity 18 Current liabilities: 19 Accounts payable $ 25,000 20 Notes payable 36,000 21 Income taxes payable 25,000 22 Total current liabilities 86,000 23 24 Owners' equity: 25 Capital stock 400,000 26 Retained earnings 143,500 27 Net income Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget: ... 28 Total owners' equity 543,500 29 Total liabilities and owners' equity $ 629,500 30 31 Check B5 B D E F I J 1 Sailor, Inc. 2 Sales Budget 3 4 5 Projected unit sales 6 Sales price 7 Projected revenue Q1 Q2 03 Q4 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget: ... BUS105-Unit7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to do G2 : fax A B D E F G H J K Next year 2,600 2,600 2,600 1 Sailor, Inc. 2 Production Budgets 3 4 Units of Production Q1 Q2 Q3 Q4 5 Projected unit sales 6 Add: Ending inventory 7 Units available for sale 8 Less: Beginning inventory 2,400 9 Budgeted production (2,400) 10 Try Again Try Again Try Again Try Again 11 Direct Materials Purchases Budget 12 Boats 13 Boats needed for production 14 Add: Ending inventory 15 Boats available for production 16 Less: Beginning inventory 17 Boats to purchase 19 Budgeted cost of boats $ $ $ $ 20 Try Again Try Again Try Again Try Again 21 Sails 22 Sails needed for production 14,800 23 Add: Ending inventory 24 Sails available for production 14,800 25 Less: Beginning inventory 26 Sails to purchase 14,800 Try Again Try Again Try Again Try Again 14,400 $ 14,400 $ $ Try Again Try Again Try Again $ Try Again 28 Budgeted cost of sails 29 Total budgeted cost of materials 30 31 32 Direct Materials Used Budget 33 Beginning inventory 34 Purchases 35 Less: Ending inventory boats 36 Less: Ending inventory sails 37 Direct materials used 38 39 40 Direct Labor Budget 41 Budgeted production 42 Total labor hours needed 44 Budgeted direct labor cost 45 46 Manufacturing Overhead 47 Budgeted production 49 Variable overhead cost 50 Fixed overhead cost 51 Total overhead cost 52 Try Again Try Again Try Again Try Again $ $ $ $ Try Again Try Again Try Again Try Again 53 54 Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to do A21 X f Cost of ending FG inventory A B D E F H I j K L M N O Q3 Q4 Hint: Complete this schedule up until this point, then work on Finished Goods Inventory below. 1 Sailor, Inc. 2 Budgeted Cost of Goods Manufactured and Sold 3 4 Q1 Q2 5 Finished goods, beginning inventory 6 Add: Cost of goods manufactured 7 Direct materials used 19,700 8 Direct labor used 9 Variable overhead 10 Fixed overhead 11 Total cost of goods manufactured 19,700 12 Total costs of goods available for sale 19,700 13 Less: Ending finished goods inventory $ 14 Cost goods sold $ 19,700 $ 15 Try Again Try Again 16 Finished Goods Inventory 17 Cost of goods manufactured 18 Divide by: Units of production 19 Production cost per unit 20 Multiply by: Ending inventory units 21 Cost of ending FG inventory 22 Try Again Try Again 23 $ $ Try Again $ $ Try Again Try Again Try Again 24 25 26 Facts PY BS Sales Production COGS Opex NP IS Cash BS Flexible Budget: ... BUS105-Unit 7-MasterBudgetCase-Template File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you B5 fi B D E F G H J A 1 Sailor, Inc. 2 Operating Expense Budget 3 4 5 Variable opex 6 Fixed opex 7 Total opex $ Q1 Q2 Q3 Q4 $ $ $ $ 00 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget: ... 5 BUS105-Unit7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to do B1 fi B C D E F G H 1 Sailor, Inc. 2 Notes Payable 3 4 NP-Bank 5 Principal 6 Interest rate 7 00 Principal Payment Cash Payment Interest Balance 9 10 Beg 11 Q1 $ 450 12 Q2 13 Q3 Try Again 14 Q4 15 16 17 18 19 20 21 22 23 24 25 26 Facts PY BS Sales Production COGS Opex NP IS Cash BS Flexible Budget: ... 5 BUS105-Unit7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to di A13 for Net income (loss) B D E F H Q1 Q2 Q3 Q4 99,198 (99,198) 1 Sailor, Inc. 2 Budgeted Income Statement 3 4 5 Revenue 6 Cost of goods sold 7 Gross profit 8 Operating expenses 9 Operating income 10 Interest expense 11 Income before taxes 12 Tax expense benefit) 13 Net income (loss) 14 (99,198) (99,198) $ $ Try Again $ (99,198) $ Try Again Try Again $ Try Again 15 16 17 18 19 20 21 22 23 24 25 26 27 Facts PY BS Sales Production COGS Opex NP IS Cash BS Flexible Budget: ... BUS105-Unit7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to do A25 Total cash outflows A B D E F G H 4 Q1 Q2 Q3 Q4 Next year 5 Cash inflows 6 Sales 00 65% $ 35% $ $ $ 8 Beg bal A/R 9 Quarter 10 Quarter following 11 Total cash inflows 12 13 Cash outflows 14 Direct materials purchased 15 Payments for direct materials 16 162,500 $ Try Again Try Again Try Again Try Again 17 $ $ $ $ 9,338 18 Direct materials 19 Direct labor 20 Mfg overhead 21 Debt service 22 Income taxes 23 Operating expenses 24 Less: Depreciation 25 Total cash outflows 26 Net change in cash 27 Beginning balance cash 28 Ending balance cash 29 $ $ $ 162,500 $ 9,338 $ (9,338) $ $ $ $ $ 162,500 $ Try Again (9,338) $ Try Again Try Again Try Again 30 Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget ... B Sos BUS105-Unit 7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to G14 E F G H 8 9 A B C D 4 Q1 Q2 Q3 Q4 5 Assets 6 Current assets: 7 Cash $ 257,734 Accounts receivable Inventory: 10 Direct materials 11 Finished goods 12 Total current assets 257,734 13 Buildings and equipment 14 Less: Accumulated depreciation (28,000) 15 Total assets $ (28,000) $ $ 257,734 $ 16 Try Again Try Again Try Again Try Again 17 Liabilities and Owners' Equity 18 Current liabilities: 19 Accounts payable 20 Notes payable 21 Income taxes payable 22 Total current liabilities 23 24 Owners' equity: 25 Capital stock 26 Retained earnings 227,023 27 Net income 28 Total owners' equity 227,023 29 Total liabilities and owners' equity $ $ $ 227,023 $ Facts PYBS Sales Production COGS Opex NP 15 Cash PY BS Flexible Budget: ... BUS105-Unit7-MasterBudgetCase-Template - Exc File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want G14 A B D F G H E Q4 Q1 Q2 Q3 4 17 Liabilities and Owners' Equity 18 Current liabilities: 19 Accounts payable 20 Notes payable 21 Income taxes payable 22 Total current liabilities 23 24 Owners' equity: 25 Capital stock 26 Retained earnings 27 Net income 28 Total owners' equity 29 Total liabilities and owners' equity $ 30 31 Check 32 227,023 227,023 227,023 $ $ $ (28,000) 30,711 33 34 35 36 37 38 39 40 41 42 Facts PY BS Sales Production COGS Opex NP is Cash BS Flexible Budget: 5. BUS105-Unit7-MasterBudgetCase-Template - Excel File Home Insert Page Layout Formulas Data Review View Help QuickBooks Tell me what you want to F8 fi A F G H j B D E Q1 Budget 21 Actual Variance % Variance 4 5 5 Manufacturing costs: 6 Direct materials 7 Direct labor 8 Variable mfg OH 9 Fixed mfg OH 10 Total manufacturing costs 11 Units 12 $ 21,000 16,250 22,200 21,000 3,200 $ 22,200 $ 61,450 7,250 Volume Q1 Budget Variance % Variance Flexible Q1 Flexible Budget Budget Variance % Variance Q1 Actual 13 $ 21,750 14 Manufacturing costs: 15 Direct materials 16 Direct labor 17 Variable mfg OH 18 Fixed mfg OH 19 Total manufacturing costs 20 Units 21 22 23 $ $ 21,750 $ 24 25 26 27 PY BS Sales Production COGS Opex NP is Cash BS Flexible Budgets