Comm 210 Summer 2020 - Master Budget Project

Overview

As part of the continued advancement of technology, a drone camera market has emerged in

recent years. The drone camera market has been growing as more photography enthusiasts have

begun adopted this high-tech approach to capturing still images and video using remotely

controlled devices. Eager to capture a share of this growing market, CamDrone entered the

market in early 2018. CamDrone manufactures camera drones, selling primarily to retailers.

CamDrone 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 a sensitivity analysis and

conduct additional analyses that will help management.

Assignment Requirements (total of 150pts)

1. Use the provided Excel template to prepare a monthly master budget for CamDrone for the

year ending December 31, 2020, including the following schedules:

Sales Budget & Schedule of Expected Cash Collections (20pts)

Production Budget & Manufacturing Overhead Budget (20pts)

Direct Materials Budget & Schedule of Expected Cash Disbursements (20pts)

Direct Labour Budget (10pts)

Ending Finished Goods Inventory Budget (10pts)

Selling and Administrative Expense Budget (10pts)

Cash Budget (10pts)

2. Prepare a budgeted Income Statement, budgeted Balance Sheet, and a budgeted Statement of

Retained Earnings for the year ending December 31, 2020 (30pts)

3. Using data from parts 1 & 2, what recommendations, if any, would you make to this

company with respect to managing its cash budget? (10pts)

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. (10pts)

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 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

$175

7. 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 $1,830,930

1. Use the provided Excel template to a monthly master budget for CamDrone for the

year ending December 31, 2020, including the following schedules:

Sales Budget & Schedule of Expected Cash Collections (20pts)

Production Budget & Manufacturing Overhead Budget (20pts)

Direct Materials Budget & Schedule of Expected Cash Disbursements (20pts)

Direct Labour Budget (10pts)

Ending Finished Goods Inventory Budget (10pts)

Selling and Administrative Expense Budget (10pts)

Cash Budget (10pts)

2. a budgeted Income Statement, budgeted Balance Sheet, and a budgeted Statement of

Retained Earnings for the year ending December 31, 2020 (30pts)

3. Using data from parts 1 & 2, what recommendations, if any, would you make to this

company with respect to managing its cash budget? (10pts)

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. (10pts)