Budget Information Based on your discussions with the various departments throughout the company, you have collected the following relevant information for preparing the budget: Sales 1. The marketing department is forecasting the following annual sales: For the year ended December 31, 2019: 7,000 units at $925 each* For the year ended December 31, 2020: 8,500 units at $925 each . For the year ended December 31, 2021: 11,000 units at $925 each *Expected sales for the year ended December 31, 2019 were based on actual sales to date and budgeted sales for the duration of the year. 2. Peak months for sales generally correspond with summer weather and gift-giving holidays. History shows that January is the slowest month, with only 1% of annual sales, followed closely behind by Feb-April with 2% of annual sales for each month. Sales spike during summer months with May, June, July, and August contributing 12%, 15%, 12%, and 10% of annual sales respectively. With the back-to-school focus in September, there is a significant dip in camera drone sales to 3% of annual sales. As Christmas shopping picks up momentum, winter sales increase to 8% in October, 13% in November, and then peak at 20% in December. This pattern of sales is not expected to change in the next two years. Manufacturing Costs and Inventory 3. Each camera drones spends a total of 6.75 hours in production. 4. Due to the highly technical nature of CamDrone's manufacturing process, CamDrone's direct labour rate has averaged $36.00 per hour for 2019. This rate already includes the employer's portion of employee benefits. A new collective agreement is being negotiated, with a 3% pay increase anticipated effective January 1, 2020. 5. Each CamDrone requires 1.55kg of direct materials. During 2019, the average cost of direct materials was $61/kg. The supplier of the direct materials tends to be somewhat erratic, so CamDrone finds it necessary to maintain a direct materials inventory balance equal to 40% of the following month's production needs as a precaution against stock-outs. 6. Due to the similarity of the equipment in each of the production stages and the company's concentration on a single product, manufacturing overhead is allocated based on volume (i.e. the units produced). The variable manufacturing overhead rate for 2019 is $175/unit, consisting of: Plant & Equipment Maintenance $73 Utilities 45 Indirect Materials 37 Other 20 $1757. The fixed manufacturing overhead costs for 2019 are as follows: Supervisor's salary $181,800 Amortization of Plant & Equipment 132,000 Insurance 84,000 Training & Development 54,750 Property and Business Taxes 48,000 Other 30.000 $530.550 Amortization is calculated using the straight-line method, with no amortization calculated in the year capital assets are acquired. 8. Aside from amortization, all other manufacturing costs are expected to increase by 3% in 2020 due to inflation. 9. From previous experience, management has determined that an ending finished goods inventory equal to 25% of the next month's sales is required to efficiently meet customer demands. Collections Pattern 10. Sales are on a cash and credit basis, with 49% collected during the month of the sale, 33% the following month, and 15% the month thereafter. There are no early payment discounts for customers. Bad debt expense (amounts considered uncollectible) account for 3% of sales. 11. Based on the collection pattern described above, accounts payable as at end of business day on December 31, 2019 is projected to be $1,098,899 arising from the following estimates: Sales (November 2019) $1,170,000 Sales (December 2019) 1,800,000 Payments Pattern 12. CamDrone pays for 20% of a month's purchases of direct materials in the month of purchase, 50% in the following month and the remaining 30% two months after the month of purchase. There are no early payment discounts offered by suppliers. 13. Based on the payment pattern described above, accounts payable as at end of business day on December 31, 2019 is projected to be $78,641 arising from the following estimates: Direct Material Purchases (November 2019) $95,888 Direct Material Purchases (December 2019) 62,334 14. All payroll costs are paid in the period in which they are incurred. 15. The property and business taxes, paid at the beginning of July each year, apply to the following 12-month period. Any increases for inflation on property and business taxes do not take effect until the beginning of July each year.16. Annual insurance premiums, paid at the beginning of April each year, apply to the following 12-month period. Any increases for inflation on insurance premiums do not take effect until the beginning of April each year. 17. Fixed manufacturing overhead costs are incurred evenly over the year and "cash- related" amounts are paid as incurred. 18. Selling and administrative expenses are paid in the month in which they occur. Other 19. Anticipating a significant increase in customer demand and market share over the next few years, CamDrone is planning a significant expansion involving acquiring additional manufacturing equipment for $3,000,000 cash. Half of this amount is to be paid to the equipment supplier in July 2020, with the remainder to be paid in October 2020. 20. Selling and administrative expenses are known to be a mixed cost; however, there is a lot of uncertainty about the portion that is fixed. Based on prior year experience: Lowest level of monthly sales: 80 units .. Total Operating Expenses: $87,270* Highest level of monthly sales: 1,600 units .. Total Operating Expenses: $360,780* * excluding bad debts and amounts described below These expenses are expected to increase by 3% in 2020 due to inflation. 21. To secure and grow its market share, CamDrone is planning to operate "pop-up" kiosks in various local retail centers during peak sales months (May-Aug, Nov-Dec) commencing Nov2019. CamDrone is budgeting $10,900 per month (including all related fees and taxes), payable at the beginning of each month, to set-up and operate these kiosks. The cost to set-up and operate "pop-up" kiosks will be in addition to the normal selling and administrative expenses described above and is not expected to increase during 2020. 22. Income tax expense is estimated to be 25% of net income. CamDrone makes monthly income tax installment payments of $250 and pays all outstanding income taxes (in excess of installment payments) in March of the following year. As such, any outstanding income tax balance for the year ended December 31, 2019 it will be paid in March 2020. 23. An arrangement has been made with the local bank that if CamDrone maintains a minimum balance of $50,000 in their bank account, they will be given a line of credit at a preferred rate of 3% per annum (0.25% per month). All borrowing is considered to happen on the first day of the month, repayments are on the last day of the month. All borrowings and repayments from the bank should be in multiples of $10,000 and interest must be paid at the end of each month. Interest is calculated on the balance at the beginning of the month, which includes any amounts borrowed that month. 24. CamDrone has a policy of paying dividends at the end of each quarter. The President tells you that the Board of Directors is planning on declaring dividends as follows: March 2020 $125,000 June 2020 175,000 September 2020 325,000 December 2020 325,000 Dividends are paid in the month they are declared.25. The company is forecasting the following balances as at the end of business day on December 31, 2019: Assets Cash $100.000 Accounts Receivable 1,098.899 Inventory: Direct Materials 3,591 Inventory: Finished Goods 11.640 Prepaid Property and Business Taxes 24.000 Prepaid Insurance 21,000 Capital Assets (Net) 571.800 Total Assets 1.830.930 Liabilities & Shareholders' Equity Accounts Payable $78.641 Income Taxes Payable 19,700 Capital Stock 500.000 Retained Earnings 1.232.589 Total Liabilities and Shareholders' Equity S1 830 93013 * X V fx B C D E F G H 1 K L M N 0 Q CamDrone Direct Labour Budget CHECK FIGURE: For the year ended December 31, 2020 Total Direct Labor Cost for the Year = $2,129,319 January Feburary March April May June July August September October November December Year Units Required to be Produced 107 170 170 382 1084 1211 976 701 361 78 1254 1303 B507 Direct Labour cost per Unit 250.30 250.30 250.30 250.30 250.30 250.30 250.30 250.30 250.30 250.30 250.30 250.30 250.30 Direct Labour Cost 26782.31 42551.34 42551.34 95615.36 271327.35 303115.71 244795.34 175461.69 90359.02 196737.36 313878.69 326143.49 212931915 X Vfx D E G L M N P R CamDrone CHECK FIGURE: Direct Materials Budget Total Cost of Purchase of Direct For the year ended December 31, 2020 Materials for February = $16,556 January February March April May June July August September October November December Units to be Produced 107 170 170 382 1,084 1,211 978 701 361 786 1,254 1,303 Direct Materials Needed (kg/unit) 1.55 1.55 1.55 1.55 1.55 1.55 1.55 1.55 1.55 1.55 1.55 1.55 Production Needs (kg) 165.85 263.50 263.50 592.10 ,680.20 1,877.05 1,515.90 1,086.55 559.55 1,218.30 ,943.70 2,019.65 Add: Desired Ending Inventory of Direct Material (kg) 105.40 105.40 236.84 672.08 750.82 605.96 434.62 223.82 487.32 777.48 807.86 212.35 Total Needs (kg) 271.25 368.90 500.34 1,264.18 2,431.02 2,483.01 1,950.52 1,310.37 1,046.87 1,995.78 2,751.56 2,232.00 Less: Beginning Inventory of Direct Materials (kg) 64.20 102.00 102.00 229.20 650.40 726.60 586.80 420.60 216.60 471.60 752.40 781.80 Direct Materials to be Purchased (kg) 207.09 266.90 198.34 1,034.98 1,780.62 1,756.41 1,363.72 889.77 830.27 1,524.18 1,999.16 1,450.20 Cost of Direct Materials ($/kg) 62.03 62.03 62.03 62.03 62.03 62.03 62.03 62.03 62.03 62.03 2.03 62.03 Total Cost of Purchases of Direct Materials 12,843 16,556 24,709 64,201 110,454 108,952 84,593 55,193 51,502 94,546 124,010 89,957 CamDrone Schedule of Expected Cash Disbursement for Purchases of Direct Materials CHECK FIGURE: Total Cash Disbursements for For the year ended December 31, 2020 Purchases of Direct Materials in February = $29,182 January Feburary March April May June July August September October November December Year Direct Materials Amount 12,843 $ 16,556 $ 24,709 $ 64,201 $ 110,454 $ 108,952 $ 84,593 $ 55,193 $ 51,502 $ 94,546 $ 124,010 $ 89,957 -$ 837,518 Payment 2,568.66 3,311.21 4,941.89 $ 12,840.17 $ 22,090.73 $ 21,790.37 16,918.58 11,038.66 10,300.50 18,909.28 $ 24,801.98 17,991.47 149,512.04 6,421.66 8,278.04 $ 12,354.71 12,100.42 55,226.82 54,475.93 42,296.46 27,596.66 25,751.24 $ 62,004.95 $ 373,780.09 4,966.82 7,412.83 $ 33,136.09$ 32,685.56 25,377.87 16,558.00 47,273.20 $ 3,852.99 19,260.25 15,450.74 $ 28,363.92 $ 187,065.09 Payment of Materials 43625.37 43625.37 Payment of Materials 49821.75 19449.13 69270.88 Total Cash Disbursement 96,015.78 $ 29,182.00 $ 17,072.92 $ 30,161.71 $ 61,603.98 $ 96,277.45 $ 104,530.61 $ 86,020.68 $ 63,275.03 $ 61,218.52 $ 87,525.93 $ 108,360.34 $ 841,244.94F21 x v fx A C D E F G H K CamDrone Ending Finished Goods Inventory Budget CHECK FIGURE: Total Budgeted Cost of Goods Sold = $5,027,304 For the year ended December 31, 2020 Quantity Cost/unit Total Production Cost (/unit) Direct Materials 1.55 kq 40 62.03 837,518 Direct Labour 37.08 hr $ 250.30 2,129,319 Manufacturing Overhead (Variable and Fixed) 8,500 unit $ 244.22 2,075,897 LO Unit Product Cost 8500 593.26 3 5,042,734 11 Budgeted Finished Goods Inventory 3 Ending Finished Goods Inventory (units) 2,131 14 Unit Product Costs (see above) 593 Ending Finished Goods Inventory ($) 1,264,237 .6 17 Budgeted Cost of Goods Sold 18 Beginning Finished Goods Inventory 1,261,864 .9 Plus: Cost of Goods Manufactured 5,029,677 20 Less: Ending Finished Goods Inventory 1,264,237 Budgeted Cost of Goods Sold 5,027,30418 X V fx =E7*25% D E G L M N Cam Drone Production Budget CHECK FIGURES: For the year ended December 31, 2020 Required Production for October = 786 (all amounts in Units) January Feburary March May July August September October Expected Sales November December 85 170 170 170 1,020 1,275 850 Add: Desired Ending Inventory of Finished Goods 255 580 8,500 43 43 43 255 319 255 213 64 170 276 425 Total Units Needed 128 28 2,131 213 213 425 1,339 1,530 1,233 914 425 956 Less: Beginning Inventory of Finished Goods (255) 10,631 (21) (319) (255) (213) (64) (170) Required Production (276) (425) (2,127) 107 382 1,084 1,211 978 701 786 1,254 1,303 8,507 CamDrone CHECK FIGURES: Manufacturing Overhead Budget For the year ended December 31, 2020 Total Cash Discursements for Manufacturing Ovearhead In July = $199,164 January Production in Units Feburary March April May June July August September October 107 November Year Variable overhead rate per unit 170 December 170 382 180.25 180.25 180.25 180.25 180.25 1,211 978 180.25 180.25 701 180.25 361 786 180.25 1,254 1,303 180.25 Variable manufacturing overhead 180.25 30.643 195.391 180.25 30.643 68.856 180.25 126.355 Fixed manufacturing overhead: 55.070 141.677 234.866 Supervisor Salaries 15,605 Amortization of Plant & Equipment 11,000 15,605 15,605 15,605 11,000 15,605 11,000 15,605 15,605 15,605 11,000 15,605 11,000 11,000 15,605 7,210 7.210 11,000 15,605 187,257 Insurance 7.210 11,000 11,000 7.210 32,000 7.210 Training and Development 4699 7.210 7.210 4699 4699 4699 4699 86,520 4699 4699 4699 4699 4690 Property and Business Taxes 4,120 4699 4699 4,120 56,390 4,120 4,120 Other 2,575 4,120 4,120 4,120 4,120 2,575 4,120 2,575 4,120 Total fixed manufacturing OH 2,575 2,575 4,120 4,120 2,575 2,575 2,575 2,575 2,575 2,575 2,575 45,209 45,209 15,209 45.209 45,209 45,209 45,209 Total manufacturing OH 54,496 75,852 75,852 45,209 45.209 114,065 45,209 45,209 45.209 Less: Amortization of Plant & Equipment * 11,000) (11,000) 7,210 (11,000) 240,600 263,492 542.507 Less: Prepaid Insurance ** (7,210) (11,000 221,494 (7,210) (11,000) (11,000) (11,000) 171,564 (11,000) 110,279 186,886 271,243 280,075 (11,000) (11,000) (11,000) 2,075,897 (7,210 (7,210 (7,210 (11,000) (132,000) Less: Prepaid Property and Business Taxes ** (7,210 (4,120) (4,120) 42,166 $ (4,120) (7,210 (4,120) (7,210 4,120) 4,120) (7,210) (7,210 (4,120 4,120) (4,120) (7,210) (86,520) 53,522 53,522 91,735 (4,120) 241,162 4,120) Cash disbursements for manufacturing OH 218,270 199,164 (4,120) (49,440) 149,234 87,949 164,556 $ 248,913 257,745 1,807,938 * Amortization is a non-cash expense ** Prepaid expenses addressed as separate line items in Cash Budget8 X V fx D G M N 0 P CamDrone Sales Budget CHECK FIGURES: For the year ended December 31, 2020 Total Budgetd Sales for May = $943,500 Units Sold January February March April Total Cash Colletions for November = $743,792 May June July August September October November Price Per Unit 85 170 170 925 170 December 1020 1020 925 275 925 850 255 680 925 1 105 1700 925 925 925 Sales ($) 925 925 925 925 925 78625 157250 157250 157250 94350 1179375 943500 786250 235875 629000 1022125 1572500 CamDrone Schedule of Expected Cash Collections For the year ended December 31, 2020 January February Amount Collected 175500 March April May June July August September October November December Amount Collected 594000 270000 Amount 38526.25 77052.5 77052.5 77052.5 462315 577893.75 462315 385262.5 25946.25 51892.5 308210 11793.75 51892.5 311355 1 15578.75 500841.25 770525 23587.5 51892.5 23587.5 389193.75 311355 259462.5 372998.75 23587.5 207570 Total Cash Collection 140738.75 141525 176906.25 117937.5 337301.25 308026.25 912836.25 993033.75 141525 77838.75 152532.5 873523.75 35381.25 94350 537795 516566.25 503986.25 743792.5 1202176.25Formatting as Table Styles Format Filter Select 126 x v fx B C D E F G H 1 J K L M N 0 P Q R S CamDrone CHECK FIGURE: Selling and Administrative Expense Budget For the year ended December 31, 2020 Total Cash Disbursements for September = $122,240 January Feburary March April May June July August September October November December Year Budgeted Sales 85 170 170 170 1,020 1,275 1,020 850 255 680 1,105 1,700 8,500 Variable Selling and Administrative Expense 345.22 345.22 345.22 345.22 345.22 180.25 345.22 345.22 180.25 345.22 345.22 345.22 345.22 Total Budgeted Variable Selling and Administrative Expenses 29,343.70 58,687.40 58,687.40 58,687.40 352,124 229,818.75 352,124 293,437.00 45,963.75 234,750 381, 468.10 586,874 2,681,966 Fixed Selling and Administrative Expenses: Supervisors Salary 15,605 15,605 15,605 15,605 15,605 15,605 15,605 15,605 15,605 15,605 15,605 15,605 187,260 Insurance 7,210 7,210 7,210 7,210 7,210 7,210 7,210 7,210 7,210 7,210 7,210 7,210 86.520 Training and Development 4,699 4,699 4,699 4,699 4,699 4,699 4,699 4,699 4,699 4,699 4,699 4,699 56,388 Property and Business Taxes 4,120 4,120 4,120 4,120 4,120 4,120 4,120 4,120 4,120 4,120 4,120 4,120 19.440 Other 2,57 2,575 2,575 2,575 2,575 2,575 2,575 2,575 2,575 2,575 2,575 2,575 30,90 Amortization 11,000 11,000 11,000 11,000 11,000 11,000 11,000 11,000 11,000 11,000 11,000 11,000 132,000 Total Budgeted Fixed Selling and Administrative Expenses 45,209 45,209 45,209 45,209 45,209 45,209 45,209 45,209 45,209 45,209 45,209 45,209 542,508 Total Budgeted Selling and Administrative Expenses 74,552.70 103,896 103,896 103,896 397,333 275,028 397,333 338,646 91,173 279,959 426,677 632,083 3,224,473.90 Less: Amortization 11,000 11,000 11,000 11,000 11,000 11,000 11,000 11,000 11,000 11,000 11,000 11,000 132,000 Cash Disbursements for Selling and Administrative Expense 63,552 92,896 92,896 92,896 386,333 264,028 386,333 327,646 122,240 268,959 415,677 621,083 3,134,539