Hello,

I need to understand where I made a mistake for budgeted statement of Retained earnings (Year ending 12/31/20x2)? The answer I have is $82,315,490 but the correct answer is $129,921,990.

Please see the assumptions below and screen shots of the excel file to assist me. The formatting on courehero will not let me share the excel file.

Sales Assumptions:

• Based on historical data, 85% of sales revenue is collected in the year of the sale with 5% collected in the year following the sale. 10% of sales is estimated as bad debt. As you know, we make this estimate at year end and record the expense in the year of sale. The net receivables balance at year-end 20X1 consists of the 5% uncollected balance of 20X1 sales.

Inventory Assumptions:

• The costs listed above apply for both 20X1 and 20X2. The current sales prices are based on cost plus 50% markup. Based on historical data, 85% of inventory is paid for in the year of purchase with the remaining 15% paid for in the year following the purchase. Our accounts payable account is used specifically for inventory purchases.

SG&A Assumptions:

• All above budgeted expenses except warranties, depreciation and bad debts will be paid in cash during 20X2. Based on historical data, 100% of warranty claims are paid in the year following the sale. The warranty liability on the year-end 20X1 balance sheet is the estimate for 20X1. And, as you know, depreciation and bad debts are non-cash expenses.

Other Assumptions:

• The year-end 20X1 balances of Accounts Payable, Accrued Expense and Other Current Liabilities will be paid with cash during 20X2. On January 1, 5% of the long-term debt balance will be paid off. At year end, interest on long-term debt is accrued at an annual rate of 12% but is not paid until the following year. We're budgeting an annual 3% return on short-term investments and an annual 7% return on long-term investments; both are expected in cash at year-end. We expect to pay out dividends of 15% of net income on 12/31/20X2. Our profits are taxed at a rate of 20% and will be paid in cash during 20X2. If we generate a net loss, assume zero income tax.

Balance sheet year end 12/31/20x1:

Assets Current assets Cash and equivalents Short term investments Net receivables Inventories Other current assets Total current Non-current assets Long term investments Net property, plant and equipment Other assets Total noncurrent Total assets 48,230,500 45,050,000 11,455,000 11,250,000 6,843,000 I 64,257,000 .| 45,124,000 7,935,000 ABC Company Balance Sheet Year end 12/31/20X1 Liabilities 8: Shareholders Equity Current liabilities Accounts payable Warranties Accrued expenses Other current liabilities Total current 122,329,500 Non-current liabilities Long term debt Total non-current Total liabilities 118,366,000 Shareholders equity 241,195,500 Common stock Additional Paid-In Capital Treasury stock Retained earnings Total shareholders equity Total liabilities & shareholders equity 30,000,000 2,754,000 2,210,000 1,348,000 70,000,000 70,000,000 9,255,000 9,957,000 (15,219,000) 130,859,500 36,322,000 106,322,000 134,873,500 241,195,500 Inventory Purchases Budget Sales units + Target ending inventory Total units required - Beginning inventory Number of units to purchase Unit Cost Cost of Purchases Cost of goods available for sale Cost of goods sold Model A 50 5 55 5 55 $1,000,000 $55,000,000 $60,000,000 $50,000,000 Model B 150 5 155 5 155 $750,000 $116,250,000 $120,000,000 $112,500,000 Model C 100 5 105 5 105 $500,000 Total 300 15 315 15 315 $2,250,000 $52,500,000 $223,750,000 $55,000,000 $235,000,000 $50,000,000 $212,500,000 Intermediate Budgets and Schedules ALL CELLS WILL LINK TO OTHER TABS 0R WILL HAVE FORMULASI Sales Budget Model A Model B Model C Total Inventory Purchases Budget Sales units + Target ending inventory Total units required - Beginning inventory Number of units to purchase Unit Cost Cost of Purchases Cost of goods available for sale Cost of goods sold Units Selling Price Total Revenues 50 $1,500,000 $75,000,000 150 $1,125,000 $168,750,000 100 $750,000 $75,000,000 $318,750,000 Model A Model B Model C 50 150 100 5 5 5 55 155 105 5 5 5 55 155 105 $1,000,000 $750,000 $500,000 $55,000,000 $60,000,000 $50,000,000 $116,250,000 $120,000,000 $112,500,000 Total 300 15 315 15 315 $2,250,000 $52,500,000 $223,750,000 $55,000,000 $235,000,000 $50,000,000 $212,500,000 Selling, General and Administrative Expense Budget Utilities, maintenance, and facilities Executive and administrative salaries Depreciation Marketing and promotions Bad debt expense Sales commission Warranties Shipping and handling costs Insurance Total $681.3. expenses Budgeted Interest Expense Beginning longterm debt balance Payments on longterm debt Ending longterm debt balance Interest expense on ending balance Schedule of Cash Receipts from Sales Beginning accounts receivable Cash receipts from current year sales Total cash receipts on sales Schedule of Cash Receipts from Investments Interest received from shortterm investments Interest received from longterm investments Total cash collections on investments $850,000 $9,000,000 $2,432,000 $31,875,000 $31,875,000 $9,562,500 $9,562,500l $3,187,500 $6,712,500 $105,057,000 $70,000,000 $3,500,000 $66,500,000 $7,980,000 $11,456,000 $270,937,500 $282,393,500 $1,351,500 $4,497,990 $5,849,490 Schedule of Cash Payments for Warranties Cash payments for warranties previous year's estimate Total cash payments for warranties Cash Budget Beginning cash balance Add cash receipts Collections from customers Collections from investments Total cash available Deduct disbursements Cash payments for inventory Utilities, maintenance, and facilities Executive and administrative salaries Marketing and promotions Sales commissions Warranties Shipping and handling costs Insurance Accrued expenses and other Longterm debt Dividends Income tax Total cash payments Budgeted ending cash balance $2,764,000 $2,764,000 $48,230,500 $282,393,500 $5,849,490 $336,473,490 $220,187,500 $850,000 $9,000,000 $31,875,000 $9,562,500 $2,764,000 $3,187,500 $6,712,500 $3,558,000 $3,500,000 $0.00 $0.00 $291,197,000 $45,276,490 IF, THEN FORMULA PROVIDED IF, THEN FORMULA PROVIDED 20X2 Budget Information Estimated sales price per unit Estimated sales volume Cost per unit to purchase Beginning inventory in units Budgeted ending inventory in units % of cash receipts from 20x2 sales % of cash receipts 20X1 sales Ending net Accounts Receivable balance Cash payments for inventory purchased in 20x2 Cash payments for inventory purchased in 20X] Ending Accounts Payable balance Budgeted Selling, General and Administrative Expenses: Utilities, maintenance, and facilities Executive and administrative salaries Depreciation Marketing and promotions Bad debt expense Sales commission Warranties Shipping and handling costs Insurance Payment to reduce Long-term debt Interest rate on long-term debt Model A $1,500,000 50 $1,000,000 5 10 85% 5% 5% 85% 15% 15% $850,000 $9,000,000 $2,432,000 10.0% 10.0% 3.0% 3.0% 1.0% 3.0% 5% 12% Model B Model C $1,125,000 $750,000 150 100 $750,000 $500,000 5 5 10 10 % of sales collected in year of sale % of 12/31/20X1 net receivables % of sales % of cost % of 12/31/20X1 accounts payable % of cost % of sales % of sales % of sales % of sales % of sales % of inventory cost % of beginning of year balance % of beginning of year balance after payment Previous year warranty estimate (20X1) Cash payments for warranties Cash payments for accounts payable, accrued expenses, other current liabilities Cash payment for dividends Cash payment for income tax Cash received for interest earned on short-term investments Cash received for interest earned on long-term investments $2,764,000 100% 100% 15% 20% 3% 7% % of previous year % of balance % of net income % of income before tax % of balance % of balance ALL CELLS WILL LINK TO OTHER TABS 0R WILL HAVE FORMULASI ABC Company Budgeted Income Statement Year ending 12/31/20X2 Sales 282,393,500 Cost of goods sold (223,750,000) Gross margin 58,643,500 Administrative expenses (105,057,000) Operating income (46,413,500) Interest expense (7,980,000) Other income 5,849,490 Income before taxes (48,544,010) Income taxes - IF, THEN FORMULA PROVIDED Net income (48,544,010) ABC Company Budgeted Cost of Goods Sold Schedule Year ending 12/31/20X2 Beginning Inventory 15 Add Purchases 315 Cost of goods available for sale 235,000,000 Less ending inventory 30 Cost of goods sold 212,500,000 ABC Company Budgeted Statement of Retained Earnings Year ending 12/31/20X2 Beginning balance of retained earnings 130,859,500 +/- Net income/loss (48,544,010) - Dividends - Ending balance of retained earnings 82,315,490 129,921,990