Hello, I need help building a budget with the following information. What I'm struggling with is finding the appropriate inputs and make sure the excel sheet is fully linked together. Meaning that when I change the inputs on part 1 fact, it changes the outcome on the last tab Part 1 Fanncials.

If someone could help me build this document and enter in the formulas for each sections, I would be very grateful.

Thank you,

A B C D E F G H Sales Budget July August September Quarter Sales $ 600,000 $ 910,000 $ 475,000 $ 385,000 Cash Collections July August September Quarter June sales (A/R from beginning Balance Sheet) 340,000 340,000 July sales - Collected in Current Month 180,000 180,000 July sales - Collected in Following Month 420,000 420,000 August sales - Collected in Current Month 273,000 273,000 August sales - Collected in Following Month 637,000 637,000 September sales - Collected in Current Month 142,500 142,500 Total Expected Cash Collections $ 520,000 $ 693,000 $ 779,500 $ 1,992,500 Total Sales for the Quarter A/R at the end of the Quarter (September sales still not collected) Part 1 Facts Part 1 Sales Budget Part 1 Purchasing Budget Part 1 Admin Budget Part 1 Cash Budget Part 1 Financials +A B C D E F G H I J K L M Inventory Purchase Budget July August September October Sales $ 600,000.00 $ 910,000.00 $ 475,000.00 $ 385,000.00 Cost of Merchandise as % of Sales 45% 45% 15% 45% Budgeted Cost of Merchandise Sold $ 270,000 $ 409,500 $ 213,750 $ 173,250 CO 9 Following Month's COGS 10 Desired Ending Inventory % Desired Ending Inventory Dollars 12 13 Budgeted Cost of Merchandise Sold 270,000 409,500 213,750 173,250 14 Plus Desired Ending Inventory 15 Total Inventory needs 16 Less beginning Inventory 17 Required purchases 18 19 20 21 22 Schedule of Expected Cash Disbursements - Purchases 23 July August September Quarter 24 June purchases (A/P from Balance Sheet) 25 July purchases 26 August purchases 27 September purchases 28 Total disbursements 29 30 31 32 Cost of Merchandise Sold for the Quarter 33 Ending Inventory at the end of the Quarter 34 Ending A/P at the end of the Quarter 35 36 37 38 39 Part 1 Facts Part 1 Sales Budget Part 1 Purchasing Budget Part 1 Admin Budget Part 1 Cash Budget Part 1 Financials +F28 * X Vfx A B C D E F G Shipping as a percentage of Sales 8 Other Expenses as a percentage of Sales 10 Variable expenses: Shipping 13 Other expenses 14 Total variable expenses 15 Fixed expenses 16 Salaries and wages 17 Advertising 18 Prepaid insurance 19 Depreciation 20 Total fixed expesnes 21 Total selling and admin expenses 22 23 Less non-cash items (Depreciation & Prepaid Ins) Total cash disbursements 25 26 27 28 Prepaid Insurance that is expensed during the Quarter 29 Depreciation Expense Recognized during the Quarter 30 31 32 33 34 35 36 37 38 39 40 42 43 44 4 Part 1 Facts Part 1 Sales Budget Part 1 Purchasing Budget Part 1 Admin Budget Part 1 Cash Budget Part 1 Financials +C5 x v fx A B C D E F G H Name Box C N Cash Budget July August September Quarter Cash balance, beginning Add cash collections (from sales budget) 8 Total cash available 9 Less cash disbursements For inventory (from purchasing budget) For operating expenses (from selling & admin budget) 12 For equipment Bal. Sheet - equip 13 For cash dividends Bal. Sheet - RE 14 For Interest (from previous month's borrowing) 15 Total cash disbursements 16 Excess (Deficiency) of cash 17 Financing 18 Borrowing Bal. Sheet N/P 19 Repayment Bal. Sheet N/P 20 Total financing 21 Cash balance, ending Bal. Sheet - cash 22 23 24 25 Outstanding Loan Balance 26 Interest on Borrowing (due the following quarter) CA CA $ 27 28 29 30 31 32 33 34 35 36 37 38 4 Part 1 Facts Part 1 Sales Budget Part 1 Purchasing Budget Part 1 Admin Budget Part 1 Cash Budget Part 1 Financials +D7 + XVfx A C D E F G H K M N 0 P Q R S SRS Educational Supplies Company Budgeted Income Statement For the Quarter Ended September 30 Sales Cost of goods Sold From sales budget Gross Margin From purchasing budget Selling and administrative expenses Shipping From operating budget Other 13 From operating budget Salaries and wages 14 Advertising From operating budget 15 From operating budget Prepaid insurance 16 Depreciation From operating budget 17 Net operating incomes From operating budget Less interest expense From cash budget 19 Net Income 20 SRS Educational Supply Company Balance Sheet September 30, 29 Assets 30 Current assets: 31 Cash 32 Accounts receivable From cash budget 33 Inventory From sales budget - collections From purchase budget 14 | Prepaid insurance 35 Total current assets 36 Buildings and equipment (net) 37 Total assets Beg equip + purchepr 38 39 Liabilities and Equity 40 Accounts payable From purchase budget - cash disb Notes Payable From cash budget 42 Stockholder's equity 43 Capital stock 44 Retained earnings From beg balance sheet Beg RE + NI - dividends 45 Total liability and equity 89882 Part 1 Facts Part 1 Sales Budget Part 1 Purchasing Budget Part 1 Admin Budget Part 1 Cash Budget Part 1 Financials +C14 x v fx NAME Group Role James Hanley SAS Cross Fuction 1 Operations Manager pantages as a whole number, I.e, 43, not . 43 (cilla round to whole numbers) SALES MANAGER PRIVATE INFORMATION July August September October Sales 600,000 5 910.000 $ 475,000 5 385,000 PURCHASING MANAGER PRIVATE INFORMATION Part 1 Information Cost of Merchandise as a % of Sales 45% Desired Ending Inventory as a Percentage of Next Month's Cost of Sales 20% OPERATION MANAGER PRIVATE INFORMATION Part 1 Information Shipping Expenses as a Percent of Sales 5.5% Other Expenses as a Percent of Sales 10.5% Salaries and Wages (per month 104,500 Advertising costs (per month] 63,800 Insurance costs (per month) 3,300 Depreciation Expense (per month] 27,500 FINANCE MANAGER PRIVATE INFORMATION Part 1 Information Percent Sales Collected in Month of Sale 30% Percent Sales Collected in Month after Sale 70% Percent of Inventory Purchases paid in month of purchase 50% Percent of Inventory Purchases paid in month after purchase Percent of Operating Expenses paid in month of purchase 100% Percent of Operating Expenses paid in month after purchase Desired minimum ending cash balance each month 35,060 Borrow in increments of 1,000 Monthly interest rate on borrowings (not compounded) 1% Other planned Outlays of Cash JUly August Capital Expenditures 200,000 5 September 90.000 Dividends 50.000 SRS Educational Supply Company Balance Sheet June 30 Assets Current assets: Cash 40.000 Accounts receivable 340.000 Inventory Prepaid insurance 60.000 18.000 Total current assets 148 000 Buildings and equipment (net) TOTAL ASSETS 860.000 1.308.000 Liabilities and Equity Liabilitys Accounts payable Notes Payable 130 000 Total Liabilities 130.000 Stockholder's equity Capital stock 420 000 Retained camings 758.000 Total Equity 1.178.000 TOTAL LIABILITIES AND EQUITY 1.308.000 Part 1 Facts Part 1 Sales Budget Part 1 Purchasing Budget Part 1 Admin Budget Part 1 Cash Budget Part 1 Financials +