please use excel formulas if possible. need help with proforma income statement and proforma bal sheet

just balance sheet now

Jo Music Loft is one of the customers that purchases guitars from Keenan Music and selis them to customers throughout Southeast Ohio region. 10 is in the process of planning its fourth quarter operation this vear, which Includes the holiday shopping season. The holiday shopping season usualiy creates a bump in sales as the customers shop for holiday gifts. You are asked to help the accountant complete the Mater Budget and Proforma Financial Statements, including (1) Sales Budget, Inventory Purchases Budget, Selling \& Administrative Expense Budget, and Cash Budget, and (2) Proforma incorne Statement in Contribution Margin format, and Proforma Balance Sheet. The following information is gathered by JD's accountant in preparation of its fourth quarter master budget. 1. Total Sales exnertations are at folleww: December total sales are predicted to increase 15% from November Sales. The growth percentage applies to both Cash 5ales and Credit 5 ales. December Sales growth percentage from November 15% January total sales are expected to return to the normal sales level after the holiday bump. 2 According to the past collection history, 10 expects to collect its cegfit sales in the following pattern: Month of 5 ales 60% Month following 5 ales 40% 3 inventory policy and inventory purchase payments are as follows: Cost of Goods Sold as a percentage of total sales 75% Desired Ending inventory as a percentage of next month coGs 25% Al purchases of Inventory are on credit and the payment of these credit purchases are made in the following pattern: Month of Purchases Month following Purchases 80s 20% Montilinimerestivie 25 10,000 TUIAL LIABILIIIES \& SIUCKHULUEKS EQUIIY Section 2: Cash Payments For Purchases this month For purchases last month Total Cash Payments Note: You do not need to complete the budget for January. You only need January COGS for the Desired Ending Inventory for December. \begin{tabular}{|c|c|} \hline 1 & \\ \hline 2 & \\ \hline 5 & Selling \& Administrative Expense Budg \\ \hline 6 & For the Month in \\ \hline 7 & \\ \hline 8 & Section 1: Variable Expenses \\ \hline 9 & Commissions Expense \\ \hline 10 & Variable Shipping and Handling Expen \\ \hline 11 & Total Variable S\&A Expenses \\ \hline 12 & \\ \hline 13 & Section 2: Fixed Expenses \\ \hline 14 & Fixed Shipping and Handling Expense \\ \hline 15 & Insurance Expense \\ \hline 16 & Rent Expense \\ \hline 17 & Depreciation Expense \\ \hline 18 & Utilities Expense \\ \hline 19 & Salaries Expense \\ \hline 20 & Advertising Expense \\ \hline 21 & Total Fixed S\&A Expenses \\ \hline 22 & \\ \hline 23 & Section 3: Cash Payments \\ \hline 24 & Commissions Expense \\ \hline 25 & Total Shipping and Handling Expense \\ \hline 26 & Insurance Expense \\ \hline 27 & Rent Expense \\ \hline 28 & Depreciation Expense \\ \hline 29 & Utilities Expense \\ \hline 30 & Salaries Expense \\ \hline 31 & Advertising Expense \\ \hline 32 & Total Cash Payments \end{tabular} \begin{tabular}{l|cccl} \hline 1 & earned & possible & cell & general \\ \hline 2 & 29 & 45 & E31 & 0 \\ \hline \end{tabular} JD Music Loft Cash Budget For the Month in Section 1: Cash Available Beginning Cash Cash Collections from Customers Total cash available \begin{tabular}{|c|c|c|c|c|} \hline \multicolumn{2}{|c|}{ October } & \multicolumn{2}{|c|}{ November } & December \\ \hline$ & 21,000 & $ & 20,000 & \\ \hline & 104,500 & & 114,730 & \\ \hline$ & 125,500 & $ & 134,730 & \\ \hline \end{tabular} Section 2: Cash Payments For Inventory purchases For Selling and Administrative Expenses For Land For Interest Total Cash Paid \begin{tabular}{|c|c|c|c|c|} \hline$ & 83,800 & $ & 90,508 & \\ \hline & 16,450 & & 25,404 & \\ \hline & 35,000 & & - & \\ \hline & 1,000 & & 1,615 & \\ \hline$ & 136,250 & $ & 117,527 & \\ \hline \end{tabular} Part 3: Financing Cash Surplus (Deficit) New Borrowing (Repayments) Ending Cash \begin{tabular}{|c|c|c|c|c|c|} \hline$ & (10,750) & $ & 17,203 & & \\ \hline & 30,750 & & 2,797 & & \\ \hline$ & 20,000 & $ & 20,000 & $ & 20,000 \\ \hline \end{tabular} Bank Line of Credit Beginning Balance New Borrowing Repayments Ending Balance \begin{tabular}{|rr|rr|r|} \hline$ & 50,000 & $ & 80,750 & \\ \hline & 30,750 & & 2,797 & \\ \hline & - & & - & \\ \hline$ & 80,750 & $ & 83,547 & \\ \hline \end{tabular} 5 JD Music Loft earned possible cell 0 27 Proforma Income Statement For the Month in Sales October November December Less: Variable Expenses Cost of Goods Sold Commissions Expense Variable Shipping and Handling Expense \begin{tabular}{|l|l|l|} \hline & & \\ \hline & & \\ \hline & & \\ \hline \end{tabular} Contribution Margin Less: Fixed Expenses Fixed Shipping and Handling Expense Insurance Expense Rent Expense Depreciation Expense Utilities Expense Salaries Expense Advertising Expense Interest Expense \begin{tabular}{|l|l|l|} \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline \end{tabular} 28 Net Income 1