Accounting 1C Long Term Project- Master Budget You need to prepare a Master Budget for the The company has an exclusive right to sell PowerPulses and sales have been brisk. The Master Budget will be for the next three months starting April 1. The following information is available related to the budget. The company needs to maintain a minimum cash balance at the end of every month in the amount of $15,000. The Pulses are forecasted to sell at $36 each. Recent actual and projected sales (in units) are as follows Actual Jan Feb 57,000 68,000 Mar 80,000 Projected Apr 100,000 May 128,000 Jun 171,000 Projected Jul Aug 114,000 103,000 Sep 91,000 In order to meet the product demand, the company has established a policy requiring that ending inventory for each month must be equal to 90% of the units expected to sold in the next month. The cost to purchase each unit of product is $22. Purchases are typically paid for as follows: 50% paid in the month of purchase, and the remaining 50% paid in the month after purchase. All sales are on credit, with no discount, and payable within 15 days. The company's collections on account usually are 25% in the month of sale, 50% in the month immediately after the sale, and 25% in the second month after sale. The company has a very rigorous credit policy and there are virtually no bad debts. The company's operating expenses are shown below: Variable: Sales Commissions Fixed: Wages Utilities Insurance expired Depreciation Miscellaneous $4 per unit $47,000 1,600 1,600 2,000 2,600 All operating expenses are paid during the month, in cash, with the exception of depreciation and insurance expired. New fixed assets will be purchased during May for $30,000. The company declares dividends of $16,000 each quarter, payable in the first month of the following quarter. Balance Sheet at March 31 is as follows. ASSETS Cash Accounts receivable* $16,000 2,772,000 Inventory (90000 units)** 1,980,000 Unexpired insurance 19,200 Fixed assets (net of depreciation) Total Assets 193,600 $4,980,800 LIABILITIES AND EQUITY Accounts payable (purchases) Dividends payable Capital stock, (no par) Retained Earnings Total Liabilities & Equity $1,078,000 16,000 400,000 3,486,800 $4,980,800 *Accounts receivable consists of $612,000 from February sales and $2,160,000 from March Sales. Use these numbers for both scenarios. ** Use this same March ending inventory number for both scenarios. The company has a good relationship with its bank and can borrow money at a 10% annual rate at any time and in any amount. All borrowing and repayments must be made at the end of the month. When the company is ready to make a payment, all unpaid interest must be paid first. After the unpaid interest is paid, then principal can be repaid as long as the minimum cash balance is maintained. Required: You will complete all tasks listed below for the original facts above...this will be Scenario 1. Then you will repeat the entire process for Scenario 2. This second scenario will show what would happen if there was an increase of 20% (twenty percent) in the number of units sold. This is essentially a flexible budget. SCENARIO 1 Prepare a Master Budget for the three month period ending June 30th. Include the following detailed budgets: 1. a. A sales budget by month and in total. b. A schedule of budgeted cash collections from sales and accounts receivable by month and in total. c. A purchases budget in units and dollars by month and in total. d. A schedule of budgeted cash payments for purchases by month and in total. 2. A cash budget by month and in total. 3. A budgeted income statement for the three-month period ending June 30. Use the contribution margin approach. 4. A budgeted balance sheet as of June 30. 5. Calculate the Contribution Margin and Break-Even amounts (for the three month period) based on your assumptions about variable and fixed costs. SCENARIO 2 Repeat all the steps (1-5) shown above assuming that the number of units expected to be sold increase by 20%. The months January to March have already occurred so those will be the same for both Scenarios. Please pay attention to the information above when it says: *Accounts receivable consists of $612,000 from February sales and $2,160,000 from March Sales. Use these numbers for both scenarios. ** Use this same March ending inventory number for both scenarios. Budgeted Ending Inventory for June is based on July sales. Therefore you will need to increase the expected July sales in Scenario 2 and this will mean June Ending Inventory will be different in Scenario 2. Here are some check figures to check your final work. If you agree with these check numbers it is an important confirmation, although it is not guarantee that everything is correct. Amounts for the quarter: Scenario 1 Scenario 2 Sales budget $14,364,000 $17,236,800 Budgeted cash collections $11,367,000 $13,086,000 Budgeted purchases $9,055,200 $11,262,240 Budgeted cash payments-purchases $8,816,500 $10,760,200 Ending Cash Balance $770,054 $217,250 Inc Stmt Interest Expense $846 $9,750 Inc Stmt Net income $3,824,754 $4,613,850 Bal Sheet AR $5,769,000 $6,922,800 Bal Sheet Inventory $2,257,200 $2,708,640 Bal Sheet AP $1,316,700 $1,580,040 Bal Sheet Retained Earnings (RE) $7,295,554 $8,084,650 Bal Sheet Total Assets (=Liab+OE) $9,028,254 $10,080,690 5 6 Amounts for the quarter: Scenario 1 Scenario 2 7 Sales budget 14,364,000 17,236,800 8 Budgeted cash collections 11,367,000 13,086,000 9 Budgeted purchases 9,055,200 11,262,240 10 Budgeted cash payments-purchases 8,816,500 10,760,200 11 Ending Cash Balance 770,054 217,250 12 Inc Stmt Interest Expense 846 9,750 13 Inc Stmt Net income 3,824,754 4,613,850 14 15 16 Bal Sheet AR Bal Sheet Inventory Bal Sheet AP 5,769,000 6,922,800 2,257,000 2,708,640 1,316,000 1,580,040 17 Bal Sheet Retained Earning 7,295,554 8,084,650 18 Bal Sheet Total Assets (=Liab+OE) 9,028,254 10,080,690 19 20 21 22 23 24 SCENARIO 1 25 26 27 Sales budget For the Three Months Ending June 30, 202X 28 29 30 Budgeted sales (units) 31 32 Budgeted sales price/unit Budgeted sales price/dollars April May June Quarter 100,000 128,000 171,000 399,000 $ 36 $ 36 $ 36 $3,600,000 $ 4,608,000 $6,156,000 $14,364,000 35 36 37 38 39 40 41 February sales 42 March sales 43 April sales 44 May sales 45 June sales 46 Total cash collections 47 48 49 50 56 51 52 53 54 55 56 57 58 59 60 61 62 Budgeted sales (units) Add budgeted ending inventory* Total needs Less beginning inventory Required purchases in units Unit cost Required purchases in dollars SCENARIO 1 Budgeted cash collections For the Three Months Ending June 30, 202X April May June Quarter 612,000 0 0 612,000 1,440,000 720,000 0 2,160,000 900,000 1,800,000 900,000 3,600,000 ? ? 1,152,000 2,304,000 3,456,000 1,539,000 1,539,000 $ 2,952,000 $ 3,672,000 $4,743,000 $ 11,367,000 SCENARIO 1 Budgeted purchases For the Three Months Ending June 30, 202X April May June Quarter 100,000 128,000 171,000 399,000 115,200 153,900 102,600 371,700 215,200 281,900 273,600 770,700 90,000 115,200 153,900 359,100 125,200 166,700 119,700 411,600 $ 22 $ 22 $ 22 $ 22 $ 2,754,400 $3,667,400 $ 2,633,400 $ $ 9,055,200 63 64 *Budgeted ending inventory at 90% of next months sales in units. 65 66 67 68 69 70 SCENARIO 1 Budgeted cash payments for purchases For the Three Months Ending June 30, 202X 71 72 73 March purchases 74 April purchases 75 May purchases 76 June purchases April May June Quarter 1,078,000 0 0 1,078,000 1,377,200 1,377,200 0 2,754,400 1,833,700 ? 1,833,700 3,667,400 1,316,700 1,316,700 77 Total cash payments $ 2,455,200 $3,210,900 $3,150,400 $8,816,500 78 79 80 81 82 83 SCENARIO 1 Cash Budget For the Three Months Ending June 30, 202X 84 June Quarter 85 86 Cash balance beginning of month 87 Add cash from customers 88 Total cash available 89 Less cash payments: 90 91 92 93 94 Purchase of Inventory Sales commissions Salaries and wages Utilities Misc 95 Dividends paid Equipment Purchases 96 97 98 99 Financing 100 101 Total cash paid Excess (deficiency) of available over pmts Borrowing (Repayment) Interest 102 Total financing 103 Cash balance end of month 104 April May $ 16,000 $ 2,952,000 2,968,000 45,600 $ 15,000 $ 76,600 3,672,000 4,743,000 11,367,000 3,717,600 4,758,000 11,443,600 2,455,200 3,210,900 3,150,400 8,816,500 400,000 512,000 684,000 1,596,000 47,000 47,000 47,000 141,000 1,600 1,600 1,600 4,800 2,600 2,600 2,600 7,800 16,000 0 0 16,000 0 30,000 0 30,000 2,922,400 3,804,100 3,885,600 10,612,100 45,600 (86,500) 872,400 831,500 0 0 101,500 (101,500) 0 0 0 (8,458,333) (846) 0 101,500 (1,023,458) (846) $ 45,600 $ 15,000 $ 770,054 $ 830,654 105 106 107 108 109 SCENARIO 1 Budgeted Income Statement For the Three Months Ending June 30, 202X 110 April May June Quarter 111 Sales in units (Memo) 100,000 128,000 171,000 399,000 112 113 Sales dollars 3,600,000 4,608,000 6,156,000 14,364,000 114 Less variable expenses 0 115 Cost of Goods sold at $22 per unit 116 Commissions $4 per unit 117 Contribution Margin 1,000,000 1,280,000 1,710,000 3,990,000 118 Less fixed expenses 0 119 Wages 47,000 47,000 47,000 141,000 120 Utilities 1,600 1,600 1,600 4,800 121 Insurance expired 1600 1,600 1,600 4,800 122 Depreciation 2,000 2,000 2,000 6,000 123 Miscellaneous 2,600 2,600 2,600 7,800 124 Net operating income 945,200 1,225,200 1,655,200 3,825,600 125 Less interest expense* 0 0 84,583,333 846 126 Net income 945,200 1,225,200 1,654,354 3,824,754 127 10.00% 0 128 129 *MEMO: interest expense calculation: 130 131 Interest expense on amount borrowed in 2nd month: 132 total amount borrowed in 2nd month 133 rate per year 134 interest for 12 months $ 135 number of months in a year 136 interest for 1 month $ 137 months outstanding 138 total interest expense for this amount 139 140 Interest expense on amount borrowed in 1st month: 141 total amount borrowed in 1st month 142 rate per year 143 interest for 12 months $ 144 number of months in a year 145 interest for 1 month $ 146 months outstanding 147 148 total interest expense for this amount total interest expense for both amounts 149 150 151 2010 10.00% O2020 12 0 MEMO: To help you calculate interest and round the amount to the nearest dollar, formulas are provided in the yellow cells. ENTER AMOUNTS FROM CASH BUDGET HERE (If any) CHANGE YOUR RATE HERE if different MEMO: note rounding formula in the column G formula ENTER AMOUNTS FROM CASH BUDGET HERE (If any) CHANGE YOUR RATE HERE if different 0 MEMO: note rounding formula in the column G formula 0 189 190 191 Contribution Margin and BEP 192 Unit Sales price 193 Unit Purchase price 194 Unit Sales Commissions PLS HELP! PO this 195 Unit Contribution Margin 196 197 Fixed Costs for three month period 198 Wages 199 Utilities 200 201 Insurance expired Depreciation 202 Miscellaneous 203 Total Fixed Costs 204 205 206 Break Even Point: 207 Fixed Costs/Unit CM BEP units 208 Fixed Costs/CM% BEP dollars 209 210 218 219 220 221 222 223 224 225 Budgeted sales (units) 226 Budgeted sales price/unit 227 Budgeted sales price/dollars 228 229 230 231 232 233 234 235 236 February sales 237 March sales 238 April sales 239 May sales 240 June sales 241 Total cash collections 242 243 244 245 246 247 248 249 SCENARIO 2 (Increase sales units by 20%) Sales budget For the Three Months Ending June 30, 202X April May June Quarter 120,000 153,600 $ 36 $ 36 $ 205,200 36 478,800 $ 36 $4,320,000 $ 5,529,600 $ 7,387,200 $17,236,800 SCENARIO 2 (Increase sales units by 20%) Budgeted cash collections For the Three Months Ending June 30, 202X April 612,000 May June Quarter 0 0 612,000 1,440,000 720,000 0 2,160,000 1,080,000 2,160,000 1,382,400 2,764,800 1,080,000 4,320,000 4,147,200 1 1,846,800 1,846,800 $ 3,132,000 $4,262,400 $5,691,600 $13,086,000 SCENARIO 2 (Increase sales units by 20%) Budgeted purchases For the Three Months Ending June 30, 202X April May June Quarter 120,000 153,600 205,200 478,800 138,240 184,680 123,120 446,040 258,240 338,280 328,320 924,840 90,000 138,240 184,680 412,920 168,240 138,240 143,640 511,920 $ 22 $ 22 $ 22 $ 22 $ 3,701,280 $4,400,800 $ 3,160,080 $ 11,262,240 250 Budgeted sales (units) Add budgeted ending inventory" 252 Total needs 253 Less beginning inventory 254 Required purchases in units 255 Unit cost 256 Required purchases in dollars 257 beginning AP 258 259 *Budgeted ending inventory at 90% of next months sales in units. 260 262 263 264 265 266 267 268 March purchases 269 April purchases 270 May purchases 271 June purchases 272 Total cash payments 273 274 275 276 277 278 279 280 SCENARIO 2 (Increase sales units by 20%) Budgeted cash payments for purchases For the Three Months Ending June 30, 202X April May June Quarter 1,078,000 0 0 1,078,000 1,850,640 1,850,640 0 3,701,280 2,200,440 2,200,440 4,400,880 1,580,040 1,580,040 $2,928,640 $4,051,080 $ 3,780,480 $ 10,760,200 SCENARIO 2 (Increase sales units by 20%) Cash Budget For the Three Months Ending June 30, 202X April May June Quarter 281 Cash balance beginning of month $ 16,000 $ 282 Add cash from customers 3,132,000 15,000 $ 9,286 4,262,400 $ 40,286 5,691,600 13,086,000 283 Total cash available 3,148,000 4,277,400 5,700,886 13,126,286 284 Less cash payments: 285 Purchase of Inventory 2,928,640 4,051,080 3,780,480 10,760,200 286 Sales commissions 480,000 614,400 820,800 1,915,200 287 Salaries and wages 47,000 47,000 47,000 141,000 288 Utilities 1,600 1,600 1,600 4,800 289 Misc 2,600 2,600 2,600 7,800 290 Dividends paid 16,000 0 0 16,000 291 Equipment Purchases 0 30,000 0 30,000 292 Total cash paid 3,475,840 4,746,680 4,652,480 12,875,000 293 Excess (deficiency) of available over pmts (327,840) (469,280) 1,048,406 251,286 294 Financing 295 Borrowing (Repayment) 342,840 481,423 (824,263) 0 296 Interest 0 (2,856) (6,893) (9,750) 297 Total financing 342,840 478,566 (831,156) (9,750) 298 Cash balance end of month $ 15,000 $ 9,286 $ 217,250 $ 241,536 299 300 301 302 303 304 305 306 307 Sales in units (Memo) 308 Sales dollars SCENARIO 2 (Increase sales units by 20%) Budgeted Income Statement For the Three Months Ending June 30, 202X April May June Quarter 120,000 4,320,000 153,600 5,529,600 205,200 478,800 7,387,200 17,236,800 309 Less variable expenses 310 Cost of Goods sold at $22 per unit 2640000 3,379,200 $4,514,400 40,933,600 311 Commissions $4 per unit 480,000 614,400 312 Contribution Margin 1,200,000 1,536,000 820,800 2,052,000 1,915,200 4,788,000 313 Less fixed expenses 314 Wages 47,000 4,700 4,700 141,000 315 Utilities 1,600 1,600 1,600 4,800 316 317 318 Insurance expired 1,600 1,600 1,600 4,800 Depreciation 2,000 2,000 2,000 6,000 Miscellaneous 2,600 2,600 2,600 7,800 319 Net operating income 1,145,200 1,481,200 1,997,200 4,623,600 Net income 320 Less interest expense* 321 322 0 1,145,200 2,857 1,478,343 6,893 9,750 1,990,307 $ 4,613,850 323 324 *MEMO: interest expense calculation: 325 326 327 total amount borrowed in 2nd month 328 rate per year 329 interest for 12 months $ Interest expense on amount borrowed in 2nd month: 481,423 10.00% MEMO: To help you calculate interest and round the amount to the nearest dollar, formulas are provided in the yellow cells. ENTER AMOUNTS FROM CASH BUDGET HERE (If any) CHANGE YOUR RATE HERE if different 48,142 330 number of months in a year 12 331 interest for 1 month $ 4,012 332 months outstanding 1 333 total interest expense for this amount 4,012 4,012 MEMO: note rounding formula in the column G formula 334 335 Interest expense on amount borrowed in 1st month: 336 total amount borrowed in 1st month 342,840 337 rate per year 10.00% ENTER AMOUNTS FROM CASH BUDGET HERE (If any) CHANGE YOUR RATE HERE if different 338 interest for 12 months $ 34,284 339 number of months in a year 12 340 interest for 1 month $ 2,857 341 months outstanding 2 342 total interest expense for this amount 5,714 343 total interest expense for both amounts 5,714 9,726 MEMO: note rounding formula in the column G formula 344 345 346 347 348 349 350 Destiny Co. 351 352 SCENARIO 2 (Increase sales units by 20%) Budgeted Balance Sheet June 30, 202X 353 354 Cash 355 AR 356 Inventory 357 Unexpired insurance 358 Fixed assets, net April May June Quarter units at $ per unit 15,000 3,960,000 3041280 17,600 191,600 9,286 3,844,800 4,062,960 16,000 219,600 217,250 6,922,800 241,536 14,727,600 2,708,640 9,812,880 14,400 217,600 10,080,690 48,000 828,800 25,428,816 359 Total Assets 360 361 362 AP 363 Interest Payable 364 Dividends payable 365 Notes payable-bank 366 Capital Stock, no-par 367 Retained Earnings 368 Total Liabilities & Equity 369 7,225,480 8,152,646 Liabilities & Equity 1,850,640 2,200,440 1,580,040 5,631,120 0 0 2,857 0 342,840 400,000 481,423 400,000 0 16,000 0 2,857 16,000 824,263 4,632,000 5,067,926 400,000 8,084,650 1,200,000 17,784,576 7,225,480 8,152,646 10,080,690 25,458,816 370 371 372 373 MEMO: Balance Sheet detail: 374 Accounts Receivable (AR) 375 May Sales 376 June Sales 377 378 379 Retained Earnings (RE) 380 Beg RE 381 Net income 382 Less Dividends declared 383 384 385 0 AR Beginning +Sales -Collections AR Ending 386 387 Contribution Margin and BEP 388 389 Unit Sales price 390 Unit Purchase price 391 Unit Sales Commissions 392 Unit Contribution Margin 393 394 Fixed Costs for three month period 395 Wages 396 Utilities 397 Insurance expired 398 Depreciation 399 Miscellaneous 400 Total Fixed Costs 401 402 403 Break Even Point: 404 Fixed Costs/Unit CM 405 Fixed Costs/CM% 406 HELP PLS DO THIS! BEP units BEP dollars 407 408 409 410 152 153 154 155 156 SCENARIO 1 Budgeted Balance Sheet June 30, 202X April May June Quarter 157 Cash 158 AR 45,600 3,420,000 15,000 770,054 830,654 3,204,000 5,769,000 12,393,000 159 Inventory. units at $ ? per unit 2534400 3,385,800 2,257,200 8,177,400 160 Unexpired insurance 17,600 16,000 14,400 48,000 161 Fixed assets, net 191,600 162 Total Assets 6,209,200 219,600 6,840,400 217,600 9,028,254 628,800 22,077,854 163 164 Liabilities & Equity. 165 AP 1,377,200 1,833,700 1,316,700 4,527,600 166 167 168 Interest Payable Dividends payable Notes payable-bank 169 Capital Stock, no-par Retained Earnings 0 0 0 0 0 0 16,000 16,000 0 101,500 0 101,500 400,000 400,000 4,432,000 4,505,200 400,000 7,295,554 1,200,000 16,232,754 6,209,200 6,840,400 9,028,254 22,077,854 IF NEEDED PLS (CONFUSEDI 170 171 Total Liabilities & Equity 172 173 nd this IF 174 MEMO: 175 Balance Sheet detail: 176 177 Accounts Receivable (AR) 178 May Sales 0 179 June Sales 1 180 181 182 Retained Earnings (RE) 183 Beg RE 184 Net income 185 Less Dividends declared 186 187 188 AR Beginning +Sales -Collections AR Ending