Answered step by step
Verified Expert Solution
Question
1 Approved Answer
JD Music Loft is one of the customers that purchases guitars from Keenan Music and sells them to customers throughout Southeast Ohio region. JD is
JD Music Loft is one of the customers that purchases guitars from Keenan Music and sells them to customers throughout Southeast Ohio region. | |
JD is in the process of planning its fourth quarter operation this year, which includes the holiday shopping season. | |
The holiday shopping season usually 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 Income 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. |
Sales and administrative expense budget:
Cash Budget:
Performance Income Statement:
Performance Balance Sheet:
AutoSave OFF start_file_18_keenan_Excel_3_Master_Budget_START Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View @ Tell me 5 Section 2: Cash Collections From Cash Sales From Credit Sales this month From Credit Sales last month Total Cash Collections from Customers \begin{tabular}{|lr|rr|rr|} \hline$ & 27,500 & $ & 29,125 & $ & 33,494 \\ \hline$ & 49,500 & $ & 52,605 & $ & 60,496 \\ \hline$ & 27,500 & $ & 33,000 & $ & 35,070 \\ \hline$ & 104,500 & $ & 114,730 & $ & 129,060 \\ \hline \end{tabular} \begin{tabular}{|lr|rr|rr|} \hline$ & 27,500 & $ & 29,125 & $ & 33,494 \\ \hline$ & 49,500 & $ & 52,605 & $ & 60,496 \\ \hline$ & 27,500 & $ & 33,000 & $ & 35,070 \\ \hline$ & 104,500 & $ & 114,730 & $ & 129,060 \\ \hline \end{tabular} AutoSave OFF start_file_18_keenan_Excel_3_Master_Budget_START Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View @ Tell me Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? JD Music Loft is one of the customers that purchases guitars from Keenan Music and sells them to customers throughout Southeast Ohio region. JD is in the process of planning its fourth quarter operation this year, which includes the holiday shopping season. The holiday shopping season usually 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 Income 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. December total sales are predicted to increase 15% from November Sales. The growth percentage applies to both Cash Sales and Credit Sales. 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, JD expects to collect its credit sales in the following pattern: Month of Sale Month following Sales 40% 4 The information on expected monthly Selling and Administrative Expenses is outlined below: (1) Sales Commissions are paid the month after they are incurred. The paid rate to total sales i: (2) The company pays its Shipping and Handling expense in the same month it incurred. Based on an analys Total Fixed Shipping and Handling Expense Variable Shipping and Handling Expense as a (3) Insurance Expense related information: Company pre-pays its insurance premium every year on Nove $ Number of months the insurance policy covers (4) Other S\&A Expenses are paid in the same month they are incurred and include: 5 The company expects to purchase a piece of land in October, paying cash for $35,000 6 The company has a line of credit from a lncal bank and it pays interest each month based on its beginning balance. Monthly interest rate 2% 7 The company must maintain a minimum cash balance at the end of each month. Minimum ending cash balance each month $20,000 AutoSave OFF start_file_18_keenan_Excel_3_Master_Budget_START Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View @ Tell me Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline \multicolumn{23}{|c|}{ Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? } \\ \hline A3 & ^ & fx & & & & & & & & & & & & & & & & & & & & \\ \hline A & & B & c & D & E & F & G & H & 1 & J & K & L & M & N & 0 & P & Q & & R & s & T & u \\ \hline 1 & & earned & possible & cell & & & & & & Start Fil & enan & & Press F9 & & & & Feedback & & & & Cheer & \\ \hline 2 & & 0 & 27 & & & & & & & & & & & & & & & & & & & \\ \hline \end{tabular} JD Music Loft Proforma Income Statement For the Month in Sales 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 Net Income \begin{tabular}{|l|l|l|} \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline \end{tabular} 41 4 Beginning Balance Sheet \begin{tabular}{l|l|l|l|} Sales Budget & Purchases Budget & S\&A Exp. Budget & Cash Budget Proforma Income Statement \\ \hline \end{tabular} Proforma Balance Sheet Ready Q x Accessibility: Investigate [- AutoSave OFF start_file_18_keenan_Excel_3_Master_Budget_START Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View @ Tell me Calibri (Body) 12AA ab Wrap Text v Formatting as Table Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? A3 fx A earned C 0 possible D E 21 60 cell Start File 18 Keenan M Press F9 to grade JD Music Loft Selling \& Administrative Expense Budget For the Month in Section 1: Variable Expenses Commissions Expense Variable Shipping and Handling Expense Total Variable S\&A Expenses Section 2: Fixed Expenses Fixed Shipping and Handling Expense Insurance Expense Rent Expense Depreciation Expense Utilities Expense Salaries Expense Advertising Expense Total Fixed S\&A Expenses Section 3: Cash Payments Commissions Expense Total Shipping and Handling Expense Insurance Expense Rent Expense Depreciation Expense Utilities Expense Salaries Expense Advertising Expense Total Cash Payments October November December \begin{tabular}{|l|l|l|} \hline & & \\ \hline & & \\ \hline & & \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline \end{tabular} > Case Beginning Balance Sheet Sales Budget Purchases Budget S\&A Exp. Budget Cash Budget Proforma Income Statement Proforma Balance Sheet Ready Q x Accessibility: Investigate - +100% AutoSave OFF start_file_18_keenan_Excel_3_Master_Budget_START Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View @ Tell me AutoSave OFF start_file_18_keenan_Excel_3_Master_Budget_START Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View @ Tell me 5 Section 2: Cash Collections From Cash Sales From Credit Sales this month From Credit Sales last month Total Cash Collections from Customers \begin{tabular}{|lr|rr|rr|} \hline$ & 27,500 & $ & 29,125 & $ & 33,494 \\ \hline$ & 49,500 & $ & 52,605 & $ & 60,496 \\ \hline$ & 27,500 & $ & 33,000 & $ & 35,070 \\ \hline$ & 104,500 & $ & 114,730 & $ & 129,060 \\ \hline \end{tabular} \begin{tabular}{|lr|rr|rr|} \hline$ & 27,500 & $ & 29,125 & $ & 33,494 \\ \hline$ & 49,500 & $ & 52,605 & $ & 60,496 \\ \hline$ & 27,500 & $ & 33,000 & $ & 35,070 \\ \hline$ & 104,500 & $ & 114,730 & $ & 129,060 \\ \hline \end{tabular} AutoSave OFF start_file_18_keenan_Excel_3_Master_Budget_START Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View @ Tell me Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? JD Music Loft is one of the customers that purchases guitars from Keenan Music and sells them to customers throughout Southeast Ohio region. JD is in the process of planning its fourth quarter operation this year, which includes the holiday shopping season. The holiday shopping season usually 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 Income 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. December total sales are predicted to increase 15% from November Sales. The growth percentage applies to both Cash Sales and Credit Sales. 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, JD expects to collect its credit sales in the following pattern: Month of Sale Month following Sales 40% 4 The information on expected monthly Selling and Administrative Expenses is outlined below: (1) Sales Commissions are paid the month after they are incurred. The paid rate to total sales i: (2) The company pays its Shipping and Handling expense in the same month it incurred. Based on an analys Total Fixed Shipping and Handling Expense Variable Shipping and Handling Expense as a (3) Insurance Expense related information: Company pre-pays its insurance premium every year on Nove $ Number of months the insurance policy covers (4) Other S\&A Expenses are paid in the same month they are incurred and include: 5 The company expects to purchase a piece of land in October, paying cash for $35,000 6 The company has a line of credit from a lncal bank and it pays interest each month based on its beginning balance. Monthly interest rate 2% 7 The company must maintain a minimum cash balance at the end of each month. Minimum ending cash balance each month $20,000 AutoSave OFF start_file_18_keenan_Excel_3_Master_Budget_START Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View @ Tell me Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline \multicolumn{23}{|c|}{ Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? } \\ \hline A3 & ^ & fx & & & & & & & & & & & & & & & & & & & & \\ \hline A & & B & c & D & E & F & G & H & 1 & J & K & L & M & N & 0 & P & Q & & R & s & T & u \\ \hline 1 & & earned & possible & cell & & & & & & Start Fil & enan & & Press F9 & & & & Feedback & & & & Cheer & \\ \hline 2 & & 0 & 27 & & & & & & & & & & & & & & & & & & & \\ \hline \end{tabular} JD Music Loft Proforma Income Statement For the Month in Sales 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 Net Income \begin{tabular}{|l|l|l|} \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline \end{tabular} 41 4 Beginning Balance Sheet \begin{tabular}{l|l|l|l|} Sales Budget & Purchases Budget & S\&A Exp. Budget & Cash Budget Proforma Income Statement \\ \hline \end{tabular} Proforma Balance Sheet Ready Q x Accessibility: Investigate [- AutoSave OFF start_file_18_keenan_Excel_3_Master_Budget_START Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View @ Tell me Calibri (Body) 12AA ab Wrap Text v Formatting as Table Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? A3 fx A earned C 0 possible D E 21 60 cell Start File 18 Keenan M Press F9 to grade JD Music Loft Selling \& Administrative Expense Budget For the Month in Section 1: Variable Expenses Commissions Expense Variable Shipping and Handling Expense Total Variable S\&A Expenses Section 2: Fixed Expenses Fixed Shipping and Handling Expense Insurance Expense Rent Expense Depreciation Expense Utilities Expense Salaries Expense Advertising Expense Total Fixed S\&A Expenses Section 3: Cash Payments Commissions Expense Total Shipping and Handling Expense Insurance Expense Rent Expense Depreciation Expense Utilities Expense Salaries Expense Advertising Expense Total Cash Payments October November December \begin{tabular}{|l|l|l|} \hline & & \\ \hline & & \\ \hline & & \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline \end{tabular} \begin{tabular}{|l|l|l|} \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline & & \\ \hline \end{tabular} > Case Beginning Balance Sheet Sales Budget Purchases Budget S\&A Exp. Budget Cash Budget Proforma Income Statement Proforma Balance Sheet Ready Q x Accessibility: Investigate - +100% AutoSave OFF start_file_18_keenan_Excel_3_Master_Budget_START Saved to my Mac Home Insert Draw Page Layout Formulas Data Review View @ Tell meStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started