Accounting $1$C Long Term Project

Accounting IC Long Term Project- Master Budget You need to prepare a Master Budget for The company has an exclusive right to sell Powe use and sales have been brisk. The Master Budget will be for the next three months starting April 1. The followtng information is available related to the budget. The cmvany needs to maintain a minimum cash balance at the end of every month in the amount of $15,000. The Pulses are forecasted to sell at $25 each. Recent actual and projected sales (in units) are as follows Balance Sheet at March 31 is as folbws Feb Mar 74.000 87,000 Jun 140.000 186,000 112.000 99,000 In order to meet the product demand, the company has established a policy requiring that ending inventory for each month must be equal to 90% of the units expected to sold in the next month. The cost to purchase each unit of product is $15. Purchases are typically paid for as follows: 50% paid in the month of purchase. and the remaining 50% paid in the month aner purchase. All sales are on credit, with no discount, and payable within 15 days. The company's collections on account usually are 25% in the month of sale, 50% in me month immediately after the sale, and 25% in the second month after sale. The company has a very rigorous credit policy and there are virtually no bad debts. The convany's operating expenses are shown bebw: ASSETS Cash Accounts receivable' Inventory (98100 units)" Unexpired insurance Fixed assets (net of depreciation) Total Assets LIABILITIES AND EQUITY Accounts payable (purchases) Dividends payable Capital stock, (no par) Retained Eamings Total Liabilities & Equity $16.000 2,093,750 1,471,500 16.800 193 600 91, 5 $801,000 16,000 400,000 2,574.650 3.791.650 Variable: Sales Commissbns Wages Utilities Insurance expired Depreciation Miscellaneous $3 per unit $42,000 1,500 1.400 1,800 2,400 Accounts receivable consists of $462,500 from February sales and $1,631,250 from March Sales. Use these numbers for both scenarios. " Use this same March ending inventory number for both scenarios. The company has a good relationship with its bank and can borrow money at a 10% annual rate at any time and in any amount All borrowing and repayments must be made at the end of the month. When the company is ready to make a payment, all unpaid interest must be paid first. After the unpad interest is paid. then pnncipal can be repaid as long as the minimum cash balance is maintained. Link these check numbers to your spreadsheet below. .do not just use the check numbers provided with the problem All operating expenses are paid during the month. in cash. with the exception of depreciation and insurance expired. New fixed assets will be purchased dunng May for $30.000. The company declares dividends of $16,000 each quarter. payable in the first month of the following quarter. You Will complete all tasks listed below for the onginal facts above...this will be Scenario I. Then you will repeat the entire process for Scenario 2. This second scenario will show what would happen if there was an increase of 20% (twenty percent) in the number of units sold. This is essentially a flexlbk budget. SCENARIO 1 Prepare a Master Budget for the three month period ending 30th. Include the following detailed I. a. A sales budget by month and in total. b. A schedule of budgeted cash collectims from sales and accounts receivable by month and in total. c. A purchases budget in units and dollars by month and in total. d. A schedule of budgeted cash payments for purchases by month and in total. 2. A cash budget by month and in total. 3. A budgeted income statement for the three-month period ending June 30. Use the contnbution margin approach. 4. A budgeted balance sheet as of June 30. 5. Calculate the Contribution Margin and Break-Even amounts (for the three month period) based on your assumptions about variable and fixed costs. SCENARIO 2 Repeat all the steps (1-5) shown above assuming that the number of units expected to be sold increase by 20%. The months January to March have already occurred so those will be the same for both Scenarios. Please pay attention to the informatm above when it says: 'Accounts receivable consists of $462,500 from February sales and $1,631,250 from March Sales. Use these numbers for both scenarios- " use this same March ending inventory number for both scenanos. Budgeted Ending Inventory for June is based on July sales. Therefore you will need to increase the expected July sales in Scenario 2 and this will June Ending Inventory will be different in Scenario 2. Here are some check figures to check your final work. If you agree with these check numbers it is an important confirmaton, although it is not guarantee that everything is correct. Amounts for the quarter: Sales budget Budgeted cash collections Budgeted purchases Budgeted cash payments-purchases Ending Cash Balance lnc Stmt Interest Expense Inc Stmt Net income Bal Sheet AR Bal Sheet Inventory Bal Sheet AP Bal Sheet Retained Earnin Bal Sheet Total Assets (=Liab+OE) Budgeted sales (units) Budgeted sales price/unit Budgeted sales price/dollars February sales March sales April sales May sales June sales Total cash collections Scenario 1 SCENARIO 1 Name of your Company Sales budget Scenario 2 For the Three Months Ending June 30, 202X Amounts for the quarter: Sales budget Budgeted cash collections Budgeted purchases Budgeted cash payments-purchases Ending Cash Balance Inc Stmt Interest Expense lnc Stmt Net income Bal Sheet AR Bal Sheet Inventory Bal Sheet AP Bal Sheet Retained Earnings (RE) Bal Sheet Total Assets (=Liab+OE) Scenario $8.606.250 $6.727.500 $580,831 $719 $2.896.981 $976,500 $6.848.131 SCENARIO 1 Name of your Company Budgeted purchases Scenario 2 9,908.750 $8.367.300 $171,091 $7,459 S3,499.241 $2,008,800 $1,171,800 $7.645.691 June April May SCENARIO 1 Name of your Company Budgeted cash collections For the Three Months Ending June 30, 202X May For the Three Months Ending June 30, April May Budgeted sales (units) Add budgeted ending inventory* Total needs Less beginning inventory Required purchases in units Unit cost Required purchases in dollars *Budgeted ending inventory at 90% of next months sales in units. SCENARIO 1 Name of your Company Budgeted cash payments for purchases For the Three Months Ending June 30, 202X Quarter Quarter April SCENARIO 1 Name of your Company Cash Budget For the Three Months Ending June 30, 202X June June June Quarter Quarter Quarter Cash balance beginning of month Add cash from customers Total cash available Less cash payments. Purchase of Inventory Sales commissions Salaries and wages Utilities Misc Dividends paid Equipment Purchases Total cash paid Excess (deficiency) of available over pmts Financing Borrowing (Repayment) Interest Total financing Cash balance end of month April MEMO May April March purchases April purchases May purchases June purchases Total cash payments SCENARIO 1 May June Name of your Company Budgeted Income Statement For the Three Months Ending June 30, Sales in units (Memo) Sales dollars Less variable expenses Cost of Goods sold at S_per unit Commissions S per unit Contribution Margin Less fixed expenses Wages Utilities Insurance expired Depreciation Miscellaneous Net operating income Less interest expense* Net income Cash AR Inventory *MEMO: interest expense calculation Interest expense on amount borrowed in 2nd month: total amount borrowed in 2nd month rate per year interest for 12 months S number of months in a year interest for 1 month S months outstanding total interest expense for this amount Interest expense on amount borrowed in 1st month: total amount borrowed in 1st month rate per year interest for 12 months S number of months in a year interest for 1 month S months outstanding total interest expense for this amount total interest expense for both amounts 10.00% 12 10.00% 12 2 SCENARIO 1 Name of your Company Budgeted Balance Sheet June 30, 202X units at S per unit Unexpired insurance Fixed assets, net Total Assets Interest Payable Dividends payable Notes payable-bank Capital Stock, no-par Retained Earnings Total Liabilities & Equity MEMO. Balance Sheet detail: Accounts Receivable (AR) May Sales June Sales Retained Earnings (RE) Beg RE Net income Less Dividends declared Contribution Margin and BEP Unit Sales price Unit Purchase price Unit Sales Commissions Unit Contribution Margin Fixed Costs for three month period Wages Utilities Insurance expired Depreciation Miscellaneous Total Fixed Costs Break Even Point_ Fixed Costs/lJnit CM Fixed Costs/CMO/0 To help you calculate interest and round the amount _1 to the nearest dollar: formulas are provided in the yellow cells. ENTER AMOUNTS FROM CASH BUDGET HERE (If any) CHANGE YOUR RATE HERE if different MEMO: note rounding formula in the column G formula ENTER AMOUNTS FROM CASH BUDGET HERE (If any) CHANGE YOUR RATE HERE if different MEMO: note rounding formula in the column G formula AR Beginning +Sales -Collections AR Ending BEP units BEP dollars