How to prepare a budgeted balance sheet

Executive Brief

Prepare a master budget from the attached budgets for the 4 depts. stating the goals of the master budget and the process used to create the budget how if the budget process differs presented in an executive brief

Instructions
Southern New Hampshire University
Final Project II
ACC 207 - Cost Accounting - STUDENT WORKBOOK
MILESTONE ONE (Due in Module Five) FINAL PROJECT (Due in Module Seven)
1. 1.
Prepare Sales Budget Prepare Budgeted Income
Prepare Schedule of Statement
Expected Cash Collection Prepare Budgeted Balance
From Sales Sheet
2.
Prepare Merchandise
Purchases Budget
Prepare Schedule of Expected
Cash Disbursements for
Merchandise Purchases
3.
Prepare Selling and Admin
Budget, Including Expected
Cash Disbursements
4.
Prepare Cash Budget
Sales Budget

/xl/drawings/drawing1.xml#'Part%20II%20Sales%20Budget'!A1
Admin Budget

/xl/drawings/drawing1.xml#'Part%20II%20Admin%20Budget'!A1
Purchasing Budget

/xl/drawings/drawing1.xml#'Part%20II%20Purchasing%20Budget'!A1
Financial Statements

/xl/drawings/drawing1.xml#'Part%20II%20Financials'!A1
Instructions Milestone 1

/xl/drawings/drawing1.xml#'Milestone%20One%20Instructions'!A1
Instructions Final Project

/xl/drawings/drawing1.xml#'Instructions%20Final%20Project%20II'!A1
Cash Budget

/xl/drawings/drawing1.xml#'Part%20II%20Cash%20Budget'!A1
Milestone One Instructions
Southern New Hampshire University
College of Continuing Education (COCE)
ACC 207 - Cost Accounting - STUDENT WORKBOOK
INSTRUCTIONS FOR MILESTONE 1 (Due Week 5)
IMPORTANT NOTES:
Make sure to completely review the Milestone One Final Project II Rubric.
Use the data from this milestone and begin work on your final presentation, which is due in Module Seven.
ITEMS TO COMPLETE FOR THIS MILESTONE (Blue Tabs):
GENERAL
You are the accountant tasked with creating the master budget for SRS Educational Supply Company. You have made your budget assumptions (see the red tab labeled Part II Assumptions) and are ready to prepare the following:
SALES BUDGET
Prepare the sales budget by month and in total.
Prepare a schedule of excepted cash collections from sales by month and in total.
PURCHASING BUDGET
Prepare a merchandise purchases budget by month and in total.
Prepare a schedule of expected cash disbursements for merchandise purchases by month and in total.
ADMIN BUDGET
Prepare a selling and admin budget by month and in total.
Add expected cash disbursements for selling and admin by month and in total.
CASH BUDGET
Prepare a cash budget by month and in total.
FINANCIAL INFORMATION FOR BUDGETS - SEE PART II ASSUMPTIONS (RED TAB)
HOME

/xl/drawings/drawing2.xml#Instructions!A1
Part II Assumptions
MOST LIKELY NUMBERS AND ASSUMPTIONS
SALES MANAGER PRIVATE INFORMATION July August September October
Most likely sales $ 600,000 $ 910,000 $ 475,000 $ 385,000
PURCHASING MANAGER PRIVATE INFORMATION Part 1 Information
Most likely cost of merchandise as a % of sales 45%
Desired ending inventory as a percentage of next month's cost of sales 20%
OPERATION MANAGER PRIVATE INFORMATION Part 1 Information
Most likely shipping expenses as a percent of sales 5.0%
Most likely other expenses as a percent of sales 8.0%
Salaries and wages (per month) $ 85,000
Most likely advertising costs (per month) $ 50,000
Most likely insurance costs (per month) $ 3,000
Depreciation expense (per month) $ 25,000
FINANCE MANAGER PRIVATE INFORMATION Part 1 Information
Percent sales collected in month of sale 30%
Percent sales collected in month after sale 70%
Percent of inventory purchases paid in month of purchase 50%
Percent of inventory purchases paid in month after purchase 50%
Percent of operating expenses paid in month of purchase 100%
Percent of operating expenses paid in month after purchase 0%
Desired minimum ending cash balance each month $ 35,000
Borrow in increments of $ 1,000
Monthly interest rate on borrowings (not compounded) 1%
Other planned outlays of cash July August September
Capital expenditures $ 200,000 $ 90,000 $ - 0
Dividends $ 50,000 $ - 0 $ - 0
SRS Educational Supply Company
Balance Sheet
Previous Year End
Assets
Current assets:
Cash $ 40,000
Accounts receivable $ 340,000
Inventory $ 50,000
Prepaid insurance $ 18,000
Total current assets $ 448,000
Buildings and equipment (net) $ 860,000
TOTAL ASSETS $ 1,308,000
Liabilities and Equity
Liabilites
Accounts payable $ 130,000
Notes payable $ - 0
Total liabilities $ 130,000
Stockholder's equity
Capital stock $ 420,000
Retained earnings $ 758,000
Total equity $ 1,178,000
TOTAL LIABILITIES AND EQUITY $ 1,308,000
Home

/xl/drawings/drawing3.xml#Instructions!A1
Sheet1
Part II Sales Budget
Sales Budget
July August September Quarter
Sales $ 600,000 $ 910,000 $ 475,000 $ 1,985,000
Schedule of Cash Collections
July August September Quarter
June sales (A/R From Beginning Balance Sheet) 340,000 340,000
July sales - Collected in Current Month 180,000 180,000
July sales - Collected in Following Month 420,000 420,000
August sales - Collected in Current Month 273,000 273,000
August sales - Collected in Following Month 637,000 637,000
September sales - Collected in Current Month 142,500 142,500
Total Expected Cash Collections
520000 693000 779500 1992500
Total Sales for the Quarter $ 1,985,000
A/R at the end of the Quarter (September Sales Still not Collected) $ 332,500
View Assumptions

/xl/drawings/drawing4.xml#'Part%20II%20Assumptions'!A1
HOME

/xl/drawings/drawing4.xml#Instructions!A1
Part II Purchasing Budget
HOME
Inventory Purchase Budget
July August September October
Sales $ 600,000 $ 910,000 $ 475,000 $ 385,000
Cost of Merchandise as % of Sales 45% 45% 45% 45%
Budgeted Cost of Merchandise Sold $ 270,000 $ 409,500 $ 213,750 $ 173,250
Following Month's COGS $ 409,500 $ 213,750 $ 173,250
Desired Ending Inventory % 20% 20% 20%
Desired Ending Inventory Dollars 81,900 427,500 34,650
Budgeted Cost of Merchandise Sold 270,000 409,500 213,750
Plus Desired Ending Inventory 81,900 427,500 34,650
Total Inventory Needs 351,900 837,000 248,400
Less Beginning Inventory (50,000) (81,900) (42,750)
Required Purchases $ 301,900 $ 370,350 $ 205,650
Schedule of Expected Cash Disbursements - Purchases
July August September Quarter
June Purchases (A/P From Balance Sheet) $ 130,000 $ 130,000
July Purchases $ 150,950 $ 150,950 $ 301,900
August Purchases $ 185,175 $ 185,175 $ 370,350
September Purchases $ 102,825 $ 102,825
Total Disbursements $ 280,950 $ 336,125 $ 288,000 $ 905,075
Cost of Merchandise Sold for the Quarter $ 893,250
Ending Inventory at the end of the Quarter $ 34,650
Ending A/P at the end of the Quarter $ 102,825
View Assumptions

/xl/drawings/drawing5.xml#'Part%20II%20Assumptions'!A1
HOME

/xl/drawings/drawing5.xml#Instructions!A1
Part II Admin Budget
Selling and Administrative Budget
July August September Quarter
Sales $ 600,000 $ 910,000 $ 475,000 $ 385,000
Shipping as a Percentage of Sales 5% 5% 5% 5%
Other Expenses as a Percentage of Sales 8% 8% 8% 8%
Variable Expenses:
Shipping 30,000 45,500 23,750 99,250
Other Expenses 48,000 72,800 38,000 158,800
Total Variable Expenses 78,000 118,300 61,750 258,050
Fixed Expenses:
Salaries and Wages 85,000 85,000 85,000 255,000
Advertising 50,000 50,000 50,000 150,000
Prepaid Insurance 3,000 3,000 3,000 9,000
Depreciation 25,000 25,000 25,000 75,000
Total Fixed Expesnes 163,000 163,000 163,000 489,000
Total Selling and Admin Expenses 241,000 281,300 224,750 747,050
Less Noncash Items (Depreciation and Prepaid Ins) (28,000) (28,000) (28,000) (84,000)
Total Cash Disbursements 213,000 253,300 196,750 663,050
Prepaid Insurance That Is Expensed During the Quarter $ 9,000
Depreciation Expense Recognized During the Quarter $ 75,000
View Assumptions

/xl/drawings/drawing6.xml#'Part%20II%20Assumptions'!A1
HOME

/xl/drawings/drawing6.xml#Instructions!A1
Part II Cash Budget
Cash Budget
July August September Quarter
Cash Balance: Beginning 40,000 35,050 35,435 40,000
Add Cash Collections (From Sales Budget) 520,000 693,000 779,500 1,992,500
Total cash Available 560,000 728,050 814,935 2,032,500
Less Cash Disbursements
For Inventory (From Purchasing Budget) 280,950 336,125 288,000 905,075
For Operating Expenses (From Selling and Admin Budget) 213,000 253,300 196,750 663,050
For Equipment 200,000 90,000 - 290,000
For Cash Dividends 50,000 - - 50,000
For Interest (From Previous Month's Borrowing) $ 2,190 $ 2,080 4,270
Total Cash Disbursements 743,950 681,615 486,830 1,912,395
Excess (Deficiency) of Cash (183,950) 46,435 328,105 120,105
Financing
Borrowing 219,000 219,000
Repayment 11,000 208,000 219,000
Total Financing 219,000 11,000 208,000
Cash Balance: Ending 35,050 35,435 120,105 120,105
Outstanding Loan Balance 0
Interest on Borrowing (Due the Following Quarter) 0
SRS Educational Supplies Company
Budgeted Income Statement
For the Quarter Ended September 30
Sales ERROR:#REF! From sales budget
Cost of goods Sold ERROR:#REF!
Gross Margin ERROR:#REF!
Selling and administrative expenses
Shipping ERROR:#REF!
Other ERROR:#REF! From operating budget
Salaries and wages ERROR:#REF!
Advertising ERROR:#REF!
Prepaid insurance ERROR:#REF!
Depreciation ERROR:#REF! ERROR:#REF!
Net operating incomes ERROR:#REF!
Less interest expense ERROR:#REF! From cash budget
Net income ERROR:#REF!
SRS Educational Supply Company
Balance Sheet
September 30,
Assets
Current assets:
Cash $120,105 From cash budget
Accounts receivable ERROR:#REF! From sales budget - collections
Inventory ERROR:#REF! From purchase budget
Prepaid insurance ERROR:#REF!
Total current assets ERROR:#REF!
Buildings and equipment (net) ERROR:#REF! Beg equip + purch - depr
Total assets ERROR:#REF!
Liabilities and Equity
Accounts payable ERROR:#REF! From purchase budget - cash disb
Notes Payable 438,000 ERROR:#REF! From cash budget
Stockholder's equity
Capital stock 420,000 From beg balance sheet
Retained earnings ERROR:#REF! Beg RE + NI - dividends
Total liability and equity ERROR:#REF!
HOME

/xl/drawings/drawing7.xml#Instructions!A1
View Assumptions

/xl/drawings/drawing7.xml#'Part%20II%20Assumptions'!A1
Sheet2
Instructions Final Project II
Southern New Hampshire University
Final Project II
ACC 207 - Cost Accounting - STUDENT WORKBOOK
INSTRUCTIONS FOR FINAL PROJECT II (Due Module Seven)
IMPORTANT NOTES:
Make sure to completely review the Final Project II Rubric.
Use the data from Milestone One and begin work on your final presentation, which is due in Module Seven.
ITEMS TO COMPLETE FOR FINAL PROJECT II (Purple Tabs):
GENERAL
Use data from Milestone One and the Part II Assumptions tab (the red tab) for your statement preparation.
BUDGETED FINANCIAL STATEMENTS
Prepare a budgeted income statement.
Prepare a budgeted balance sheet.
See Rubric for Presentation Instructions
HOME

/xl/drawings/drawing8.xml#Instructions!A1
Part II Financials
SRS Educational Supplies Company
Budgeted Income Statement
For the Quarter Ended September 30th
Sales 1,985,000
Cost of Goods Sold 893,250
Gross Margin 1,091,750
Selling and Administrative Expenses
Shipping 99,250
Other 158,800
Salaries and Wages 255,000
Advertising 150,000
Prepaid Insurance 9,000
Depreciation 75,000
Net Operating Incomes 344,700
Less Interest Expense 4,270
Net Income 340,430
SRS Educational Supply Company
Balance Sheet
September 30th
Assets
Current Assets:
Cash $120,105
Accounts receivable 332,500
Inventory 34,650
Prepaid Insurance 9,000
Total Current Assets 496,255
Buildings and Equipment (Net) 1,075,000
Total Assets $1,571,255
Liabilities and Equity
Accounts Payable 102,825
Notes Payable 0
Stockholder's Equity 102,825
Capital Stock 420,000
Retained Earnings 1,048,430
Total Liability and Equity 1,571,255
View Assumptions

/xl/drawings/drawing9.xml#'Part%20II%20Assumptions'!A1
Home