HK’s marketing director is concerned about having the correct number of dealers in the areas where sales are expected to occur for the new motor vessels. Based on his research, he has forecasted the percentage of sales that will come from each major city in eastern Canada, as follows:

Toronto 10%

Hamilton 15%

Kingston 30%

Halifax 30%

Montreal 15%

He has also prepared a list of current dealers by city using Excel (see file: Prep-COF-ASN02-Q7-Power BI Data).

Required:

a) To assist HK’s marketing director with his analysis, create a map visualization of the current dealer locations using Power BI. (2 marks)

b) From the current map, which location has the highest number of dealers? The lowest number of dealers? (2 marks)

c) Given the information on the sales forecast, is the current number of dealers at each location sufficient to meet the sales forecast demands? If not, which cities appear to be underserviced by the current dealers and might require new dealers be established? (4 marks)

Background

Dealer	Location
AC2100	Halifax, Canada
AC2351	Toronto, Canada
AD3499	Halifax, Canada
AW1559	Hamilton, Canada
BB1130	Toronto, Canada
BH3490	Kingston, Canada
BM2100	Montreal, Canada
CD1004	Halifax, Canada
CK2111	Toronto, Canada
DB2771	Hamilton, Canada
DB4567	Hamilton, Canada
DE2107	Montreal, Canada
DF1922	Montreal, Canada
EF2390	Halifax, Canada
EM3441	Montreal, Canada
EY2991	Toronto, Canada
FT2188	Toronto, Canada
GQ4671	Montreal, Canada
GS1203	Toronto, Canada
JS2115	Toronto, Canada
KA3913	Montreal, Canada
KD2351	Montreal, Canada
KT2363	Toronto, Canada
KW3159	Kingston, Canada
LK4002	Halifax, Canada
LL2733	Hamilton, Canada
LN1668	Kingston, Canada
LP1088	Kingston, Canada
LP2905	Toronto, Canada
LT3192	Kingston, Canada
LV3012	Hamilton, Canada
LW1030	Toronto, Canada
MC2567	Kingston, Canada
MM2333	Toronto, Canada
MV3331	Hamilton, Canada
NX2950	Kingston, Canada
PD1168	Kingston, Canada
PS3005	Montreal, Canada
QS2611	Toronto, Canada
RE2955	Kingston, Canada
RE3291	Hamilton, Canada
RF2562	Montreal, Canada
SP2667	Kingston, Canada
SY1232	Toronto, Canada
TF3122	Montreal, Canada
TY3141	Montreal, Canada
WA1258	Toronto, Canada
WB2321	Toronto, Canada
WQ2961	Toronto, Canada
WV1266	Toronto, Canada

Heavy Keele Ltd. (HK) has a long-standing reputation as a manufacturer of quality sailboats. HK currently produces two different models of sailboat from a single production facility — the HK41 and the HK49. The past several years have seen a 25% decline in demand for HK’s sailboats and a significant increase in interest within the boating community for motor vessels. Given this shift in attitudes, HK is now considering a proposal to introduce a motor vessel into its product line — the HKMV55.

If the proposal is accepted, senior management has decided to restructure the firm into two separate divisions for operational purposes — the Sailboat division and the Motor Vessel division. Under the proposal, all current personnel will remain with the Sailboat division and the senior management team will remain unchanged. For accounting purposes, however, the $2 million annual cost of the senior management team will be allocated equally between the two divisions. HK will then seek an entire new management team to oversee the Motor Vessel division. Management has also decided that the new division should operate out of its own production facility. It can be built on the block of land adjacent to the current facility that HK already owns.

Senior management has decided that the appropriate planning horizon for the proposed new Motor Vessel division is 10 years. You work in the controller’s office of HK and have been asked to perform a series of analyses on this proposal. To facilitate your analysis, you have been provided with the Motor Vessel division’s projected income statements over the next 10 years, as well as the following information.

Capital expenditures

• The block of land on which the new production facility will be built was purchased by HK three years ago at a cost of $2.5 million. It has a current market value of $3 million, and it is expected that the value of the land will remain at $3 million when the project is complete.

• Management expects that the production facility will cost $10 million to build. It has an estimated useful life of 20 years and will be depreciated on a straight-line basis to an estimated salvage value of $1 million for accounting purposes. It belongs to an asset class with a CCA rate of 7.5%. At the end of the 10-year planning horizon, the facility will have an estimated market value of $3 million. The building qualifies for the Accelerated Investment Incentive, and 1.5 times the CCA can be taken in the year of acquisition. At the end of the planning horizon, assume that there is still a positive balance remaining in the class after the deduction of the proceeds.

• The new equipment required for the production of the HKMV55 will cost $7 million and has an estimated useful life of 10 years. For accounting purposes, this equipment will also be depreciated on a straight-line basis. It belongs to an asset class with a CCA rate of 10% and has an estimated salvage value of $500,000 at the end of its useful life. The equipment qualifies for the Accelerated Investment Incentive, and 1.5 times the CCA can be taken in the year of acquisition. At the end of the planning horizon, assume that there is still a positive balance remaining in the class after the deduction of the proceeds.

• The new Motor Vessel division requires an initial investment in net working capital of $750,000.

Operating revenues and expenses, and working capital accounts

Data from the divisional pro forma operating income statements (see Appendix):

• Gross revenues are projected to be $12.5 million in the first three years of operation and $18 million from Year 4 onward.

• Cost of goods sold (COGS) is expected to be 56% of sales in the first three years and then decline to 50% of sales from Year 4 onward.

• The general and administrative costs are expected to be constant at $2.95 million per year over the 10-year planning horizon. Administrative costs include the allocation of senior management costs; the remainder of these costs relate directly to the new Motor Vessel division and are paid in cash when due.

• HK requires its customers to make a 15% deposit at the time of order and pay the balance at the time of delivery. The average lag between the time of order and delivery is two months. Sales occur uniformly throughout the year.

• HK has a policy of keeping a cash balance throughout the year equal to 2.5% of expected sales for the year, and an inventory balance throughout the year equal to 25% of expected COGS for the year. The cash balance is essentially funded by the required customer deposits and is invested in marketable securities at an average rate of 0.5%.

• HK’s suppliers currently offer terms of 1/10, net 60 on all purchases.

Capital structure

• HK’s capital structure consists of a single long-term debt issue with a face value of $20 million and 2 million common shares with a current market price of $15 per share.

• The long-term debt issue carries a coupon rate of 5% with interest paid semi-annually. It has eight years remaining until maturity and a current market yield of 6%, also based on semi-annual compounding.

• The common shares have a beta of 1.15. HK paid a dividend of $0.95 per share in its most recently completed financial year. Analysts believe these dividends will grow at an average annual rate of 3% for the foreseeable future. The current risk-free interest rate is 2.5%, and the market price of risk is 6%.

• Recent discussions with HK’s investment banker have indicated that flotation costs would be 7% before tax on any new issue of common shares and 4% after tax on any new issue of long-term debt.

General corporate information

• HK’s corporate tax rate is 32%.

• Senior management has determined that 12% is the appropriate discount rate to use in evaluating the proposal to expand its operations to motor vessels.

Appendix

Motor Vessel division, Heavy Keele Ltd.

Pro forma operating income statements

	Years 1 to 3	Years 4 to 10
Sales revenue	$12,500,000	$18,000,000
Cost of goods sold	(7,000,000)	(9,000,000)
Gross profit	5,500,000	9,000,000
General and administrative expenses	(2,950,000)	(2,950,000)
Depreciation	(1,100,000)	(1,100,000)
Operating income	$1,450,000	$4,950,000

Answer rating: 100% (QA)

a To create a map visualization of the current dealer locations using Power BI you can follow these steps Open Power BI and create a new report Click on Get Data to import the data from the Excel file ... View the full answer