Sun and Clear, Inc. is a small wholesale distributor of consumer goods. The company generates a gross margin shown in the blue table. The percent of cash sales is shown in the blue table; the remainder is sold on account and is collected one month later. Accounts receivable on June 30 2022 are the result of June's credit sales to be collected the next month. Budgeted sales for the period are: Budgeted Sales Cash Sales Ending Inventory $55,000 $54.000 As of July 1st, the company plans for each month's ending inventory to be the blue table percentage of the following month's budgeted cost of goods sold. Inventory cash purchases are also shown in the blue table; the rest is paid for in the following month. The accounts payable on June 30 are the result of June's purchases of inventory. All monthly expenses were paid monthly. Monthly expenses included: commissions, $8,500; rent, $2,000; other expenses (excluding depreciation), are reflected in the blue able as a percent of sales. Depreciation is $1,200 for the quarter and includes depreciation on new assets acquired during the quarter. The assets acquired were all cash purchases, equipment of $3,000 in July and $2,500 in August. The company wishes to maintain a minimum cash balance of $2,000 at the end of each month. The company has excellent relationship with a local credit union that allows the company to borrow in increments of $1,000 at the beginning of each month, up to a total line of credit loan balance of $30,000. The interest rate on these loans is 2.5% per month, and interest is not compounded. The company, when able, repays the loan plus accumulated interest at the end of the quarter. Inventory purchases paid in cash uly ug ept Oct Other Expenses Save your file to preserve this table. Complete the assignments on the "Display Budget Schedules" tab. Additional information: Some Balance Accounts on June 30 2022: $3.800 $29.250 | To receive the following month Invento $7.100 $102,550 $22.400 | To pay the following month $99.000 $21.300 Required: Using the data above, for the 3rd quarter ending Sept 30 2022, prepare the following monthly & quarter: a. The schedule of the expected Cash Collections b. The merchandise Purchases budget: . The schedule of expected cash disbursements Merchandise purchases. d. The schedule of expected cash disbursement Selling and Administrative expenses e. The Cash budget: f. An absorption costing Income Statement, for the quarter ending Sept 30 2022. g. A Balance Sheet as of Sept 30 2022 Budging Excel Project Hints You can work alone or with 1 partner. Please list both your names if you are working in team and only one person has to submit. Email me if you have any questions. I'm including some hints and check figures to help you get started o ~(a) Cash collection, for July, what vou collect will be the A/R balance from June 30 and July's cash sale. o (b) Purchase budget, you will find that you need to calculate your COGS to fulfill the desired ending inventory. You are not given the COGS directly, but if vou recall from earlier chapters when we discussed income statement: o Sales - COGS = Gross Profit Margin - OP expenses = Net Income w Sales (100%) = COGS + Gross Profit Margin, you will find in your blue data box that you are given the Gross Profit Margin %. You can then calculate your COGS. o - (c) Expected cash dishursement for purchasing vour inventory: o July's cash disbursement is going to be June's 30's A/P balance + July's Inventory Purchase in cash (% found in blue box x the budgeted purchase you calculated in schedule (b) o Aug's cash disbursement is going to be July's purchase on account + Aug's cash purchase. Additionally, here's a couple figures I'm providing to you to check your work: Schedule a; Total cash collection for Q3 is $154,750 *A/R at the end of Qtr, is Sept's budgeted credit sales that will be collected in Oct. Schedule b: Total Merchandise Purchase for Q3 is $135,004 Schedule : Total Merchandise Purchase Cash Disbursements for Q3 is $135,922 *A/P at the end of Qtr that you will list on your balance sheet is Sept budgeted purchase that you have not yet paid Schedule d: SG&A Cash Disbursements for Q3 is $44,780 *Remember depreciation is a non-cash expense, so you would not include the depreciation in this schedule Schedule d: Cash budget, for July, the cash balance before financing is -$17,794 *Remember that Line of credit available is $30,000 and loan can be taken out a $1,000 increment Schedule f: Income Statement *Remember to include depreciation expense and interest expense.. The instructions stated the interest rate is 2.5% per month and interest is not compound. In other words, July's interest expense from borrowing will be July's borrowing x 2.5%, in Aug's interest will be (July's borrowing + Aug's borrowing) x 2.5%, and in Sept interest will be (July's borrowing + Aug's borrowing+ Sept's borrowing) x 2.5%. The sum of the three months interest expense will be your total interest expense for the Q3 that will show up on your income statement for the Qtr ending on Sept 30 2022, Schedule f: Balance Sheet Review the example in the Royal Case Study and or review your BA 211 notes. Hint: *Net Equipment is Cost - Accumulated Depreciation (remember there are new equipment purchases in the quarter and depreciation expense was given) *Remember to calculate the new Retained Earnings ending balance: RE Beginning Bal + Net Income - Dividends = RE Ending Balance