D Hawk entered the market in early 2018. D Hawk manufactures camera drones, selling primarily to retailers.

D Hawk is pleased with its financial performance over its first few years of operations, optimistic to achieve continued financial success. For planning and control purposes the company utilizes a monthly master budget, which is usually developed at least three months in advance of the budget year. The company has a fiscal year ending December 31.

It is now Sept 15, 2019. You have been asked to prepare the Master Budget for the year ending December 31, 2020. In addition, you believe it is important to carry out sensitivity analysis and conduct additional analyses that will help management.

Requirements

1. Use the provided Excel template to prepare a monthly master budget for D Hawk for the year ending December 31, 2020, including the following schedules:

Sales Budget & Schedule of Expected Cash Collections

Production Budget & Manufacturing Overhead Budget

Direct Materials Budget & Schedule of Expected Cash Disbursements

Direct Labour Budget

Ending Finished Goods Inventory Budget

Selling and Administrative Expense Budget

Cash Budget

2. Prepare a budgeted Income Statement, budgeted Balance Sheet, and a budgeted Statement of Retained Earnings for the year ending December 31, 2020

3. Using data from parts 1 & 2, what recommendations, if any, would you make to this company with respect to managing its cash budget?

4. What additional recommendations, if any, might you make to this company in order to improve its current situation? Do not necessarily constrain your answer to include only those areas dealing with budgets.

Important Notes About Preparing Your Master Budget

1. Rounding calculations:

For calculation of COGS and ending Finished Goods Inventory, carry the MOH cost/unit to 4 decimal places

For calculations involving Direct Materials, round all kg to 2 decimal places

For calculations involving Direct Labour, round all hours to the nearest hour

Round all units of product to full units (e.g. Sales, Finished Goods Inventory, Production, etc.)

Aside from MOH cost/unit, round all other dollar amounts less than $100 to the nearest cent

Aside from MOH cost/unit, round all other dollar amounts greater than $1,000 to the nearest dollar

To round a number in excel use the formula =ROUND(value, number of decimals spots). For e.g., if I wanted to round the number 23.4567 to two decimal places, I would enter the following into excel o =ROUND(23.4567,2)

2. Do not hardcode your numbers. This means you enter all the necessary budget data into the worksheet entitled DATA, and use reference to these cells and formulas to complete the other tables and schedules. When grading your project, I will randomly audit cells to see if they are properly referenced, or if data has been hardcoded. If you hardcode data, you will automatically lose 15pts on the assignment.

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 D Hawks manufacturing process, D Hawks direct labour rate has averaged $36.00 per hour for 2019. This rate already includes the employers portion of employee benefits. A new collective agreement is being negotiated, with a 3% pay increase anticipated effective January 1, 2020.

5. Each D Hawk 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 Hawk finds it necessary to maintain a direct materials inventory balance equal to 40% of the following months production needs as a precaution against stock-outs.

6. Due to the similarity of the equipment in each of the production stages and the companys 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:

7. The fixed manufacturing overhead costs for 2019 are as follows:

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 months 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 receivable as at end of the 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. D Hawk pays for 20% of a months 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, D Hawk 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, D Hawk is planning to operate pop-up kiosks in various local retail centers during peak sales months (May-Aug, Nov-Dec) commencing Nov2019. D Hawk is budgeting $10,900 per month (including all related fees and taxes), payable at the beginning of each month, to set-up and operates 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. D Hawk 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 D Hawk 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. D Hawk 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:

Dividends are paid in the month they are declared.

March 2020 $125,000

June 2020 $175,000

September 2020 $325,000

December 2020 $325,000

25. The company is forecasting the following balances as at the end of the business day on December 31, 2019:

Plant & Equipment Maintenance Utilities Indirect Materials Other $73 45 37 20 $175 Supervisor's salary Amortization of Plant & Equipment Insurance Training & Development Property and Business Taxes Other $181,800 132,000 84,000 54,750 48,000 30,000 $530,550 Assets Cash Accounts Receivable Inventory: Direct Materials Inventory: Finished Goods Prepaid Property and Business Taxes Prepaid Insurance Capital Assets (Net) Total Assets $100,000 1,098,899 3,591 11,640 24,000 21,000 571,800 1,830,930 Liabilities & Shareholders' Equity Accounts Payable Income Taxes Payable Capital Stock Retained Earnings Total Liabilities and Shareholders' Equity $78,641 19,700 500,000 1,232,589 $1,830,930 B D E F G H - J A Name Box L OF YOUR RAW DATA IN THIS WORKSHEET 2 Try to organize your data into similar groupings (e.g., all the information related to sales) m 4 5 6 B 20 1 2 3 4. -6 -7 8 9 A B D E F H - 2 Cam Drone Sales Budget For the year ended December 31, 2020 3 4 5 6 bo 9 0 1 2 3 -4 CamDrone Schedule of Expected Cash Collections For the year ended December 31, 2020 5 -6 7 8 9 20 21 22 23 24 5 26 27 28 19 50 31 $2 D M 1 2 3 CamDrone Production Budget For the year ended December 31, 2020 (al amounts in Units) 5 January Feburary March April May June July August September October November December 9 6 7 Expected Sales & Add: Desired Ending Inventory of Finished Goods Tatal Units Needed 20 Less: Beginning Inventory of Finished Goods 11 Required Production 12 13 16 CamDrone Manufacturing Overhead Budget For the year ended December 31, 2020 March April May June 19 January Feburary July August September October November December 21 Production in Units 22 Variable overhead rate per unit 23 Variable manufacturing overhead 24 Rxed manufacturing overhead: 25 Supervisor Salaries 26 Amortization of Plant & Equipment 27 Insurance 28 Training and Development 29 Property and Business Taxes 30 Other 91 Total fixed manufacturing OH 32 Total manufacturing OH 33 Less: Amortization of Plant & Equipment Less: Prepaid Insurance ** 35 Less: Prepaid Property and Business Taxes ** 16 Cash disbursements for manufacturing OH 38 Atxartization is a non-cash exVIS ** Prepaid expenses addressed as separate line items 29 i Cash Budget -40 A B D H K L M P Q R S T U 2 Cam Drone Direct Materials Budget For the year ended December 31, 2020 CHECK FIGURE: Total Cost of Purchase of Direct Materials for February $16,556 3 4 5 6 Units to be Produced Direct Materials Needed (kg/unit) Production Needs (kg) 9 Add: Desired Ending Inventory of Direct Materials (kg) 10 Total Needs (kg) 1 Less: Beginning Inventory of Direct Materials (kg) 2 Direct Materials to be purchased (kg) 13 14 Cost of Direct Materials (S/kg) 5 Total Cost of Purchases of Direct Materials 16 7 18 9 CamDrone Schedule of Expected Cash Disbursement for Purchases of Direct Materials For the year ended December 31, 2020 CHECK FIGURE: Total Cash Disbursements for Purchases of Direct Materials in February = $29,182 January Feburary March April May June August September October November December Year 20 21 22 23 24 25 26 27 28 29 50 51 2 E H M 1 2 CHECK FIGURE: Cam Drone Direct Labour Budget For the year ended December 31, 2020 Total Direct Labor Cost for the Year = $2,129,319 3 4 5 6 7 January Feburary March April May June July August September October November December Year 00 10 11 12 13 14 15 16 17 A B D E F G - H CHECK FIGURE: Total Budgeted Cost of Goods Sold = $5,027,304 3 1 Cam Drone 2 Ending Finished Goods Inventory Budget For the year ended December 31, 2020 4 5 Quantity Cost/ unit 6 Production Cost (/unit) 7 Direct Materials kg 8 Direct Labour hr 9 Manufacturing Overhead (Variable and Fixed) unit LO Unit Product Cost Total 11 12 Budgeted Finished Goods Inventory 13 Ending Finished Goods Inventory (units) 14 Unit Product Costs (see above) 15 Ending Finished Goods Inventory ($) 16 17 Budgeted Cost of Goods Sold 18 Beginning Finished Goods Inventory 19 Plus: Cost of Goods Manufactured 20 Less: Ending Finished Goods Inventory 21 Budgeted Cost of Goods Sold 22 23 24 25 26 27 28 29 30 31 32 A D E F H J K L M N 0 0 R S 1 CHECK FIGURE: 2 Cam Drone Selling and Administrative Expense Budget For the year ended December 31, 2020 Total Cash Disbursements for September = $122,240 3 4 5 jary Feburary March April May June July August September October November December Year 6 7 8 9 10 11 12 A B D E F G H J K M 0 Q R S CHECK FIGURES: 2 Cam Drone Cash Budget For the year ended December 31, 2020 Total Disbursements for June = $1,138,142 3 4 Ending Cash Balance for August = $54,021 January Feburary March April May June August September October November December Year Line of Credit Balance for November = $3,690,000 6 Cash Balance (beginning) 7 Add: Collections from Customers 8 9 Less Cash Disbursements: 10 Direct Materials 11 Direct Labour 12 Manufacturing Overhead 13 Selling and Administrative Expenses 14 Tax Remittances 15 Tax Balances Payable 16 Prepaid Property and Business Taxes 17 Prepaid Insurance 18 Capital Asset Purchases 19 Dividend Payments 20 Total Disbursements 21 Excess (Deficiency) of Cash Available Over Disbursements 22 23 Financing: 24 Borrowing (beginning of month) 25 Interest Expense (paid monthly) 26 Repayment (end of month) 27 Total Financing 28 Cash Balance (ending) 29 30 31 Line of Credit Running Total (end of month) 32 33 34 A B D E CHECK FIGURE: Net Income $10,903 1 CamDrone 2 Budgeted Income Statement 3 For the year ended December 31, 2020 4 5 Sales 6 Less: Cost of Goods Sold 7 Gross Margin 8 Less: Selling & Administrative Expenses 9 Net Operating Income 10 Less: Interest Expense 11 Net Income Before Taxes (NIBT) 12 Less: Income Taxes (25%) 13 Net Income 14 15 CamDrone 16 Budgeted Statement of Retained Earnings 17 For the year ended December 31, 2020 18 19 Retained Earnings (Beginning of Year) 20 Plus: Net Income 21 22 Less: Dividends Declared 23 Retained Earnings (End of Year) 24 25 CamDrone 26 Budgeted Balance Sheet 27 December 31, 2020 28 29 Assets 30 Cash 31 Accounts Receivable 32 Inventory: Direct Materials 33 Inventory: Finished Goods 34 Prepaid Property and Business Taxes 35 Prepaid Insurance 36 Capital Assets (net) 37 Total Assets 38 Liabilities and Shareholders' Equity 39 Liabilities 40 Accounts Payable 41 Income Taxes Payable 42 Bank Loan Payable 43 Total Liabilities 44 Shareholders' Equity 45 Capital Stock 46 Retained Earnings 47 Total Shareholders' Equity 48 Total Liabilities & Shareholders' Equity 49 50 51 CHECK FIGURE: Total Liabilities & Shareholdres' Equity = $4,816,523 AutoSave C Comm210 Budget Project Student (2).xlsx - Excel Search A Mohammad Hossain - File Home Insert Page Layout Formulas Data Review View Help Share Comments Times 12 ~A A 89 Wrap Text 48 O . NI HEX Insert Delete format 4 X Out [Copy Format Painter Clipboard General $%, Peste Ideas BIU- === Merge Center AutoSum rill Sort & Find & Clear Filter Select Eding LA Conditional Format as Cell Formatting Table Styles Swiss Sensitivity Font Alignment Numi Cels deas Sensitivity AZ Using data from parts 1 & 2, what recommendations, if any, would you make to this company with respect to managing its cash budget? E Your Answer Question Using data from parts 1&2, what recommendations, if any, would you make to this company with respect to managing its cash budget? Whal additional recommendations, if any, migll you make to this company in order to improve its current situation? Do not uccessarily coustrain your answer to include only those areas dealing with budgets. 3 Production on & MOH DM DL End FG and COGS S&A Cash Projected Financial Statements Discussion Questions Ready 75% Type here to search o BE . AN ENG 10:59 PM 2020-08-11 Plant & Equipment Maintenance Utilities Indirect Materials Other $73 45 37 20 $175 Supervisor's salary Amortization of Plant & Equipment Insurance Training & Development Property and Business Taxes Other $181,800 132,000 84,000 54,750 48,000 30,000 $530,550 Assets Cash Accounts Receivable Inventory: Direct Materials Inventory: Finished Goods Prepaid Property and Business Taxes Prepaid Insurance Capital Assets (Net) Total Assets $100,000 1,098,899 3,591 11,640 24,000 21,000 571,800 1,830,930 Liabilities & Shareholders' Equity Accounts Payable Income Taxes Payable Capital Stock Retained Earnings Total Liabilities and Shareholders' Equity $78,641 19,700 500,000 1,232,589 $1,830,930 B D E F G H - J A Name Box L OF YOUR RAW DATA IN THIS WORKSHEET 2 Try to organize your data into similar groupings (e.g., all the information related to sales) m 4 5 6 B 20 1 2 3 4. -6 -7 8 9 A B D E F H - 2 Cam Drone Sales Budget For the year ended December 31, 2020 3 4 5 6 bo 9 0 1 2 3 -4 CamDrone Schedule of Expected Cash Collections For the year ended December 31, 2020 5 -6 7 8 9 20 21 22 23 24 5 26 27 28 19 50 31 $2 D M 1 2 3 CamDrone Production Budget For the year ended December 31, 2020 (al amounts in Units) 5 January Feburary March April May June July August September October November December 9 6 7 Expected Sales & Add: Desired Ending Inventory of Finished Goods Tatal Units Needed 20 Less: Beginning Inventory of Finished Goods 11 Required Production 12 13 16 CamDrone Manufacturing Overhead Budget For the year ended December 31, 2020 March April May June 19 January Feburary July August September October November December 21 Production in Units 22 Variable overhead rate per unit 23 Variable manufacturing overhead 24 Rxed manufacturing overhead: 25 Supervisor Salaries 26 Amortization of Plant & Equipment 27 Insurance 28 Training and Development 29 Property and Business Taxes 30 Other 91 Total fixed manufacturing OH 32 Total manufacturing OH 33 Less: Amortization of Plant & Equipment Less: Prepaid Insurance ** 35 Less: Prepaid Property and Business Taxes ** 16 Cash disbursements for manufacturing OH 38 Atxartization is a non-cash exVIS ** Prepaid expenses addressed as separate line items 29 i Cash Budget -40 A B D H K L M P Q R S T U 2 Cam Drone Direct Materials Budget For the year ended December 31, 2020 CHECK FIGURE: Total Cost of Purchase of Direct Materials for February $16,556 3 4 5 6 Units to be Produced Direct Materials Needed (kg/unit) Production Needs (kg) 9 Add: Desired Ending Inventory of Direct Materials (kg) 10 Total Needs (kg) 1 Less: Beginning Inventory of Direct Materials (kg) 2 Direct Materials to be purchased (kg) 13 14 Cost of Direct Materials (S/kg) 5 Total Cost of Purchases of Direct Materials 16 7 18 9 CamDrone Schedule of Expected Cash Disbursement for Purchases of Direct Materials For the year ended December 31, 2020 CHECK FIGURE: Total Cash Disbursements for Purchases of Direct Materials in February = $29,182 January Feburary March April May June August September October November December Year 20 21 22 23 24 25 26 27 28 29 50 51 2 E H M 1 2 CHECK FIGURE: Cam Drone Direct Labour Budget For the year ended December 31, 2020 Total Direct Labor Cost for the Year = $2,129,319 3 4 5 6 7 January Feburary March April May June July August September October November December Year 00 10 11 12 13 14 15 16 17 A B D E F G - H CHECK FIGURE: Total Budgeted Cost of Goods Sold = $5,027,304 3 1 Cam Drone 2 Ending Finished Goods Inventory Budget For the year ended December 31, 2020 4 5 Quantity Cost/ unit 6 Production Cost (/unit) 7 Direct Materials kg 8 Direct Labour hr 9 Manufacturing Overhead (Variable and Fixed) unit LO Unit Product Cost Total 11 12 Budgeted Finished Goods Inventory 13 Ending Finished Goods Inventory (units) 14 Unit Product Costs (see above) 15 Ending Finished Goods Inventory ($) 16 17 Budgeted Cost of Goods Sold 18 Beginning Finished Goods Inventory 19 Plus: Cost of Goods Manufactured 20 Less: Ending Finished Goods Inventory 21 Budgeted Cost of Goods Sold 22 23 24 25 26 27 28 29 30 31 32 A D E F H J K L M N 0 0 R S 1 CHECK FIGURE: 2 Cam Drone Selling and Administrative Expense Budget For the year ended December 31, 2020 Total Cash Disbursements for September = $122,240 3 4 5 jary Feburary March April May June July August September October November December Year 6 7 8 9 10 11 12 A B D E F G H J K M 0 Q R S CHECK FIGURES: 2 Cam Drone Cash Budget For the year ended December 31, 2020 Total Disbursements for June = $1,138,142 3 4 Ending Cash Balance for August = $54,021 January Feburary March April May June August September October November December Year Line of Credit Balance for November = $3,690,000 6 Cash Balance (beginning) 7 Add: Collections from Customers 8 9 Less Cash Disbursements: 10 Direct Materials 11 Direct Labour 12 Manufacturing Overhead 13 Selling and Administrative Expenses 14 Tax Remittances 15 Tax Balances Payable 16 Prepaid Property and Business Taxes 17 Prepaid Insurance 18 Capital Asset Purchases 19 Dividend Payments 20 Total Disbursements 21 Excess (Deficiency) of Cash Available Over Disbursements 22 23 Financing: 24 Borrowing (beginning of month) 25 Interest Expense (paid monthly) 26 Repayment (end of month) 27 Total Financing 28 Cash Balance (ending) 29 30 31 Line of Credit Running Total (end of month) 32 33 34 A B D E CHECK FIGURE: Net Income $10,903 1 CamDrone 2 Budgeted Income Statement 3 For the year ended December 31, 2020 4 5 Sales 6 Less: Cost of Goods Sold 7 Gross Margin 8 Less: Selling & Administrative Expenses 9 Net Operating Income 10 Less: Interest Expense 11 Net Income Before Taxes (NIBT) 12 Less: Income Taxes (25%) 13 Net Income 14 15 CamDrone 16 Budgeted Statement of Retained Earnings 17 For the year ended December 31, 2020 18 19 Retained Earnings (Beginning of Year) 20 Plus: Net Income 21 22 Less: Dividends Declared 23 Retained Earnings (End of Year) 24 25 CamDrone 26 Budgeted Balance Sheet 27 December 31, 2020 28 29 Assets 30 Cash 31 Accounts Receivable 32 Inventory: Direct Materials 33 Inventory: Finished Goods 34 Prepaid Property and Business Taxes 35 Prepaid Insurance 36 Capital Assets (net) 37 Total Assets 38 Liabilities and Shareholders' Equity 39 Liabilities 40 Accounts Payable 41 Income Taxes Payable 42 Bank Loan Payable 43 Total Liabilities 44 Shareholders' Equity 45 Capital Stock 46 Retained Earnings 47 Total Shareholders' Equity 48 Total Liabilities & Shareholders' Equity 49 50 51 CHECK FIGURE: Total Liabilities & Shareholdres' Equity = $4,816,523 AutoSave C Comm210 Budget Project Student (2).xlsx - Excel Search A Mohammad Hossain - File Home Insert Page Layout Formulas Data Review View Help Share Comments Times 12 ~A A 89 Wrap Text 48 O . NI HEX Insert Delete format 4 X Out [Copy Format Painter Clipboard General $%, Peste Ideas BIU- === Merge Center AutoSum rill Sort & Find & Clear Filter Select Eding LA Conditional Format as Cell Formatting Table Styles Swiss Sensitivity Font Alignment Numi Cels deas Sensitivity AZ Using data from parts 1 & 2, what recommendations, if any, would you make to this company with respect to managing its cash budget? E Your Answer Question Using data from parts 1&2, what recommendations, if any, would you make to this company with respect to managing its cash budget? Whal additional recommendations, if any, migll you make to this company in order to improve its current situation? Do not uccessarily coustrain your answer to include only those areas dealing with budgets. 3 Production on & MOH DM DL End FG and COGS S&A Cash Projected Financial Statements Discussion Questions Ready 75% Type here to search o BE . AN ENG 10:59 PM 2020-08-11