Can you show in excel and show the formulas and step by step on how to complete it
Case 5 - 20 marks Cash Budget - M&T's Board Shop M&T's Board Shop are considering several alternative means of financing their recent acquisition of $350,000 in new equipment in year 1. One option is to borrow $350,000 from a local bank. The bank has asked them to produce a 3-year cash budget broken down by year (Year 1, 2, and 3). Sales in the prior year were $300,000 and are expected to increase 3 percent each year. Year 1 beginning cash was $97,450 and beginning inventory was $30,000. Purchases are based on an expected cost of sales of 40 percent and a required ending inventory of 25% of next year's sales. Prior year expenses including advertising expense of $2,500, depreciation expense of $1,000, wages expense of $46,000, supplies expense of $450 and utilities expense of $1,600. All expenses except depreciation are paid in the year in which they are incurred and are expected to increase 8 percent each year. Interest expense in expected to remain constant at $15,000 each year for year 1 to 3. Collections in the year of sale are expected to be 90%, with the remaining 10% collected in the next year. Payments in the year of purchase are expected to be 95%, with the remaining 5% paid in the next year. Proceeds from the $300,000 loan are expected in year 1 and $350,000 of equipment will be purchased during year 1. In subsequent years equipment purchases are expected to be $1,000 each year. Proceeds from projected equipment sales each year are expected to amount to $200. Annual payments of $110,000 on the loan also begin in year 1. 1) Create a cash budget based on the assumptions provided. B C D E F Prior Year 1 2 3 300,000 290,000 1 M&T's Board Shop 2 Cash Budget 3 4 Operating activities 5 Operating cash receipts 6 Product sales revenue 7 Collections in the year of sale 8 Collections in the year following sale 9 Operating cash receipts 10 Operating cash payments 11 Purchases 12 Cost of sales 13 Required ending inventory 14 Beginning inventory 15 Purchases 16 Payments in the year of purchase 17 Payments in the year following purchase 18 Cash payments for purchases 19 Expenses 20 Advertising expense 21 Interest expense 22 Wages expense 23 Supplies expense 24 Utilities expense 25 Expenses 26 Operating cash payments 27 Cash from (to) operating activities 150,000 152,000 2,500 56,000 450 1,600 60,550 212,550 77,450 28 Investing activities 29 Equipment purchases 30 Equipment sales 81 Cash from (to) investing activities 32 Financing activities 33 Loan proceeds 34 Loan payments 35 Cash from (to) financing activities 36 Change in cash 37 Beginning cash 38 Ending cash 39 77,450 20,000 97,450 39 40 Assumptions 41 Prior year sales 42 Sales growth each year 43 Expense growth each year 44 Collections in the year of sale 45 Collections in the year following sale 46 Payments in the year of purchase 47 Payments in the year following purchase 48 Advertising expense 49 Depreciation expense 50 Interest expense 51 Wages expense 52 Supplies expense 53 Utilities expense 54 Loan proceeds 55 Annual loan payment 56 Annual equipment purchases 57 Annual equipment sales 58 Year 1 equipment purchase 59 Cost of expected sales 60 Required ending inventory 61 Beginning inventory 62 Beginning cash in year 1 63 64 65 Inventory Income Statement Balance Sheet Ratio Analysis Asset 1001, 1002, 1003 Summary Loan Bond Cash Budget
