Facts and Numbers from last year 2020:

Year 2020	Sunglass Silver	Sunglass Shine	Sunglass Basic
Yearly Unit Sales	90,000	60,000	150,000
Sales Mix Percentage	30%	20%	50%
Selling Price Per Unit	$15.99	$24.99	$13.99
Variable Cost: Direct Material	$3.00	$4.50	$5.50
Variable Cost: Direct Labor	$3.20	$8.00	$4.80
Direct Labor Hours per sunglass set	.20	.50	.30

Direct Labor Workers were paid an average of $16 per hour in 2020.

Fixed: Manufacturing Overhead was $348,000 for the year

87,000 direct labor hours (actual activity base)

Also, in 2020 the Company had the following period costs for the year:

• Executive Salaries $182,000
• Administrative Costs and HR Costs $65,000
• Marketing Costs $47,000
• IT (Technology Costs) $15,000
• Rent & Utilities on Office Building for Exe & Admin $24,000

In 2021, currently the Company raised prices on all three products but as a result sales are forecasted to drop plus COVID19 issues.

Direct material cost of Sunglass Basic went up significantly due to COVID and imported material from overseas.

See new facts, for 2021:

Year 2021	Sunglass Silver	Sunglass Shine	Sunglass Basic
Yearly Unit Sales	70,000	40,000	130,000
Sales Mix Percentage	30%	20%	50%
Selling Price Per Unit	$16.99	$28.99	$15.99
Variable Cost: Direct Material	$3.00	$4.50	$9.00
Variable Cost: Direct Labor	$3.20	$8.00	$4.80
Direct Labor Hours per sunglass set	.20	.50	.30

Direct Labor Workers were paid an average of $16 per hour in 2021.

Fixed: Manufacturing Overhead is "Estimated" to be $382,500 for the year

85,000 direct labor hours (estimate activity base)

Also, in 2021 the Company management estimates the following period costs for the year:

• Executive Salaries $175,000
• Administrative Costs and HR Costs $65,000
• Marketing Costs $55,000
• IT (Technology Costs) $15,000
• Rent & Utilities on Office Building for Exe & Admin $25,000

Based on the facts given build the following Management Reports in Excel or Numbers:

1. Budget Reports for 2021 (5 points for 4 complete budget reports, look to Chapter 8 for assistance)
   1. Create a Sales Budget based on the information above with 240,000 sunglasses sold by sales mix
2. 1. Create a Production Budget - for 12 months, if the company plans to sell,
      • 20,000 units in January, February, and March
        • 25,000 units in April, May, June
        • 21,000 units in July, August, September
        • 14,000 units in October, November, December months
        • 18,000 units sold in January 2022
        • the company has a policy of having 10% ending inventory on hand of the next months estimated sales
        • Beginning Inventory at the start of January 2021 was 5,000 unit

3. Create a Cash Collection Budget for the first 3 months of 2021

- again estimating 20,000 units sold each month in January, February, and March

- the company is able to collect 20% of cash sales in the current month and 80% in the following month

4. Create a Budgeted Income Statement based on the facts above 2021.

"Insert Your Company Name Sales Budget For This Year January February March April May June July August September October November December Total Budgeted unit sales Selling price per unit Sales $ $ $ $ $ $ $ $ $ $ $ All | | $ $ $ $ $ A|A| $ $ $ $ $ $ $ January February March April May June July August October September $ November $ December $ $ $ $ $ $ $ $ $ $ $ Cash collections from prior month's sales Cash collections from current month's sales Total cash collections IIA $ $ $ $ $ $ $ $ $ $ $ $