PLEASE SHOW FORMULAS
A B C H | J 1 Harbor Springs Kayak Company 2 2022 Fiscal Year (January - December) 4 Produces Base Model Kayak 5 6 Units Sold 50,000 $ 7 Base 2 1.5 8 9 Seasonal Sales 10 Q1 15% 11 Q2 40% 12 Q3 25% 13 Q4 20% 14 100% 15 16 Information: 17 - Q1 Sales for 2023 are expected to be the same as 2022 18 - Harbor Springs Kayak Company requires quarterly unit inventory to end with 15% of the next quarter's budgeted sales volume 19 - Harbor Springs Kayak Company requires quarterly inventory of raw materials to end with 10% of the next quarter's production requirements 20 - Cost per pound of direct materials = $20 21 - Labor is billed at $18/hour 22- Manufacturing Overhead is 50% of Direct Labor Unit Cost 23 - Actual Sales matches budget 24 - Selling and Admin Expenses are $250,000 25 Income Tax Rate is 25% 26 27 28 20 SAWN 3 Price 100 D Direct Materials per unit E Direct Labor per unit F G F11 LN3A 56700 A 8 Expected Sales in units 9 Unit Selling Price 10 11 Total Sales 12 13 14 15 16 17 18 1 Paste 2 4 A. Font Alignment %- Number fx =F8*F9 B C D Harbor Springs Kayak Company Sales Budget For the Year Ending December 31, 2022 Base Model Q1 Q2 Q3 20,000 100 12,500 100 2,000,000 1,250,000 7,500 100 750,000 Conditional Formatting Format as Table Cell Styles E Q4 10,000 100 1,000,000 F Total 50,000 100 5,000,000 G fx =B10-B12 B C Harbor Springs Kayak Company Production Budget For the Year Ending December 31, 2022 Q1 Q2 20,000 1,875 21,875 3,000 18,875 B14 A 1 2 3 4 5 6 7 8 Expected sales in units 9 Add: Desired finished goods units 10 Total Required Units 11 12 Less: Beginning finished goods units 13 14 Required Production units 15 16 17 18 19 20 7,500 3,000 10,500 1,125 9,375 D Base Model Q3 12,500 1,500 14,000 1,875 12,125 E Q4 10,000 1,125 11,125 1,500 9,625 F Total 50,000 7,500 57,500 7,500 50,000 G B21 X fx A 1 2 3 4 5 6 7 8 Units to be produced 9 Direct materials per unit 10 Total pounds needed 11 12 Add: Ending Direct Materials 13 Total Material Required 14 15 Less: Beginning Direct Materials 16 Direct Material Purchases 17 18 Cost per pound 19 20 Total cost of direct material purchases 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Summary Information B C D Harbor Springs Kayak Company Direct Materials Budget For the Year Ending December 31, 2022 Base Model Q1 Q2 Q3 18,875 12,125 50000 2 2 2 37,750 24,250 100,000 2,425.0 1,925.0 10,000.0 40,175.0 26,175.0 110,000.0 3,775.0 2,425.0 10,000.0 36,400 23,750 100,000 20 20 20 20 20 413,000 728,000 475,000 384,000 2,000,000 Sales Budget Production Budget E Q4 9,375 2 18,750 3,775.0 22,525.0 1,875.0 20,650 9,625 2 19,250 1,875.0 21,125.0 1,925.0 19,200 Total G Direct Materials Budget F10 A 1 2 3 4 5 6 7 8 Units to be produced 9 Direct labor hours per unit 10 Total required direct labor hours. 11 12 Direct Labor cost per hour 13 14 Total direct labor costs 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 fx =B10+C10+D10+E10 B C D Harbor Springs Kayak Company Direct Labor Budget For the Year Ending December 31, 2022 Q1 Q2 Sales Budget 9,375 1.5 14,063 18 253,125 Production Budget Base Model Q3 18,875 12,125 1.5 1.5 28,313 18,188 18 18 509,625 327,375 Direct Materials Budget E Q4 9,625 1.5 14,438 18 259,875 F Total G 50,000 1.5 75,000 18 1,350,000 Direct Labor Budget H COG + I Functione G20 nicial Ready fx A B C Cost of Goods Sold Schedule For the Year Ending December 31, 2022 Quantity Unit Cost 2 3 4 5 Per Unit 6 Direct Materials 7 Direct Labor 8 Manufacturing Overhead 9 10 11 Annual Unit Sales 12 13 Total Cost of Goods Sold 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Production Budget D Total 1,000,000 1,350,000 675,000.00 3,025,000 50,000 3,025,000 Direct Materials Budget 50000 75000 Names 20 18 E Auditing F Options Direct Labor Budget B G E COGS H I Income Sta + J + 100% Functione B10 1 2 3 4 5 Sales 6 Cost of Goods Sold 7 Gross Profit 8 Selling and Administrative Expenses 9 Income from Operations 10 Income Tax expense 11 Net Income 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 fx =0.25*B5 A Harbor Springs Kayak Company Income Statement For the Year Ending December 31, 2022 Direct Materials Budget B Names C 5,000,000 (3,025,000) 1,975,000 250,000 1,725,000 1,250,000 475,000 Direct Labor Budget Auditing D COGS Options E F Income Statement G + H
