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

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 Hawk's manufacturing process, D Hawk'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 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 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:

B C D E F G H CamDrone K Sales Budget For the year ended December 31, 2020 CamDrone Schedule of Expected Cash Collections For the year ended December 31, 2020D E G M N CamDrone Production Budget For the year ended December 31, 2020 (all amounts in Units) January Feburary March April May June July August September October November December Expected Sales Add: Desired Ending Inventory of Finished Goods Total Units Needed Less: Beginning Inventory of Finished Goods Required Production 12 CamDrone Manufacturing Overhead Budget For the year ended December 31, 2020 20 January Feburary March April May June July August September October November December 21 Production in Units Variable overhead rate per unit 23 Variable manufacturing overhead Fixed manufacturing overhead: 25 Supervisor Salaries 26 Amortization of Plant & Equipment Insurance 28 Training and Development Property and Business Taxes Other Total fixed manufacturing OH Total manufacturing OH Less: Amortization of Plant & Equipment * Less: Prepaid Insurance ** Less: Prepaid Property and Business Taxes ** Cash disbursements for manufacturing OH * Amortization is a non-cash expense ** Prepaid expenses addressed as separate line items 39 in Cash BudgetB G M CamDrone N R Direct Materials Budget CHECK FIGURE: For the year ended December 31, 2020 Total Cost of Purchase of Direct Materials or February = $16,556 Units to be Produced Direct Materials Needed (kg/unit) Production Needs (kg) Add: Desired Ending Inventory of Direct Materials (kg) Total Needs (kg) Less: Beginning Inventory of Direct Materials (kg) Direct Materials to be Purchased (kg) Cost of Direct Materials (S/kg) Total Cost of Purchases of Direct Materials 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 rect Materials in February = $29,182 January Feburary March April May June July August September October November December YearCamDrone 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 YearA B C D E F G H CamDrone CHECK FIGURE: Ending Finished Goods Inventory Budget 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 kq Direct Labour hr Manufacturing Overhead (Variable and Fixed) unit Unit Product Cost 12 Budgeted Finished Goods Inventory Ending Finished Goods Inventory (units) Unit Product Costs (see above) Ending Finished Goods Inventory ($) Budgeted Cost of Goods Sold Beginning Finished Goods Inventory 9 Plus: Cost of Goods Manufactured 0 Less: Ending Finished Goods Inventory Budgeted Cost of Goods Sold CoC D E F G H M N Q R CamDrone Selling and Administrative Expense Budget CHECK FIGURE: For the year ended December 31, 2020 Total Cash Disbursements for September = $122,240 Jary Feburary March April May June July August September October November December YearB C D E F H K L M N P Q R CamDrone CHECK FIGURES: Cash Budget For the year ended December 31, 2020 Total Disbursements for June = $1,138,142 January Feburary March April May June July August Sentember October November December Ending Cash Balance for August = $54,021 Year Cash Balance (beginning) Line of Credit Balance for November = $3,690,000 Add: Collections from Customers Less Cash Disbursements: Direct Materials Direct Labour Manufacturing Overhead Selling and Administrative Expenses Tax Remittances Tax Balances Payable Prepaid Property and Business Taxes Prepaid Insurance Capital Asset Purchases Dividend Payment Total Disbursements Excess (Deficiency) of Cash Available Over Disbursements Financing: Borrowing (beginning of month) Interest Expense (paid monthly) Repayment (end of month) Total Financing Cash Balance (ending) Line of Credit Running Total (end of month)A B C D E CamDrone Budgeted Income Statement For the year ended December 31, 2020 Sales Less: Cost of Goods Sold Gross Margin Less: Selling & Administrative Expenses Net Operating In 10 Less: Interest Expense 11 Net Income Before Taxes (NIBT) 12 Less: Income Taxes (25%) 13 Net Income CHECK FIGURE: 14 15 CamDrone Net Income = $10,903 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 Less: Dividends Declared Retained Earnings (End of Year) CamDrone Budgeted Balance Sheet December 31, 2020 28 29 Assets 30 Cash Accounts Receivable 32 Inventory: Direct Materials $3 Inventory: Finished Good: Prepaid Property and Business Taxes Prepaid Insurance Capital Assets (net) Total Asset 38 Liabilities and Shareholders' Equity 39 Liabilities Accounts Payable Income Taxes Payable nk Loan Pa Total Liabilities Shareholders' Equity Capital Stock Retained Earnings Total Shareholders' Equity 48 Total Liabilities & Shareholders' Equity 49 CHECK FIGURE: 50 Total Liabilities & Shareholdres' Equity = $4,816,523ENTER ALL OF YOUR RAW DATA IN THIS WORKSHEET Try to organize your data into similar groupings (e.g., all the information related to sales) 40 42 43 44 45 46 17 DATA Sales & Collections Production & MOH DM DL End FG and COGS S&A Cash Projected Financial Statements Discussion Questions\fSupervisor'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 m \fCash $100 ,000 Accounts Receivable 1,098 ,899 Inventory: Direct Materials 3,591 Inventory: Finished Goods 11,640 Liabilities & Shareholders' Equity Accounts Payable $78,641 Income Taxes Payable 19,700 Capital Stock 500,000 Retained Earnings 1,2325 89 Total Liabilities and Shareholders' Equity 1 830 930 AutoSavE Lommzi u and at File Home Insert Page Layout Formulas Data Review View Help r3 Share D Comments _' AutoSum v 7 D [Em in v m a y @ 322 E 5 2r p f g? Copy V , FIII V , Paste we Conditional Format as Cell Insert Delete Format Sort 81 Find 8: Ideas Sensitivity I U V H~1 V 0a V A V V v g Formal Painter I E Me'ge B'Ceme' Formattllrg v Table v Styles v v v v 0 Clear V Fllter v Select v v Clipboard Alignment Style; Cell; Editing ideas Sensitivity A 3: Using data from parts 1 & 21 what recommendations, if any, would you make to t IS com with respect to managing its cash budget? V A A l B c l D l E l r r G | . 1 Question Your Answer Using data irom parts 1 dz 2, what recommendations. ir any. would you make to this company with respect to managing in cash budget? What additional recommendations, if any, might you make to this company in order to improve it: current rituationz no not neeenarily constrain your answer to include only those areas dealing with budgets. 'l'SEl PM E W\" 2020708711