Please show the formulas to how you got the answer for the excel sheet!
A B D E F G H I J K Individual Excel Spreadsheet Project Campanile Caf Inputs Sheet 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 $ 8.100 7.200 6.300 5.4001 9.000 Cups of Cold Brew Selling Price per Cup 4,950 4.50 $ 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) $ 1 2 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 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) Paid in Quarter of Purchase Paid in Following Quarter 3 ounces 0.04 per ounce 30% of next quarter's production needs 12,555 ounces 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 10,000 Cash Minimum Cash Balance Note Payable as of December 31, 2021 Interest Payable as of December 31, 2021 $ $ $ 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) 55 A B D E F G . 1 2 3 4 Individual Excel Spreadsheet Project Campanile Caf Balance Sheet 5 6 7 8 9 10 11 12 13 14 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 $ A A A A A A A A A A 180 20,000 2,000 80,000 60,622 162,802 15 10 A B D E F F G 1 2 Nm 3 4 Individual Excel Spreadsheet Project Campanile Caf Income Statement 5 6 7 0 0 8 9 10 11 12 13 14 15 16 Income Statement as of December 31, 2022 Sales Variable Expenses Variable COGS Selling & Administrative Cotribution Margin Fixed Expenses Manufacturing Overhead Selling & Administrative Net Income 17 18 A B D E F G H Individual Excel Spreadsheet Project Campanile Caf Balance Sheet wnmanonoom24 1 10 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 12 13 14 15 16 17 A B D E F G H I J K Individual Excel Spreadsheet Project Campanile Caf Inputs Sheet 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 $ 8.100 7.200 6.300 5.4001 9.000 Cups of Cold Brew Selling Price per Cup 4,950 4.50 $ 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) $ 1 2 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 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) Paid in Quarter of Purchase Paid in Following Quarter 3 ounces 0.04 per ounce 30% of next quarter's production needs 12,555 ounces 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 10,000 Cash Minimum Cash Balance Note Payable as of December 31, 2021 Interest Payable as of December 31, 2021 $ $ $ 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) 55 A B D E F G . 1 2 3 4 Individual Excel Spreadsheet Project Campanile Caf Balance Sheet 5 6 7 8 9 10 11 12 13 14 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 $ A A A A A A A A A A 180 20,000 2,000 80,000 60,622 162,802 15 10 A B D E F F G 1 2 Nm 3 4 Individual Excel Spreadsheet Project Campanile Caf Income Statement 5 6 7 0 0 8 9 10 11 12 13 14 15 16 Income Statement as of December 31, 2022 Sales Variable Expenses Variable COGS Selling & Administrative Cotribution Margin Fixed Expenses Manufacturing Overhead Selling & Administrative Net Income 17 18 A B D E F G H Individual Excel Spreadsheet Project Campanile Caf Balance Sheet wnmanonoom24 1 10 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 12 13 14 15 16 17
