I started to get stuck on this budget project beginning at the Cash budget but the first several pictures are for your information.

info:

as well as check figures:

what I have solved, if you see any issues please let me know:

where I start to get lost, I got direct materials purchased (502) from beginning balance sheet but it looks goofy in that area compaired to the other ones in the next quarters. There may also be other issues there I don't really know what I was doing.

also if you can help with the following that would really help me out.

2022 (Projected) 2023 (Projected) Quarter 2 Quarter 3 Quarter 4 Quarter 1 Quarter 2 Quarter 1 Sales Cups of Hot Coffee Selling Price per Cup 9.000 3.00 7,200 6,300 8,100 9,450 7,650 $ Cups of Cold Brew Selling Price per Cup 4,950 4.50 8.100 7,200 6,300 5,400 9,000 $ Collections Quarter of Sale Quarter after Sale 85% 15% Finished Goods Inventory Ending FG Inventory Requirement Ending FG Inventory Units (December 31, 2021) 0% of next quarter's sales O units (total cups) Raw Materials Inventory Raw Materials (coffee beans) Required per Cup Raw Materials Cost per Ounce Ending RM Inventory Requirement Ending RM Inventory Units (December 31, 2021) Daid in Quarter of Durrhoea Name Inputs Beg. Balance Sheet 3 ounces $ 0.04 per ounce 30% of next quarter's production needs 12,555 ounces Q004 Sales and Collections Production RM, DL, & OH S&A... + G Paid in Quarter of Purchase Paid in Following Quarter 90% 10% Direct Labor Labor Required per Cup Labor Cost per Hour $ 0.06 hours 15.00 per hour Manufacturing Overhead Variable Fixed Noncash Fixed (included in above) $ $ $ 0.70 per cup 10,000 per quarter 600 per quarter Selling & Administrative Expenses Variable Fixed Noncash Fixed (included in above) $ $ $ 0.85 per cup 2.000 per quarter 400 per quarter Cash Minimum Cash Balance Note Payable as of December 31, 2021 Interest Payable as of December 31, 2021 $ $ $ 10,000 20,000 to be paid January 1, 2022 2,000 to be paid January 1, 2022 Other Store Improvement Payment Store Improvement Payment $ $ 2,600 in March (1st Quarter) 1,800 in April (2nd Quarter) Balance Sheet as of December 31, 2021 Assets Liabilities and Equities Cash $ 60,000 Accounts Payable $ Accounts Receivable $ 14,300 Notes Payable $ Raw Materials Inventory $ 502 Interest Payable $ Finished Goods Inventory $ Common Stock $ Property, Plant and Equipment, net $ 88,000 Retained Earnings $ Total Assets $ 162,802 Total Liabilities and Equities $ 0 00 180 20,000 2,000 80,000 60,622 162,802 le Have correct answers that properly calculate & match the following check figures: Cash collections in the 1st quarter = $56,183.75 Raw material purchases in the 4th quarter = $1,744.20 Product Cost per unit = $1.72 Ending cash balance for the year 2022 = $60,364.02 Net income for the year 2022 = $16,399.50 Total assets at the end of the year 2022 = $157,196.12 Goal seek selling price per unit is $4.71 Total Cups of Hot Coffee Sold Unit Price per Cup Total Cups of Cold Brew Sold Unit Price per Cup Total Sales Projected Sales Budget 2022 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total 9,000 7,200 6,300 8,100 30,600 $ 3.00 $ 3.00 $ 3.00 $ 3.00 $ 3.00 4,950 8,100 7,200 6,300 26,550 $ 4.50 $ 4.50 $ 4.50 $ 4.50 $ 4.50 $ 49,275.00 $ 58,050.00 $ 51,300.00 $ 52,650.00 $ 211,275.00 Current Quarter Prior Quarter Total Cash Collections Expected Cash Collections Budget 2022 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total $ 41,883.75 $ 49,342.50 $ 43,605.00 $ 44,752.50 $ 179,583.75 $ 14,300.00 $ 7,391.25 $ 8,707.50 $ 7,695.00 $ 38,093.75 $ 56,183.75 $ 56,733.75 $ 52,312.50 $ 52,447.50 $ 217,677.50 3 _0 Unit Sales 1 Plus: Desired FG Ending Inventory _2 Total Units Required 3 Less: FG Beginning Inventory 4 Units Produced 5 16 Quarter 1 13,950 0 13,950 0 13,950 Production Budget 2022 Quarter 2 Quarter 3 15,300 13,500 0 0 15,300 13,500 0 0 15,300 13,500 Quarter 4 14,400 0 14,400 0 14,400 Total 57,150 0 57,150 0 57,150 2023 Quarter 1 Quarter 2 14,850 16,650 0 14,850 0 14,850 7 7 8 9 10 11 12 13 14 15 16 17 18 2023 Quarter 1 14,850 3 44,550 Raw Materials Budget 2022 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total 13,950 15,300 13,500 14,400 57,150 3 3 3 3 3 41,850 45,900 40,500 171,450 13,770 12,150 12,960 13,365 52.245 55,620 58,050 53,460 56,565 223,695 502 13,770 12,150 12,960 39,382 55,118 44,280 41,310 43,605 184,313 S 0.04 $ 0.04 $ 0.04 $ 0.04 $ 0.04 $ 2,204.71 $ 1,771.20 $ 1,652.40 $ 1,744.20 $ 7,372.51 Units Produced (Total Cups) RM Ounces Required per Unit Total RM Ounces Used for Production Plus: Desired RM Ending Inventory Total Ounces Required Less: RM Beginning Inventory Raw Material Purchases (Ounces) Cost per Ounce Raw Material Purchases (Cost) 43,200 19 20 21 22 23 24 Direct Labor Budget 2022 Quarter 1 Quarter 2 Quarter 3 Quarter 4 13,950 15,300 13,500 14,400 0.06 0.06 0.06 0.06 837 918 810 864 15S 15 15S 1515 Sales and Collections Production RM, DL, & OH S&A ... E 25 Units Produced (Total Cups) Labor Hours per Unit Total Labor Hours Required Rate per Labar Hour Name Inputs Beg. Balance Sheet Total 57,150 0.06 3,429 15 26 27 Rate per Labor Hour Direct Labor Cost $ 15$ 12,555 $ 15 $ 13,770 $ 15$ 12,150 $ 15$ 12,960$ 15 51,435 Units Produced (Total Cups) Variable Overhead Rate per Unit Total Variable Overhead Cost Fixed Overhead Total Overhead Expense Noncash Overhead Expenses Overhead Cash Disbursements Manufacturing Overhead Budget 2022 Quarter 1 Quarter 2 Quarter 3 Quarter 4 13,950 15,300 13,500 14,400 $ 0.70 $ 0.70$ 0.70 $ 0.70 $ $ 9,765 $ 10,710 $ 9,450 $ 10,080 $ $ 10,000 $ 10,000 $ 10,000 $ 10,000 $ $ 19,765$ 20,710 $ 19,450 $ 20,080$ $ 600 $ 600 $ 600 $ 600 $ $ 20,365 $ 21,310 $ 20,050 $ 20,680 $ Total 57,150 0.70 40,005 40,000 80,005 2,400 82,405 Direct Materials Cost per Unit $ Direct Labor Cost per Unit $ Variable Overhead Cost per Unit $ Total Product Cost per Unit (variable costing) $ Product Cost Per Unit 0.12 0.90 0.70 1.72 Selling & Administrative Budget 2022 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total Unit Sales 13,950 15,300 13,500 14,400 57,150 Variable Selling & Administrative Cost per Unit $ 0.85 $ 0.85 $ 0.85 S 0.85 $ 0.85 Total Variable S&A $ 11,858 $ 13,005 $ 11,475 $ 12,240 $ 48,578 Fixed S&A $ 2,000 $ 2,000 $ 2,000 $2,000 $ 8,000 Total S&A Expense $ 13,858 $ 15,005 $ 13,475 $ 14,240 $ 56,578 Noncash S&A Expenses $ 400 $ 400 $ 400 $ 400 $ 1,600 S&A Disbursements $ 14,258 $ 15,405 $ 13,875 $ 14,640 $ 58,178 K a 9 660 W A B D E F G H 7 Cash Budget 8 2022 Quarter 1 Quarter 2 Quarter 3 Quarter 4 Total 10 Beginning Cash Balance $ 60,000.00 $ 31,699.34 $ 22,172.18 $ 14,986.08 $ 128,857.59 11 Cash Collections $ 56,183.75 $ 56,733.75 $ 52,312.50 $ 52,447.50 $ 217,677.50 12 Available Cash Balance $ 116,183.75 $ 88,433.09 $ 74,484.68 $ 67,433.58 $ 346,535.09 13 Cash Disbursements: 14 Direct Material Purchased in Prior Quarter $ 502.20 $ 2,204.71 $ 1,771.20 $ 1,652.40 $ 6,130.51 15 Direct Material Purchased in Current Quarter $ 2,204.71 $ 1,771.20 $ 1,652.40 $ 1,744.20 $ 7,372.51 16 Direct Labor $ 12,555.00 $ 13,770.00 $ 12,150.00 $ 12,960.00 $ 51,435.00 17 Overhead $ 20,365.00 $ 21,310.00 $ 20,050.00 $ 20,680.00 $ 82,405.00 18 Selling & Administrative $ 14,257.50 $ 15,405.00 $ 13,875.00 $ 14,640.00 $ 58,177.50 19 Equipment Purchases $ 2,600.00 $ 1,800.00 $ 4,400.00 20 Total Cash Disbursements $ 52,484.41 $ 56,260.91 $ 49,498.60 $ 51,676.60 $ 209,920.52 21 Balance Before Financing $ 63,699.34 $ 32,172.18 $ 24,986.08 $ 15,756.98 $ 136,614.57 22 Note Payment $ 20,000.00 $ 20,000.00 23 Interest Payment $ 2,000.00 $ 2,000.00 24 Balance After Financing $ 41,699.34 $ 32,172.18 $ 24,986.08 $ 15,756.98 $ 114,614.57 25 Minimum Cash Balance $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 $ 10,000.00 26 Excess (deficiency) $ 31,699.34 $ 22,172.18 $ 14,986.08 $ 5,756.98 $ 104,614.57 27 Ending Cash Balance $ 31,699.34 $ 22,172.18 $ 14,986.08 $ 5,756.98 $ 74,614.57 28 Ingunn Income Statement as of December 31, 2022 Sales Variable Expenses Variable COGS Selling & Administrative Cotribution Margin Fixed Expenses Manufacturing Overhead Selling & Administrative Net Income Balance Sheet as of December 31, 2022 Assets Liabilities and Equities Cash Accounts Payable Accounts Receivable Notes Payable Raw Materials Inventory Interest Payable Finished Goods Inventory Common Stock Property, Plant and Equipment, net Retained Earnings Total Assets Total Liabilities and Equities 7 Goal Seek Selling Price per Unit od 9 10 ER