Cash budget, income statement and performance report using a flexible budget. Harry Kohl owns a factory that specializes in making steel widgets on a just in time basis. Harry buys his steel only after he receives a firm order. Thus, Harry only buys materials for and produces what he sells in a particular month. To build his business and gain new customers Harry has extended generous credit terms to his customers. While Harry is confident about the fundamentals of his business, he is concemed about the possible cash flow implications. HARRY KOHE Cash Budget for the 3 months April, May, and June April May June Quarter 2 Beginning Cash Balance $ 25,000 $ 23,000 $ 5,000 $ 25,000 Add: Collections from customers (See Note 1) $ 41,80,000 $ 44,60,000 $ 48,50,000 $1,34,90,000 Total Cash Available $ 42,05,000 $ 44,83,000 $ 48,55,000 $1,35,15,000 Cash Disbursements: Cash disbursement for purchase (See Note 2) $ 14,22,000 $ 15,68,000 $ 16,64,000 $ 46,54,000 Cash paid for labor cost ($5 per unit) $ 9,00,000 $ 10,00,000 $ 10,50,000 $ 29,50,000 Cash paid for variable overhead ($7 per unit) $ 12,60,000 $ 14,00,000 $ 14,70,000 $ 41,30,000 Cash paid for fixed overhead (excluding dep) $ 3,10,000$ 3,10,000 $ $ 3,10,000 $9,30,000 Cash paid for fixed selling & admin costs (excl. Dep) $ 2,90,000 $2,90,000 $ 2,90,000 $8,70,000 Total Cash disbusement $ 41,82,000 $ 45,68,000 $ 47,84,000 $1,35,34,000 $ 23,000 $ $ Cash Flow before financing Borrowings (Repayments) Interest on borrowings Ending Cash balance -85,000 $ 90,000 s $ 5,000 $ 71,000 s -65,000 $ -900 $ 5,100 $ -19,000 25,000 -900 $ 23,000 $ 5,100 Workings Harry informs you that he currently expects to collect 20% of sales in the month of the sale, 40% of sales in the month after the sale and 40% of sales two months later (for example 20% of February sales are collected in February, 40% in March and 40% in April). He pays for 80% of his materials purchases in the month of the purchase and 20% one month later. Costs of labor and overhead other than depreciation are paid in the same month they are incurred. His monthly fixed selling and administrative costs, other than interest, amount to $320,000, of which $30,000 is depreciation. These costs also, excepting depreciation, are paid in the month incurred. There are no variable selling or administrative costs. Harry has large tax loss carry forwards from a previous unsuccessful business venture. Therefore he does not expect to pay any income taxes this year. (In other words you may ignore income taxes). Sales revenues in February were $4.050,000 and in March were $4.150,000 (these numbers do not agree with the excel sheet). Purchases in March were $1,350,000. The budgeted selling price of widgets for April, May, and June is $25 per widget. Harry provides the following information regarding his sales in units for the next 3 months: April 180,000 units, May 200,000 units and June 210,000 units. The variable costs of producing a widget are budgeted at: $8 for materials, $5 for labor, and $7 for variable overhead. Fixed overhead is budgeted at $380,000 per month. The detailed components of fixed and variable overhead are as listed below. For variable overhead, supplies are budgeted at $1 per unit, electric power is budgeted at S4 per unit, and indirect labor is budgeted at $2 per unit. For fixed overhead depreciation is budgeted at $70.000 per month. Supervision and other salaries is budgeted at $180,000 per month, insurance is budgeted at $12,000 per month, maintenance is budgeted at $23,000 per month, licensing fees and permits to use proprietary technology are budgeted at $64,000 per month, and other miscellaneous fixed expenses are budgeted at $31,000 per month Assume Harry's opening cash balance on April 1 is $25.000. Harry requires a minimum cash balance of $5,000 at the end of each month. If the budgeted cash balance will fall below this level Harry plans to borrow enough cash at the beginning of the month to keep his ending balance up to the minimum level. Harry's bank charges him interest at the rate of 1% per month (12% per year) on the balance outstanding during that month. Harry pays the interest at the beginning of the following month and plans to repay as much as he can without letting his cash balance go below $5,000. Assume no borrowings were outstanding as of April 1. HARRY KOHL Cash collection Schedule for the 3 months April, May, and June February March April May June $ 40,50,000 $ 41,50,000 $ 45,00,000 $ 50,00,000 $ 52,50,000 Expected Sales Revenue Cash collection from customers: In the month of sales (20%) In the first month of sales (40%) In the second month of sales (40%) Total Cash collection from customers $ 8,10,000 $ 8,30,000$ 9,00,000 8,30,000$ 9,00,000 $ 10,00,000 $ 10,50,000 $ 16,20,000 S 16,60,000 $ 18,00,000 $ 20,00,000 $ 16,20,000 $ 16,60,000 $ 18,00,000 $ 41,80,000 $ 44,60,000 $48,50,000 HARRY KOHL Schedule of Cash disbursement for purchase for the 3 months April, May, and June March April May June Expected Purchase of Material $ 13,50,000 $ 14,40,000 $ 16,00,000 $ 16,80,000 Write a brief report explaining the most important reasons why Harry's profits were different from the amount projected in the master budget. Cash disbursement for purchase: In the month of purchases (80%) In the first month of purchases (20%) Total Cash disbursement for purchase $ 10,80,000 $ 11,52,000 $ 12,80,000 $ 13,44,000 $ 2,70,000 $2,88,000 $ 3,20,000 $ 14,22,000 $ 15,68,000 $ 16,64,000 HARRY KOHL Performance Report for April Flexible Flexible Budget Actual Results Budget for Variance 175,000 units Cost Items Static Master Budget for 180,000 units Sales Volume Variance HARRY KOHL Budgeted Income Statement (Contribution Margin) for the 3 months April, May, and June April May June Quarter 2 Sales Revenue $ 45,00,000 $ 50,00,000 $ 52,50,000 $1,47,50,000 Less: Variable Costs Materials $ 14,40,000 $ 16,00,000 $16,80,000 $ 47,20,000 Labor $ 9,00,000 $ 10,00,000 $ 10,50,000 $ 29,50,000 Variable Overhead $ 12,60,000 $ 14,00,000 $ 14,70,000 $ 41,30,000 Total Variable costs $ 36,00,000 $ 40,00,000 $ 42,00,000 $1,18,00,000 $ 9,00,000 $ 10,00,000 $ 10,50,000 $ 29,50,000 Contribution Margin Less: Fixed Costs Fixed Overhead Fixed Selling & Admin costs Total Fixed costs Sales Revenue Direct Materials Direct Labor Supplies Electric Power Indirect Labor Salaries & Supervision Maintenance Insurance Permits and license fees Factory depreciation Other Overhead exp Total production expense Total Selling & Admin Exp Total Expenses Operating Income S43,42,000 S 43,75,000 S $ 15,15,625 $ 14,00,000 $ $ 8,81,436 $ 8,75,000 $ $ 1,66,288$ 1,75,000 $ $ 7,01,354 S 7,00,000 $ $ 3,39,272 S 3,50,000 $ $ 1,75,549 $ 1,80,000 $ $ 37,336 $ 23,000 $ $ 12,000 $ 12,000 $ S 68,000 $ 64,000 $ $ 70,000 $ 70,000 $ $ 33,173 $ 31,000 $ $ 40,00,033 $ 38,80,000 $ 3,29,453 $ 3,20,000 $ $ 43,29,486 S 42,00,000 $ $ 12,514 $ 1,75,000 $ 33,000 U $ 45,00,000 $ 1,15,625 U $ 14,40,000 $ 6,436U $ 9,00,000 $ 8,712 $ 1,80,000 $ 1,354 U $ 7,20,000 $ 10,728 F $ 3,60,000 $ 4,451 F $ 1,80,000 $ 14,330 U $ 23,000 $ Nonel s 12,000 $ 4,000 U $ 64,000 $ Nonel s 70,000 $ 2,173 U $ 31,000 $ 1,20,033 U $ 39,80,000 $ 9,453U $ 3,20,000 $ 1,29,486 U $ 43,00,000 $ 1,62,486 U $ 2,00,000 $ 1,58,000 V 75,625 JU 18,564 F 13,712F 18,646 F 20,728 F 4,451 F 14,336 U - None 4,000 U None 2,173 v 20,033 0 9,453 U 29,486 V 1,87,486 U $ 3,80,000 $ 3,80,000$ 3,80,000 $ 11,40,000 $ 3,20,000 $3,20,900 $ 3,20,900 $ 9,61,800 $ 7,00,000 $ 7,00,900 $ 7,00,900 $ 21,01,800 Net Income $ 2,00,000 $ 2,99,100 $ 3,49,100 $ 8,48,200 S Cash budget, income statement and performance report using a flexible budget. Harry Kohl owns a factory that specializes in making steel widgets on a just in time basis. Harry buys his steel only after he receives a firm order. Thus, Harry only buys materials for and produces what he sells in a particular month. To build his business and gain new customers Harry has extended generous credit terms to his customers. While Harry is confident about the fundamentals of his business, he is concemed about the possible cash flow implications. HARRY KOHE Cash Budget for the 3 months April, May, and June April May June Quarter 2 Beginning Cash Balance $ 25,000 $ 23,000 $ 5,000 $ 25,000 Add: Collections from customers (See Note 1) $ 41,80,000 $ 44,60,000 $ 48,50,000 $1,34,90,000 Total Cash Available $ 42,05,000 $ 44,83,000 $ 48,55,000 $1,35,15,000 Cash Disbursements: Cash disbursement for purchase (See Note 2) $ 14,22,000 $ 15,68,000 $ 16,64,000 $ 46,54,000 Cash paid for labor cost ($5 per unit) $ 9,00,000 $ 10,00,000 $ 10,50,000 $ 29,50,000 Cash paid for variable overhead ($7 per unit) $ 12,60,000 $ 14,00,000 $ 14,70,000 $ 41,30,000 Cash paid for fixed overhead (excluding dep) $ 3,10,000$ 3,10,000 $ $ 3,10,000 $9,30,000 Cash paid for fixed selling & admin costs (excl. Dep) $ 2,90,000 $2,90,000 $ 2,90,000 $8,70,000 Total Cash disbusement $ 41,82,000 $ 45,68,000 $ 47,84,000 $1,35,34,000 $ 23,000 $ $ Cash Flow before financing Borrowings (Repayments) Interest on borrowings Ending Cash balance -85,000 $ 90,000 s $ 5,000 $ 71,000 s -65,000 $ -900 $ 5,100 $ -19,000 25,000 -900 $ 23,000 $ 5,100 Workings Harry informs you that he currently expects to collect 20% of sales in the month of the sale, 40% of sales in the month after the sale and 40% of sales two months later (for example 20% of February sales are collected in February, 40% in March and 40% in April). He pays for 80% of his materials purchases in the month of the purchase and 20% one month later. Costs of labor and overhead other than depreciation are paid in the same month they are incurred. His monthly fixed selling and administrative costs, other than interest, amount to $320,000, of which $30,000 is depreciation. These costs also, excepting depreciation, are paid in the month incurred. There are no variable selling or administrative costs. Harry has large tax loss carry forwards from a previous unsuccessful business venture. Therefore he does not expect to pay any income taxes this year. (In other words you may ignore income taxes). Sales revenues in February were $4.050,000 and in March were $4.150,000 (these numbers do not agree with the excel sheet). Purchases in March were $1,350,000. The budgeted selling price of widgets for April, May, and June is $25 per widget. Harry provides the following information regarding his sales in units for the next 3 months: April 180,000 units, May 200,000 units and June 210,000 units. The variable costs of producing a widget are budgeted at: $8 for materials, $5 for labor, and $7 for variable overhead. Fixed overhead is budgeted at $380,000 per month. The detailed components of fixed and variable overhead are as listed below. For variable overhead, supplies are budgeted at $1 per unit, electric power is budgeted at S4 per unit, and indirect labor is budgeted at $2 per unit. For fixed overhead depreciation is budgeted at $70.000 per month. Supervision and other salaries is budgeted at $180,000 per month, insurance is budgeted at $12,000 per month, maintenance is budgeted at $23,000 per month, licensing fees and permits to use proprietary technology are budgeted at $64,000 per month, and other miscellaneous fixed expenses are budgeted at $31,000 per month Assume Harry's opening cash balance on April 1 is $25.000. Harry requires a minimum cash balance of $5,000 at the end of each month. If the budgeted cash balance will fall below this level Harry plans to borrow enough cash at the beginning of the month to keep his ending balance up to the minimum level. Harry's bank charges him interest at the rate of 1% per month (12% per year) on the balance outstanding during that month. Harry pays the interest at the beginning of the following month and plans to repay as much as he can without letting his cash balance go below $5,000. Assume no borrowings were outstanding as of April 1. HARRY KOHL Cash collection Schedule for the 3 months April, May, and June February March April May June $ 40,50,000 $ 41,50,000 $ 45,00,000 $ 50,00,000 $ 52,50,000 Expected Sales Revenue Cash collection from customers: In the month of sales (20%) In the first month of sales (40%) In the second month of sales (40%) Total Cash collection from customers $ 8,10,000 $ 8,30,000$ 9,00,000 8,30,000$ 9,00,000 $ 10,00,000 $ 10,50,000 $ 16,20,000 S 16,60,000 $ 18,00,000 $ 20,00,000 $ 16,20,000 $ 16,60,000 $ 18,00,000 $ 41,80,000 $ 44,60,000 $48,50,000 HARRY KOHL Schedule of Cash disbursement for purchase for the 3 months April, May, and June March April May June Expected Purchase of Material $ 13,50,000 $ 14,40,000 $ 16,00,000 $ 16,80,000 Write a brief report explaining the most important reasons why Harry's profits were different from the amount projected in the master budget. Cash disbursement for purchase: In the month of purchases (80%) In the first month of purchases (20%) Total Cash disbursement for purchase $ 10,80,000 $ 11,52,000 $ 12,80,000 $ 13,44,000 $ 2,70,000 $2,88,000 $ 3,20,000 $ 14,22,000 $ 15,68,000 $ 16,64,000 HARRY KOHL Performance Report for April Flexible Flexible Budget Actual Results Budget for Variance 175,000 units Cost Items Static Master Budget for 180,000 units Sales Volume Variance HARRY KOHL Budgeted Income Statement (Contribution Margin) for the 3 months April, May, and June April May June Quarter 2 Sales Revenue $ 45,00,000 $ 50,00,000 $ 52,50,000 $1,47,50,000 Less: Variable Costs Materials $ 14,40,000 $ 16,00,000 $16,80,000 $ 47,20,000 Labor $ 9,00,000 $ 10,00,000 $ 10,50,000 $ 29,50,000 Variable Overhead $ 12,60,000 $ 14,00,000 $ 14,70,000 $ 41,30,000 Total Variable costs $ 36,00,000 $ 40,00,000 $ 42,00,000 $1,18,00,000 $ 9,00,000 $ 10,00,000 $ 10,50,000 $ 29,50,000 Contribution Margin Less: Fixed Costs Fixed Overhead Fixed Selling & Admin costs Total Fixed costs Sales Revenue Direct Materials Direct Labor Supplies Electric Power Indirect Labor Salaries & Supervision Maintenance Insurance Permits and license fees Factory depreciation Other Overhead exp Total production expense Total Selling & Admin Exp Total Expenses Operating Income S43,42,000 S 43,75,000 S $ 15,15,625 $ 14,00,000 $ $ 8,81,436 $ 8,75,000 $ $ 1,66,288$ 1,75,000 $ $ 7,01,354 S 7,00,000 $ $ 3,39,272 S 3,50,000 $ $ 1,75,549 $ 1,80,000 $ $ 37,336 $ 23,000 $ $ 12,000 $ 12,000 $ S 68,000 $ 64,000 $ $ 70,000 $ 70,000 $ $ 33,173 $ 31,000 $ $ 40,00,033 $ 38,80,000 $ 3,29,453 $ 3,20,000 $ $ 43,29,486 S 42,00,000 $ $ 12,514 $ 1,75,000 $ 33,000 U $ 45,00,000 $ 1,15,625 U $ 14,40,000 $ 6,436U $ 9,00,000 $ 8,712 $ 1,80,000 $ 1,354 U $ 7,20,000 $ 10,728 F $ 3,60,000 $ 4,451 F $ 1,80,000 $ 14,330 U $ 23,000 $ Nonel s 12,000 $ 4,000 U $ 64,000 $ Nonel s 70,000 $ 2,173 U $ 31,000 $ 1,20,033 U $ 39,80,000 $ 9,453U $ 3,20,000 $ 1,29,486 U $ 43,00,000 $ 1,62,486 U $ 2,00,000 $ 1,58,000 V 75,625 JU 18,564 F 13,712F 18,646 F 20,728 F 4,451 F 14,336 U - None 4,000 U None 2,173 v 20,033 0 9,453 U 29,486 V 1,87,486 U $ 3,80,000 $ 3,80,000$ 3,80,000 $ 11,40,000 $ 3,20,000 $3,20,900 $ 3,20,900 $ 9,61,800 $ 7,00,000 $ 7,00,900 $ 7,00,900 $ 21,01,800 Net Income $ 2,00,000 $ 2,99,100 $ 3,49,100 $ 8,48,200 S