There is some overlapping of data in each screenshot to watch out for.
A B M N H Case 5: Comprehensive master budget in a manufacturing setting 1 2 4 Name: Instructor Course: Date: 5 7 8 4 10 11 12 13 14 Klandon Company manufactures decorative rocks for aquariums. Kim Klandon is preparing the budget for the quarter ended June 30. She has gathered the following 1. Klandon's sales manager reported that the company sold 12,000 bags of rocks in March. He has developed the following sales forecast. The expected sales price is $10 per baq. April 20,000 bags May 50,000 bags June 30,000 bags July 25,000 bags August 15,000 bags 2. Sales personnel receive a 5 percent commission on every bag of rocks sold. The following monthly fixed selling and administrative expenses are planned for the quarter. However, these amounts do not include the depreciation increase resulting from the budgeted equipment purchase in June (see part 7). 15 16 Variable Cost/Unit 17 18 19 20 21 22 23 24 25 $0.50 Monthly Fixed Selling and Administrative $10,000 25,000 1,000 10,000 500 Depreciation Salaries of sales personnel Advertising Management salaries Miscellaneous Bad debts Total costs $46,500 0.50 $1.00 27 28 3. The standard labor allowed for one bag of rocks is 15 minutes. The current direct labor rate is $10 per hour. 4. After experiencing difficulty in supplying customers in a timely fashion due to inventory shortages, the company established a policy requiring the ending finished goods inventory to equal 20 percent of the following month's budgeted sales, in units. On March 31, 4,000 bags were on hand. 5. Five pounds of raw materials are required to fill each bag of finished rocks. The company wants to have raw materials on hand at the end of each month equal to 10 percent of the following month's production needs. On March 31, 13,000 pounds of materials were on hand. 6. The raw materials used in production cost $0.40 per pound. Half of the month's purchases is paid for in the month of purchase; the other half, in the following month. No discount is available. 7. On June 1, the company plans to spend $48,000 to upgrade its office equipment that is fully depreciated. The new equipment is expected to have a five-year life, with no residual value. 8. The budgeted monthly variable and total fixed overhead are as follows. Variable overhead is based on the number of units produced. The fixed overhead budget is based on an annual production of 400,000 bags. 29 31 32 33 34 35 Variable Costilnit Fixed Monthly Overhead $8,000 1000 10,000 20,000 5,000 6,000 350.000 Depreciation Indirect materials Indirect labor Utilities Property taxes Maintenance Total costs $0.05 0.20 0.10 37 38 0.15 $0.50 40 41 42 45 46 47 48 -49 50 51 52 9. All sales are made on account. Historically, the company has collected 70 percent of its sales in the month of sale and 25 percent in the month following the sale. The remaining 5 percent of sales is uncollectible. 10. Klandon must maintain a minimum cash balance of $30,000. An open line of credit at a local bank allows the company to borrow up to $175,000 per quarter in $1,000 11. All borrowing is done at the beginning of the month, and all repayments are made at the end of a month in $1,000 increments. Accrued interest is paid only when principal is repaid. The interest rate is 12 percent per year. 12. A quarterly dividend of $49,000 will be declared and paid in April 13. Income taxes payable for the first quarter will be paid on April 15. Klandon's tax rate is 30 percent. 14. The March 31 balance sheet is as follows: March 31 Cash $40,000 Accounts receivable 30,000 Finished goods inventory 26,000 Raw materials inventory 5,200 AB o D G H K L M N N 0 PO Raw materials inventory Plant & equipment Accumulated Depreciation Total assets 5,200 200,000 150.000) $261,200 52 53 54 55 56 57 58 54 60 61 62 63 64 65 66 67 68 Accounts payable Income taxes payable Common stock Retained 3arnings Total liabilities and equities $12,000 50,000 52,000 137.200 $251,200 Required a. Prepare all components of Klandon's master budget for the second quarter. Use the remplare's provided' belor to prepare the budgets and po-forma statements. 69 70 71 72 Sales Budget April May June Quarter 73 74 75 76 Budgeted units sold Budgeted sales price Budgeted sales revenue $ 77 78 79 80 Mau June Quarter 81 82 $ 84 85 I save you own way and save the rest of the water Selinati shanistrative sentences April Leyvecvatico Sales pers come oceanian Advertising Ayanagement salaries Aliscellaneous Bad' denis Teraturopered everses $ $ lessnereast enenses Derection Baddents Totalcast oasts $ 87 88 89 $ 90 91 92 94 95 Piadian Audeer April Mau June Quarter Juli 96 97 98 Fudgered amisales - Budgered'ending inverway - Total wwsleged - Beqiwingineway - Budgeted production 99 400 Materials Purchase Budget April Mau June Duarter July 401 102 403 404 105 106 407 108 09 110 111 112 Budgeted production x Standard pounds/unit - Production needs + Budgeted ending inventory = Total pounds required - Beginning inventory - Budgeted purchases x Standard price/pound - Budgeted purchases cost $ 13 114 Direct Labor Budget AB E H K L M N 0 Direct Labor Budget April June Quarter Budgeted production x Standard DLH/unit - Total direct labor hrs required Standard wage rate - Budgeted direct labor cost $ Manufacturing Overhead Budget April Mau June Quarter Budgeted production Variable OH/unit - Total variable overhead + Fixed overhead Total budgeted Manufacturing OH Less: Non-cash items Depreciation = Total cash costs Ending Inventory and Costs of Goods Sold Budget Raw Ayaterials Beginning Balance Purchases of raw materials Less: Ending rawy materials inventory Raw materials used 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 462 463 164 165 166 767 168 169 170 1171 472 173 174 175 176 Finished Goods Zasts: Direct materials Direct labor Overhead Total standard unit cost x Ending inventory units Ending finished goods inventory $ $ $ Cal Goods Scd Beginning work in process inventory Direct materials used Direct labor Manufacturing overhead Total manufacturing costs Less: Ending work in process inventory Cost of goods manufactured Add: Beginning finished goods inventory Less: Ending finished goods inventory Cost of goods sold $ $ Cash Receipts Budget April Mau June TotalCash Bad Debts Accounts March sales $120,000 25% April sales $200,000 x 70% $200,000 N25% $200,000x5 May sales $500,000 x 70% $500,000 x 25% $500,000 SX June sales $300,000 x 70% $300,000 x 252 $300,000x52. M $300,000 x 70% $300,000 x 25% $300,000 x 5% Totals $ $ Mau June TotalCash Accounts Cash Payments for Materials Budget April A/P from March April purchases $56,000x50% $56,000 SOX May purchases $88,600x50%. $88,600 x 50% June purchases $56,800 x 50% $56,800x50% Total Cash Budget April Mau lune Quarter 174 175 476 1177 178 1179 180 181 782 183 484 185 486 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 Beginning oash balance Collections from sales Total cash available Less disbursements Materials purchases Direct labor Manufacturing overhead Selling & administrative expense Income taxes Equipment purchase Dividends Total cash disbursements Cash excess (deficiency) Minimum cash balance Cash excess (deficiency) Financing Borrowings Repayments Interest Total Financing Ending cash balance $ b. Prepare a pro forma income statement for the second quarter. Sales Cost of goods sold Gross profit Selling & administrative expense Operating income Interest expense Income before taxes Income tax expense (30%) Net income 227 228 229 230 231 232 233 234 235 C. Prepare a pro forma balance sheet as of June 30. Sale Cash AIR Save: Cash AIR Finished Goods Raw Materials Inventory Property, Plant & Equipment Less: Accumulated Depreciation Total Assets AIP Income Taxes Payable Note Payable Common Stock Retained Earnings Total Liabilities & Equities
