***Please provide formula for each cell in excel.

You are the marketing manager of Locksafe company which manufactures burglar-resistant door locks, which has a good reputation in the market. Your company sells two models of door locks: Deluxe and Standard. To present to the CEO, you have to prepare a 12-month budget for 2021 (January-December) in a CVP income statement format, and your assumptions are as follows:

1. For Standard door locks, the industry market potential is 1.5 million households, and you do not expect a grow in the households in 2021.

2. From Standard door locks, each household purchases 2 units per year.

3. For Standard door locks, you expect your market share as 5% for the first quarter, but then

   with your marketing plans, you expect your market share will increase 0.25% each quarter. So;

   your last quarter market share will be 5.75%.

4. For Standard door locks, your selling price is $85.00 per unit in 2021, and you are not planning

   a price increase during the year.

5. 5) For Deluxe door locks, the industry market potential is 1 million households, and you do not

   expect a grow in the households in 2021.

6. From Deluxe door locks, each household purchases 1 unit per year.

7. For Deluxe door locks, you expect your market share as 3% for the first quarter, but then with

   your marketing plans, you expect your market share will increase 0.25% each quarter. So;

   your last quarter market share will be 3.75%.

8. For Deluxe door locks, your selling price is $135.00 per unit in 2021, and you are not planning

   a price increase during the year.

9. Your expenses for 2021 to be as follows:

After you prepare a 12-month budget for 2021 in a CVP income statement format, also find the contribution margin % for each month and for the full year.

Sales salaries $250,000 per month Advertising 8% of total monthly sales Delivery Expense 5% of total monthly sales Sales Commissions for Standard door locks 1% of monthly sales of the product Sales Commissions for Deluxe door locks 1.5% of monthly sales of the product Variable Manufacturing Costs for Standard door locks $50 per unit Fixed Manufacturing Costs for Standard door locks $300,000 per month Variable Manufacturing Costs for Deluxe door locks $90 per unit Fixed Manufacturing Costs for Deluxe door locks $500,000 per month Rent Expense for the office $20,000 per month Utilities $2,000 per month Insurance $1,500 per month