Accounting Homework
lnsy3
master_budget.xlsxSales
| Group Members: | Jorri Hill | Lindsay Neece | Isabel Jacobs | Sophia Mathew | ||||||||
| Master Budget | ||||||||||||
| Sales Budget | ||||||||||||
| Year 1 | Year 2 | Year 3 | ||||||||||
| Year 1 4th QTR | with % increase | |||||||||||
| Product A: | Actual | in orders | Q1 | Q2 | Q3 | Q4 | Q1 | |||||
| Product A Sales forecast (units) | 363,125.00 | 432,118.75 | 479,651.81 | 237,665.31 | 337,052.63 | 432,118.75 | 446,076.19 | |||||
| Orders | 415 | 493.85 | 548.1735 | 271.6175 | 385.203 | 493.85 | 509.801355 | |||||
| Average units per order | 875 | 875 | 875 | 875 | 875 | 875 | 875 | |||||
| Price per unit | $46.00 | $46.00 | $46.00 | $46.00 | $46.00 | $46.00 | $46.00 | |||||
| Total budgeted sales (dollars) | 16,703,750.00 | 19,877,462.50 | 22,063,983.38 | 10,932,604.38 | 15,504,420.75 | 19,877,462.50 | 20,519,504.54 | |||||
| Product B: | ||||||||||||
| Product B Sales forecast (units) | 347,768.00 | 413,843.92 | 459,366.75 | 227,614.16 | 322,798.26 | 413,843.92 | 427,211.08 | |||||
| Orders | 116 | 138.04 | 153.2244 | 75.922 | 107.6712 | 138.04 | 142.498692 | |||||
| Average units per order | 2998 | 2998 | 2998 | 2998 | 2998 | 2998 | 2998 | |||||
| Price per unit | 55 | 55 | 55 | 55 | 55 | 55 | 55 | |||||
| Total budgeted sales (dollars) | 19,127,240.00 | 22,761,415.60 | 25,265,171.32 | 12,518,778.58 | 17,753,904.17 | 22,761,415.60 | 23,496,609.32 | |||||
| Total quarterly budgeted sales | 35,830,990.00 | 42,638,878.10 | 47,329,154.69 | 23,451,382.96 | 33,258,324.92 | 42,638,878.10 | 44,016,113.86 | |||||
| Schedule of Expected Cash/Credit Collections (Year 2) | ||||||||||||
| Q1 | Q2 | Q3 | Q4 | Total | ||||||||
| Cash Sales | 13,725,454.86 | 6,800,901.06 | 9,644,914.23 | 12,365,274.65 | 42,536,544.79 | |||||||
| Credit Sales | 33,603,699.83 | 16,650,481.90 | 23,613,410.69 | 30,273,603.45 | 104,141,195.87 | |||||||
| Total Collections | 47,329,154.69 | 23,451,382.96 | 33,258,324.92 | 42,638,878.10 | 146,677,740.66 | |||||||
| Schedule of Expected Cash Collections (Year 2) | ||||||||||||
| Q1 | Q2 | Q3 | Q4 | Total | ||||||||
| Accounts Receivable Beginning Balance | 12,482,882 | 9,667,201 | 0 | 0 | 22,150,083 | |||||||
| Q1 Sales | 8,241,127.70 | 12,540,846.50 | 13,615,776.20 | 0.00 | 34,397,750.40 | |||||||
| Q2 Sales | 0.00 | 9,806,941.96 | 14,923,607.34 | 16,202,773.68 | 40,933,322.98 | |||||||
| Q3 Sales | 0.00 | 0.00 | 7,649,414.73 | 11,640,413.72 | 19,289,828.45 | |||||||
| Q4 Sales | 0.00 | 0.00 | 0.00 | 9,806,941.96 | 9,806,941.96 | |||||||
| Total Collections | 20,724,009.70 | 32,014,989.46 | 36,188,798.27 | 37,650,129.36 | 126,577,926.79 | |||||||
| Bad Debt Expense (Year 2) | Q1 | Q2 | Q3 | Q4 | Year | |||||||
| 1,235,841 | 3,125,878 | 798,568 | 50,000 | 5,210,287 | ||||||||
| Fixed Assets Schedule (Year 2) | Accumulated Depreciation Schedule (Year 2) | Depreciation Expense (Year 2) | ||||||||||
| Existing fixed assets | 5,988,560 | Existing Accumulated Depreciation | 3,593,136 | Original Selling Price | 400,000 | |||||||
| Sold assets | -400,000 | Add: Depreciation Expense | Purchase Equipment | 750,000 | ||||||||
| Purchased assets | 750,000 | Less: Selling Price | Depreciation | 240,000 | ||||||||
| Increase in acumulated depreciation | 6,338,560 | Total Accumulated Depreciation | Selling Price | |||||||||
| Add: Sold Asset Depreciation | ||||||||||||
| Depreciation expense | ||||||||||||
| Cash Budget (Year 2) | Financing Schedule (Year 2) | |||||||||||
| Q1 | Q2 | Q3 | Q4 | Year | Q1 | Q2 | Q3 | Q4 | Year | |||
| Cash Balance Beginning | 100,000 | Loans | ||||||||||
| Add: Cash Collections | 47,329,154.69 | 23,451,382.96 | 33,258,324.92 | 42,638,878.10 | 146,677,740.66 | Interest | ||||||
| Total Cash Available | 47,429,155 | Loan Payback | ||||||||||
| Less: Cash Disbursements: | Loan Balance, Outstanding | |||||||||||
| Direct Materials | 11636443.73 | |||||||||||
| Direct Labor | ||||||||||||
| Inventory | Common Stock | 615,000 | ||||||||||
| Selling and Administrative | Dividends | -1,000,000 | ||||||||||
| Total Cash Disbursements | Total Financing | |||||||||||
| Excess Cash | ||||||||||||
| Total Financing | ||||||||||||
| Cash Balance End | ||||||||||||
| Cost of Goods Sold Budget (Year 2) | ||||||||||||
| Rate | Product A | Product B | ||||||||||
| Raw material XM1 | 4.5 | 2.3 | 3.5 | |||||||||
| Raw material XM2 | 2.45 | 3.2 | 5.5 | |||||||||
| Labor Hours | 7.25 | 1.6 | 1.6 | |||||||||
| Manufacturing OVH | ||||||||||||
| Total COGS | ||||||||||||
| Income Statement (Year 2) | ||||||||||||
| Sales | 44,016,113.86 | |||||||||||
| COGS | ||||||||||||
| Gross Margin | ||||||||||||
| Selling and administrative expenses | ||||||||||||
| Salaries and commissions | ||||||||||||
| Depreciation expense | ||||||||||||
| Total selling and administrative expenses | ||||||||||||
| Net operating income | ||||||||||||
| Balance Sheet (Year 2) | ||||||||||||
| Assets | ||||||||||||
| Curent Assets | ||||||||||||
| Cash | ||||||||||||
| ccounts Receivable | ||||||||||||
| Lss: Allow for Bad Expense | ||||||||||||
| Raw Material Inventory | ||||||||||||
| Product A | ||||||||||||
| Product B | ||||||||||||
| Total Current Assets | ||||||||||||
| Fixed Assets - Cost | ||||||||||||
| Accumulated Depreciation | ||||||||||||
| Fixed Assets - Net | ||||||||||||
| Total Assets | ||||||||||||
| Liabilities and Stockholder's Equity | ||||||||||||
| Accounts Payable | ||||||||||||
| Bank Loan | ||||||||||||
| Stockholders' Equity | ||||||||||||
| Capital Stock | ||||||||||||
| Retained Earnings | ||||||||||||
| Total Liabilities and Equity | ||||||||||||
| Selling of Cash Disbursement for Selling and Administrative Expenses (Year 2) | ||||||||||||
| Q1 | Q2 | Q3 | Q4 | Year | ||||||||
| Sales Commission | 14153241.05 | 16842356.85 | 18695016.1 | 9263296.267 | 13137038.34 | |||||||
| Marketing | 2020704.45 | 2395138.296 | 2653103.508 | 1339826.063 | 1879207.87 | |||||||
| Other Administration Costs | 18,000 | 18,000 | 18,000 | 18,000 | 72,000 | |||||||
| Outsourced Expenses | 1,250,000 | 0 | 0 | 1,750,000 | 3,000,000 | |||||||
| Total | 17,441,946 | 19,255,495 | 21,366,120 | 12,371,122 | 18,088,246 |
Production
| Production Budget - Product A (Year 2) | ||||||
| Q1 | Q2 | Q3 | Q4 | Total | Year 1 | |
| Budgeted Unit Sales | 479,651.81 | 237,665.31 | 337,052.63 | 432,118.75 | 1,486,488.50 | 363,125.00 |
| Add: Desired units of ending finished goods inventory | 65,357.96 | 92,689.47 | 118,832.66 | 122,670.95 | 399,551.04 | 131,904.25 |
| Total needs | 545,009.77 | 330,354.78 | 455,885.28 | 554,789.70 | 1,886,039.54 | 495,029.25 |
| Less: Units of beginning finished goods inventory | 0.00 | 65,357.96 | 92,689.47 | 118,832.66 | 276,880.09 | 0 |
| Required production of units | 545,009.77 | 264,996.82 | 363,195.81 | 435,957.04 | 1,609,159.45 | 495,029.25 |
| Production Budget - Product B (Year 2) | ||||||
| Q1 | Q2 | Q3 | Q4 | Total | Year 1 | |
| Budgeted Unit Sales | 347,768.00 | 413,843.92 | 459,366.75 | 227,614.16 | 1,448,592.83 | 347,768.00 |
| Add: Desired units of ending finished goods inventory | 196,575.86 | 218,199.21 | 108,116.72 | 117,483.05 | 640,374.84 | 54,058.36 |
| Total needs | 544,343.86 | 632,043.13 | 567,483.48 | 345,097.20 | 2,088,967.67 | 401,826.36 |
| Less: Units of beginning finished goods inventory | 0.00 | 196,575.86 | 218,199.21 | 108,116.72 | 522,891.79 | 0 |
| Required production of units | 544,343.86 | 435,467.26 | 349,284.27 | 236,980.48 | 1,566,075.87 | 401,826.36 |
Materials
| Direct Materials Budget - Product A (Year 2) | ||||||
| Q1 | Q2 | Q3 | Q4 | Year 2 | Year 1 | |
| Required production in units of finished goods | 545,009.77 | 264,996.82 | 363,195.81 | 435,957.04 | 1,609,159.45 | 495,029.25 |
| Units of raw materials needed per unit of finished oods | ||||||
| XM1 | 2.3 | 2.3 | 2.3 | 2.3 | 2.3 | 2.3 |
| XM2 | 3.2 | 3.2 | 3.2 | 3.2 | 3.2 | 3.2 |
| Units of raw materials needed to meet production | ||||||
| XM1 | 1,253,522.48 | 609,492.69 | 835,350.36 | 1,002,701.20 | 3,701,066.74 | 1138567.271 |
| XM2 | 1,744,031.28 | 847,989.84 | 1,162,226.59 | 1,395,062.54 | 5,149,310.24 | 1584093.595 |
| Add: Desired units of ending raw materials inventory | ||||||
| XM1 | 167,610.49 | 229,721.35 | 275,742.83 | 313,106.00 | 986,180.67 | 344718.6817 |
| XM2 | 233,197.20 | 319,612.31 | 383,642.20 | 435,625.74 | 1,372,077.45 | 479608.6006 |
| Total units of raw material s needed | ||||||
| XM1 | 1,421,132.97 | 839,214.04 | 1,111,093.19 | 1,315,807.20 | 4,687,247.41 | 1,483,285.95 |
| XM2 | 1,977,228.48 | 1,167,602.15 | 1,545,868.79 | 1,830,688.28 | 6,521,387.70 | 2,063,702.20 |
| Less: Units of beginning raw materials inventory | ||||||
| XM1 | 344,718.68 | 167,610.49 | 229,721.35 | 275,742.83 | 344,718.68 | 344,718.68 |
| XM2 | 479,608.60 | 233,197.20 | 319,612.31 | 383,642.20 | 479,608.60 | 479,608.60 |
| Units of raw material to be purchased | ||||||
| XM1 | 1,076,414.29 | 671,603.55 | 881,371.84 | 1,040,064.37 | 3,669,454.06 | 1,138,567.27 |
| XM2 | 1,497,619.88 | 934,404.94 | 1,226,256.48 | 1,447,046.08 | 5,105,327.38 | 1,584,093.60 |
| Unit cost of raw materials | ||||||
| XM1 | 4.50 | 4.50 | 4.50 | 4.50 | 4.50 | 4.50 |
| XM2 | 2.45 | 2.45 | 2.45 | 2.45 | 2.45 | 2.45 |
| Cost of raw materials to be purchased | ||||||
| XM1 | 4,843,864.30 | 3,022,215.99 | 3,966,173.29 | 4,680,289.67 | 16,512,543.25 | 5,123,552.72 |
| XM2 | 3,669,168.70 | 2,289,292.11 | 3,004,328.37 | 3,545,262.90 | 12,508,052.08 | 3,881,029.31 |
| Total required purchases | 8,513,033.00 | 5,311,508.10 | 6,970,501.66 | 8,225,552.58 | 29,020,595.33 | 9,004,582.03 |
| Direct Materials Budget - Product B (Year 2) | ||||||
| Q1 | Q2 | Q3 | Q4 | Year 2 | Year 1 | |
| Required production in units of finished goods | 544,343.86 | 435,467.26 | 349,284.27 | 236,980.48 | 1,566,075.87 | 401,826.36 |
| Units of raw materials needed per unit of finished oods | ||||||
| XM1 | 3.5 | 3.5 | 3.5 | 3.5 | 3.5 | 3.5 |
| XM2 | 5.5 | 5.5 | 5.5 | 5.5 | 5.5 | 5.5 |
| Units of raw materials needed to meet production | ||||||
| XM1 | 1,905,203.52 | 1,524,135.43 | 1,222,494.94 | 829,431.67 | 5,481,265.56 | 1,406,392.27 |
| XM2 | 2,993,891.24 | 2,395,069.96 | 1,921,063.48 | 1,303,392.63 | 8,613,417.31 | 2,210,044.99 |
| Add: Desired units of ending raw materials inventory | ||||||
| XM1 | 723,964.33 | 580,685.10 | 393,980.05 | 668,036.33 | 2,366,665.80 | 904,971.67 |
| XM2 | 658,644.24 | 528,292.46 | 358,432.97 | 1,049,771.37 | 2,595,141.04 | 1,422,098.34 |
| Total units of raw materials needed | ||||||
| XM1 | 2,629,167.84 | 2,104,820.52 | 1,616,474.99 | 1,497,468.00 | 7,847,931.35 | 2,311,363.94 |
| XM2 | 3,652,535.48 | 2,923,362.41 | 2,279,496.45 | 2,353,164.00 | 11,208,558.34 | 3,632,143.33 |
| Less: Units of beginning raw materials inventory | ||||||
| XM1 | 904,971.67 | 723,964.33 | 580,685.10 | 393,980.05 | 904,971.67 | 904,971.67 |
| XM2 | 1,422,098.34 | 658,644.24 | 528,292.46 | 358,432.97 | 1,422,098.34 | 1,422,098.34 |
| Units of raw material to be purchased | ||||||
| XM1 | 1,724,196.17 | 1,380,856.20 | 1,035,789.89 | 1,103,487.96 | 5,244,330.21 | 1,406,392.27 |
| XM2 | 2,230,437.14 | 2,264,718.17 | 1,751,203.99 | 1,994,731.03 | 8,241,090.34 | 2,210,044.99 |
| Unit cost of raw materials | ||||||
| XM1 | 4.50 | 4.50 | 4.50 | 4.50 | 4.50 | 4.50 |
| XM2 | 2.45 | 2.45 | 2.45 | 2.45 | 2.45 | 2.45 |
| Cost of raw materials to be purchased | ||||||
| XM1 | 8,573,415.83 | 6,858,609.42 | 5,501,227.23 | 3,732,442.54 | 24,665,695.01 | 6,328,765.20 |
| XM2 | 7,335,033.54 | 5,867,921.39 | 4,706,605.52 | 3,193,311.95 | 21,102,872.40 | 5,414,610.23 |
| Total Required Purchases | 15,908,449.37 | 12,726,530.81 | 10,207,832.75 | 6,925,754.48 | 45,768,567.41 | 11,743,375.43 |
| Schedule of Expected Cash Disbursements for Materials (Year 2) | ||||||
| Q1 | Q2 | Q3 | Q4 | Year | ||
| Cash Purchases (30%) | 4,772,534.81 | 3,817,959.24 | 3,062,349.82 | 2,077,726.35 | 13,730,570.22 | |
| Credit Purchases (70%) | 11,135,914.56 | 8,908,571.57 | 7,145,482.92 | 4,848,028.14 | 32,037,997.19 | |
| Total Purchases | 15,908,449.37 | 12,726,530.81 | 10,207,832.75 | 6,925,754.48 | 45,768,567.41 | |
| Q1 | Q2 | Q3 | Q4 | Year | ||
| Accounts Payable, Beginning Balance | 4,954,895.00 | 0.00 | 0.00 | 0.00 | 4,954,895.00 | |
| Accounts Payable, End balance | 0.00 | 0.00 | 0.00 | ??? | 0.00 | |
| Q1, Purchases | 6,681,548.73 | 4,454,365.82 | 0.00 | 0.00 | 11,135,914.56 | |
| Q2, Purchases | 0.00 | 5,345,142.94 | 3,563,428.63 | 0.00 | 8,908,571.57 | |
| Q3, Purchases | 0.00 | 0.00 | 4,287,289.75 | 2,858,193.17 | 7,145,482.92 | |
| Q4, Purchases | 0.00 | 0.00 | 0.00 | 2,908,816.88 | 2,908,816.88 | |
| Total Cash Disbursements | 11,636,443.73 | 9,799,508.76 | 7,850,718.38 | 5,767,010.05 | 35,053,680.93 |
Labor
| Direct Labor Budget - Product A (Year 2) | |||||
| Q1 | Q2 | Q3 | Q4 | Year | |
| Required production in units | 545,009.77 | 264,996.82 | 363,195.81 | 435,957.04 | 1,609,159.45 |
| Direct labour-hous per unit | 1.9 | 1.9 | 1.9 | 1.9 | 1.9 |
| Total direct labor-hours needed | 1035518.57 | 503493.9645 | 690072.0378 | 828318.3851 | 3057402.957 |
| Direct labour cost per hour | 7.25 | 7.25 | 7.25 | 7.25 | 7.25 |
| Total direct labor cost | 7507509.629 | 3650331.243 | 5003022.274 | 6005308.292 | 22166171.44 |
| Direct Labor Budget - Product B (Year 2) | |||||
| Q1 | Q2 | Q3 | Q4 | Year | |
| Required production in units | 544,343.86 | 435,467.26 | 349,284.27 | 236,980.48 | 1,566,075.87 |
| Direct labour-hous per unit | 1.6 | 1.6 | 1.6 | 1.6 | 1.6 |
| Total direct labor-hours needed | 870950.1792 | 696747.6237 | 558854.8296 | 379168.7656 | 2505721.398 |
| Direct labour cost per hour | 7.25 | 7.25 | 7.25 | 7.25 | 7.25 |
| Total direct labor cost | 6314388.799 | 5051420.272 | 4051697.514 | 2748973.551 | 18166480.14 |
| Total of Product A and Product B | 13821898.43 | 8701751.515 | 9054719.789 | 8754281.843 | 40332651.57 |
