Description: You have been asked by Management at Back-to-School Inc. to help the managerial accounting department build reports and make wise business decisions for the company's future.

Back-to-School Inc. is a backpack manufacturer in Florida.

Facts and Numbers from last year 2020:

Year 2020	Pack ABC	Pack Leather	Pack 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 backpack	.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 Pack Basic went up significantly due to COVID and imported material from overseas.

See new facts, for 2021:

Year 2021	Pack ABC	Pack Leather	Pack Basic
Yearly Unit Sales	70,000	40,000	130,000
Sales Mix Percentage	30%	20%	50%
Selling Price Per Unit	$16.99	$26.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 backpack	.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, look to Chapter 8 for assistance)
   1. Create a Sales Budget based on the information above with 240,000 backpacks sold by sales mix
   2. 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 units
   3. III. 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.

how do I make a budget report

 

Sales Cost of goods sold Gross margin Selling and administrative expenses Net operating Income Interest expense Net Income January I February $ March - April " Budgeted Income Statements For This Year (Absorption costing basis) $ May $ June $ July $ August September $ October November $ December $ - $ Total - I '