Maroota Business Accessories Company (MBAC) based in Maroota, a suburb of Sydney, Australia, is a manufacturer of business travel accessories. For 2018, MBAC is planning to launch a line of travel brief cases or TBC's. These will be offered in five retail price ranges to suit varying needs of the business traveler. Ms. Margaret Court-Smith, MBAC president, believes that this new TBC line would be a welcome complement to MBAC's product assortment and estimates the following share of total sales for each retail Australian $ price-range: 

 

Price Range 1: $200 5%

Price Range 2: $150 10%

Price Range 3: $100 40%

Price Range 4: $ 80 25%

Price Range 5: $ 60 20%

Ms. Court-Smith has learned from production staff that material and labor will run about 45% of the weighted MBAC selling price of a TBC. Packaging and shipping combined is expected to run about $30.28 for 12 TBC's. Annual loan repayment plus contracted maintenance for the new TBC manufacturing line total about $70,000.

Ms. Court-Smith is also reviewing the proposed marketing plan for the new line. The proposal is to sell the TBC line to retailers using regional wholesalers. Such wholesalers typically markup up by 32% on MBAC selling price, while the retailers are used to a 41% markup based on consumer purchase price. Although MBAC has a sales force already in place, Ms. Court-Smith plans to add eight new people exclusively to promote this new line. The annual cost of adding them will run about $36,000 in salary and an additional $11,000 in travel expenses per sales person. The sales force also earns a 7% commission on every MBAC sale.

Ms. Court-Smith plans to add the following promotional initiatives to support the new line:

a direct mail campaign (30,000 glossy printed pieces at $2.70 each) directed at a sample of business travelers to heighten their awareness of the new item

airline magazine advertising six times during the year at $12,000 per placement

participation in three travel accessory trade shows ($14,000 per show in booth rental registration and an additional $6,000 per show in staffing expenses).

Ms. Court-Smith would like to know unit and dollar sales targets for the new TBC line, for recovering all fixed costs. In addition, the company is looking at two competing profit goals: (a) $290,000 in Contribution to Margin i.e., profit, and, (b) 23% profit on sales or 23c on each sales dollar. The 2017 regional TBC market is about 200,000 total units or $16 million in manufacturer sales. Both of these are expected to increase by 10% in 2018.

Can you please help me set up the input section to this problem in an excel format?