Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I am working on the Budgeted Balance sheet. I know my Total Assets should equal my Total Liabilites /Stockholder Equity. However, I am coming up
I am working on the Budgeted Balance sheet. I know my Total Assets should equal my Total Liabilites /Stockholder Equity. However, I am coming up with a difference of $265,400. I have attached a screenshot showing my formulas, as well as the sheets that I drew the information from. Can you please help me figure out which formulas I am incorrectly using? Thank you
A B Balance Sheet December 31,2023 Assets Current Assets: Cash ='Cash Budget"!F23 Accounts receivable ='Sales Budget'!E18-'Sales Budget'!E9 Inventory ='Merchandise Purchase Budget ($)'!F9 Prepaid Insurance 18000 Property & Equipment ='Beginning Balance Sheet'!B11+'Cash Budget'!C14+'Cash Budget'!D14 Accumulated Depreciation ='Beginning Balance Sheet'!B12+'Selling and Admin Expense'!F26 Total Assets =SUM(B7:B12) Liabilities and Stockholders' Equity Curret Liabilities: Accounts payable ='Merchandise Purchase Budget ($)'!E19 Dividends payable 25000 Interest payable 0 Loans payable O Commissions payable ='Selling and Admin Expense'! D12 Total Liabilities =SUM(B16:B20) Stockholders Equity: Capital Stock ='Beginning Balance Sheet'!B23 Retained Earnings ='Beginning Balance Sheet'!B24+'Income Statement 2023'!C29-'Cash Budget'!F15 Total Stockholder Equity =SUM(B23:B24) Total Liabilities and Stockholder's Equity =SUM(C21+C25)A Balance Sheet December 31,2023 Assets Current Assets: Cash $ 86,180 Accounts receivable 157,600 Inventory 203,000 Prepaid Insurance 18,000 Property & Equipment 1,065,000 Accumulated Depreciation (193,000) Total Assets $ 1,336,780 Liabilities and Stockholders' Equity Curret Liabilities: Accounts payable $ 126,000 Dividends payable 25,000 Interest payable Loans payable Commissions payable 34,000 Total Liabilities $ 185,000 Stockholders Equity: Capital Stock $ 800,000 Retained Earnings 617,180 Total Stockholder Equity $ 1,417,180 Total Liabilities and Stockholder's Equity $ 1,602,180Bright World, LLC Balance Sheet December 31,2022 Assets Current Assets: Cash 5 24,000 Accounts receivable 569,600 Inventory 218,400 Prepaid Insurance 18,000 Property 8'. Equipment 925,000 Accumulated Depreciation [135,000] Total Assets $1,220,000 Liabilities and Stockholders' Equity Curret Liabilities: Accounts payable 5 215,040 Dividends payable 25,000 Interest payable - Loans payable - Commissions payable 32,000 Total Liabilities $ 222,040 Stockholders Equity: Capital Stock 5 800,000 Retained Earnings 642,960 Total Stockholder Equity $1,447,960 Total Liabilities and Stockholder's Equity $1,220,000 Bright World, LLC Income Statement December 31,2022 Sales $ 3,840,000 Cost of Goods Sold: Beginning Inventory S 245,000 Purchases 1,653,400 Goods Available for Sale $ 1,898,400 Ending Inventory S 218,400 Cost of Goods Sold: $ 1,680,000 Gross Profit $ 2,160,000 Operating Expenses: Advertising Expense S 800,000 Rent Expense 80,000 Salaries Expense 450,000 Utility Expense 34,000 Insurance Expense 24,000 Commission Expense 192,000 Depreciation Expense 56,000 Total Operating Expenses $ 1,636,000 Income Before Interest Expense S 524,000 Interest Expense S 2,000 Net Income S 522,000Principles of Managerial Accounting - Budget Project Selling and Administrative Expenses expected for 2023: Fall 2022 Operating Expenses expected 1 Quarter 2"d Quarter 3" Quarter 4" Quarter for 2023 - accrual based Bright World LLC (BW) distributes sunglasses to small retail stores in the Midwest part of the Variable Expenses: United States. BW does not manufacture the sunglasses, but instead buys the sunglasses from foreign Sales Commission 5% of sales 5% of sales 5% of sales 5% of sales sources and resells the sunglasses to small retail stores. revenue revenue revenue revenue Fixed Expenses: It is December of 2022 and you have been hired as BW's Budget Manager. BW's President, Gary Advertising $210,000 $210,000 $210,000 $210,000 Christens, has asked you to prepare a master budget for 2023. You have gathered the following Rent $20,000 $20,000 $20,000 $20,000 information and are ready to begin preparing the budget. Salaries $120,000 $120,000 $120,000 $120,000 Utilities $9,000 $9,000 $9,000 $9,000 Sales Information for BW: Insurance $6,000 $6.000 $6.000 $6.000 Actual sales 2022 Pairs of Sunglasses sold Depreciation (includes the $14,000 $14,000 $15,000 $15,000 1" quarter 70,000 projected 2023 equipment and "quarter 94,000 purchases) 3rd quarter 36,000 4th quarter 40,000 Sales commissions are accrued in the quarter the sales are made, but are paid to the sales staff in the following quarter. Forecasted sales 2023 1" quarter Your specific Input Insurance cost is $24,000 for a twelve-month period. The insurance premium is prepaid on October 1 "quarter Your specific Input of each year. The October 1" premium provides insurance coverage for the following 12 months, 3rd quarter Your specific Input from October 1 through September 30. quarter Your specific Input All other expenses are paid in cash in the quarter incurred for financial purposes. 1" quarter 2024 58,000 The company requires a minimum cash balance of $50,000. All borrowings and repayments are done Shipping firms and the U.S. Customs have previously delayed sunglass shipments arriving from the in even $1,000 increments. All borrowings are done on the first day of the quarter, and repayments foreign manufacturers. BW management has addressed this concern by establishing a policy that the are made on the last day of the quarter. For each quarter, only a borrowing or a repayment is made. ending inventory for a quarter should be 50% of the next quarter's sales. Interest for all monies borrowed during a quarter is computed and accrued on the last day of the quarter and paid to the bank in the following quarter. Money borrowed is charged interest at an 8% All sunglasses are sold for $16 per pair. All sales are on credit with no discount, and are payable in annual rate. 30 days. BW has found that only 20% of a quarter's sales are collected in the quarter of sale, with an additional 70% collected in the quarter following the sale, and the remaining 10% is collected in the [Borrowing and Interest Example - If BW computes that the company will need to borrow $20,000 second quarter following the sale. Bad debts have been insignificant and are not budgeted. for the first quarter, the loan would occur on January 1 and interest expense during the first quarter would he $400 and this interest would he paid to the hank in the second quarter. If in the second BW buys sunglasses from foreign suppliers. BW pays $7 per pair of sunglasses. 40% of a quarter's quarter, BW determines that it will be able to repay the $20,000 loan, the repayment would occur on purchase is paid for in the quarter of purchase, the other 60% is paid for in the quarter following the the last day of the second quarter or June 30. Interest expense for the second quarter would $400 and quarter of purchase. this interest would be paid in the third quarter.] BW plans to purchase new computer equipment costing $50,000 during the second quarter of 2023 There is a video discussing interest calculation on Canvas. The video discusses the steps in and a new packing machine for $40,000 during the third quarter of 2023. Both of these equipment calculating interest at 7%. Please note the interest rate you should be using is listed above. purchases are for use in the business and will be for cash at the time of the purchase. There is a video discussing the Selling and Administrative Budget on Canvas. BW has a regular dividend pattern which is expected to continue during 2023. This pattern involves BW's board of directors declaring a $25,000 quarterly dividend on March 15", June 15", September 15", and December 15". The dividends are paid in cash 30 days after the board's declaration.A B E Bright World, LLC Cash Budget For the Year Ended December 31, 2023 Quarter 1 2 3 4 Year Beginning cash balance $ 74,000 $ 50,080 $ 51,020 $ 141,030 $ 74,000 Add cash receipts: Collections from customers 705,280 945,280 874,240 669,600 3,194,400 Total available cash 779,280 $ 995,360 $ 925,260 $ 810,630 $ 3,268,400 Less cash disbursements: Merchandise purchases 382,200 $ 390,040 $ 313,250 $ 282,450 $ 1,367,940 Selling and administrative expense 391,000 408,920 404,600 417,000 1,621,520 Equipment purchases 50,000 40,000 90,000 Dividends 25,000 25,000 25,000 25,000 100,000 Total cash disbursements 798,200 $ 873,960 $ 782,850 $ 724,450 $ 3,179,460 Excess (deficiency) of cash available over disbursements $ (18,920) $ 121,400 $ 142,410 $ 86,180 Financing: Borrowings (at the beginning of quarters) 69,000 $ 69,000 Repayments (at the end of quarters) (69,000) (69,000) Interest (1,380) (1,380) (2,760) Total financing 69,000 $ (70,380) $ (1,380) Ending cash balance 50,080 $ 51,020 $ 141,030 $ 86,180 $ 86,180A B Bright World, LLC Income Statement December 31 ,2023 Sales $3,102,400 Cost of Goods Sold: Beginning Inventory $ 218,400 Purchases 1,341,900 Goods Available for Sale $ 1,560,300 Ending Inventory $ 203,000 Cost of Goods Sold: $ 1,357,300 Gross Profit $ 1,745,100 Operating Expenses: Advertising Expense $ 840,000 Rent Expense 80,000 Salaries Expense 480,000 Utility Expense 36,000 Insurance Expense 24,000 Commission Expense 155,120 Depreciation Expense 58,000 Total Operating Expenses $ 1,673,120 Income Before Interest Expense $ 71,980 Interest Expense $ 2,760 Net Income 69,220Bright World, LLC Merchandise Purchases Budget For the Year Ended December 31, 2023 (in dollars) Quarter 1 2 3 4 Year Budgeted Cost of Goods Sold 436,800 $ 399,000 $ 297,500 $ 224,000 $ 1,357,300 Add desired ending merchandise inventory 199,500 148,750 112,000 203,000 203,000 Total needs S 636,300 $ 547,750 $ 409,500 $ 427,000 $ 1,560,300 Less beginning merchandise inventory 218,400 199,500 148,750 112,000 218,400 Required purchases $ 417,900 $ 348,250 $ 260,750 $ 315,000 $ 1,341,900 Schedule of Expected Cash Disbursement for Merchandise Purchases Beginning Accounts Payable 215,040 215,040 First-quarter purchases 167,160 $ 250,740 417,900 Second-quarter purchases 139,300 $ 208,950 348,250 Third-quarter purchases 104,300 $ 156,450 260,750 Fourth-quarter purchases 126,000 126,000 Total cash disbursements for purchases $ 382,200 $ 390,040 $ 313,250 $ 282,450 $ 1,367,940Bright World, LLC Selling and Administrative Expense Budget For the Year Ended December 31, 2023 Quarter 1 2 3 4 Year Budgeted Unit Sales 62,400 57,000 42,500 32,000 193,900 Selling Price S 16 $ 16 $ 16 $ 16 Variable selling and administrative expenses: Commissions expense $ 49,920 45,600 $ 34,000 $ 25,600 $ 155,120 (5% of sales for the quarter) Total Variable Expense S 49,920 $ 45,600 34,000 $ 25,600 $ 155,120 Fixed Selling and administrative expenses: Advertising expense 210,000 S 210,000 $ 210,000 S 210,000 $ 840,000 Rent expense 20,000 20,000 20,000 20,000 80,000 Salaries expense 120,000 120,000 120,000 120,000 480,000 Utility expense 9,000 9,000 9,000 9,000 36,000 Insurance expense 6,000 6,000 6,000 6,000 24,000 Depreciation Expenses 14,000 14,000 15,000 15,000 58,000 Total Fixed Expenses $ 379,000 $ 379,000 $ 380,000 $ 380,000 $ 1,518,000 Total Selling and Administrative Expenses 428,920 $ 424,600 $ 414,000 405,600 $ 1,673,120 Adjustments from Accrual to Cash Less depreciation (14,000) $ (14,000) $ (15,000) $ (15,000) $ (58,000) Commissions (49,920) (45,600) (34,000) (25,600) (155,120) Cash Basis 32,000 49,920 45,600 34,000 161,520 Insurance (6,000) (6,000) (6,000) (6,000) Cash basis-insurance 24,000 Total adjustments (37,920) 15,680) (9,400) 11,400 (51,600) Total to be sent to the cash budget $ 391,000 $ 408,920 404,600 417,000 $ 1,621,520Bright World, LLC Sales Budget For the Year Ended December 31, 2023 Quarter 2 3 4 Year Budgeted Unit Sales 62,400 57,000 42,500 32,000 193,900 Selling price per Unit 16 S 16 S 16 16 $ 16 Total Sales S 998,400 $ 912,000 $ 680,000 $ 512,000 S 3,102,400 Schedule of Expected Cash Collections Beginning accounts receivable $ 448,000 S 448,000 First-quarter sales 257,280 $ 698,880 S 99,840 1,056,000 Second-quarter sales 246,400 638,400 $ 91,200 976,000 Third-quarter sales 136,000 476,000 612,000 Fourth-quarter sales 102,400 102,400 Total Cash Collections S 705,280 $ 945,280 $ 874,240 S 669,600 $ 3,194,400Step 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