Accounting excel project

profileSylviameng
acct1020excel3.xlsx
Home>Business & Finance homework help>Accounting homework help>Accounting excel project

Budget Data

September October November
Expected Sales (all sales are credit sales) $700,000 $800,000 $900,000
Collections of Accounts Receivable
Month of Sale 24%
Month following Sale 76%
Cost of Goods Sold percentage of sales 36%
Inventory Purchase Information
Purchases paid in current month 78%
Purchases paid in next month 22%
Purchases are all on credit
Planned ending inventory as a percentage of next month's Cost of Goods Sold 8%
Sales Commission Information
Sales commissions as a percentage of sales 6.5%
Sales commissions are paid the month after they are incurred.
Advertising Expense Information
Variable Rate 17.50%
Fixed Cost $60,000
Insurance policy Information
# of months of insurance coverage paid for each year 12
The policy period is from August 1 to July 31 each year
The premium was paid on July 31, 2019 $30,000
The premium will be charged to prepaid insurance
General and Administrative Expense Information
Fixed cost portion $60,000
Variable rate (as a percentage of sales) 15.00%
Selling and administrative expenses are paid in the month incurred
Fixed Asset Information
Purchase Land on September 15, 2019 150,000
Current Depreciation expense per month 10,000
Long-Term Loan Interest Information
The interest rate on the long-term debt per month is 1%
Interest is paid on the principle balance at the beginning of the month
Minimum Cash Balance $25,000
New borrowing must be in $1,000 increments
Tax Information
Income tax rate 25%
Taxes are paid in the month after they are incurred (expensed)
Dividend Information
Cash dividend declared on September 20, 2019 $25,000
Cash dividend paid on October 15, 2019

Beginning Balance Sheet

Bobcat Beverage Company
Balance Sheets
As of
August 31, 2019
ASSETS
CURRENT ASSETS
Cash $ 25,800
Accounts Receivable $ 595,000
Inventory $ 50,688
Prepaid Insurance $ 27,500
TOTAL CURRENT ASSETS $ 698,988
PROPERTY PLANT & EQUIPMENT
Land $ 100,000
Equipment $ 300,000
Accumulated Depreciation $ (100,000)
TOTAL PPE $ 300,000
TOTAL ASSETS $ 998,988
LIABILITIES & STOCKHOLDERS EQUITY
CURRENT LIABILITIES
Accounts Payable $ 81,859
Commissions Payable $ 59,500
Advertising Expense Payable $ 121,250
Income Taxes Payable $ 22,000
Dividends Payable $ - 0
TOTAL CURRENT LIABILITIES $ 284,609
LONG TERM DEBT $ 220,000
STOCKHOLDERS EQUITY
Common Stock $ 89,500
Retained Earnings $ 404,879
TOTAL STOCKHOLDERS EQUITY $ 494,379
TOTAL LIABILITIES & STOCKHOLDERS EQUITY $ 998,988

Sales Budget

Bobcat Beverage Company
Sales Budget
For the Month Ending
Section I: Project Sales September October November
Cash Sales $ - 0 $ - 0 $ - 0
Credit Sales $ 700,000 $ 800,000 $900,000
Total Projected Sales $ 700,000 $ 800,000 $ 900,000
Section II: Cash Recipts
Cash Sales $ - 0 $ - 0 $ - 0
From Prior Credit Sales $ 595,000 $ 532,000 $ 608,000
From Current Credit Sales $ 168,000 $ 192,000 $ 216,000
Total Cash Recipts $ 763,000 $ 724,000 $ 824,000
Total Sales Revenue on Income Statement $ 700,000 $ 800,000 $ 900,000
Account Recivable on Balance Sheet $ 168,000 $ 192,000 $ - 0
Fall 2013

Purchases Budget

Bobcat Beverage Company
Inventory Purchases Budget
Part 1 Inventory Purchase September October November
Cost of Good Sold (36%) $ 252,000 $ 288,000 $ 324,000
Desired Ending Inventory (8%) $ 20,160 $ 23,040 $ 25,920
Total Inventory Needs $ 272,160 $ 311,040 $ 349,920
Less: Beginning Inventory $ 50,688 $ 20,160 $ 23,040
Total Inventory Purchases $ 221,472 $ 290,880 $ 326,880
Part 2: Cash payments for inventory purchase $ - 0 $ - 0 $ - 0
Add: for prior credit pruchase $ 81,859 $ 48,724 $ 63,994
For current credit purchase $ 172,748 $ 226,886 $ 254,966
Total cash payments for inventory purchase $ 254,607 $ 275,610 $ 318,960
COGS on income statement $252,000 $288,000 $324,000
inventory ending on balance sheet $ 20,160 $ 23,040 $ 25,920
accounts payable on balance sheet $ 48,724 $ 63,994 $ - 0

Selling and Admin Budget

Section I: projected Variable
Commisions
Variable Supplies
Total Projected Variable S&A Expenses
Section II: Projected Fixed S&A
Fixed Supplies
Rent
Advertising
Depreciation
Utilities
Insurance
Total Projected Fixed
Section III: Cash Pmts for S&A Expense
Commissions
Supplies
Rent
Advertising
Depreciation
Utilities
insurace
Total Cash Pmts for S&A

Cash Budget

September October November
Section I: Total Cash Availible
Beginning Balance
Cash Reciepts
Total Cash Availible
Section II: Total cash Payments
For Investing Purchases
For S&A Expenses
For Interest
For Taxes
For Investing Activities
For Dividends
Total Cash Payments
Section III: Financing
Surplus
Borrowings
Cash Ending Balance
Long Term Debt Ending Balance

Proforma Income Statement

Revenue August September October December
Gross sales
Less: sales returns and allowances
Net sales
Cost of sales
Beginning inventory
Add: goods purchased
Total goods available
Less: ending inventory
Total cost of goods sold
Gross profit
Operating expenses
Salaries and wages
Commissions
Advertising
Depreciation
other
other
other
Total selling expenses
General and administration
Insurance
other
other
other
Total general and administration
Total operating expenses
Net income before taxes
Income taxes
Net income after taxes
Net income

Balance Sheets

Bobcat Beverage Company
Balance Sheets
As of
August 31, 2019 September 30, 2019 October 31, 2019
ASSETS
CURRENT ASSETS
Cash $ 25,800
Accounts Receivable $ 595,000
Inventory $ 50,688
Prepaid Insurance $ 27,500
TOTAL CURRENT ASSETS $ 698,988
PROPERTY PLANT & EQUIPMENT
Land $ 100,000
Equipment $ 300,000
Accumulated Depreciation $ (100,000)
TOTAL PPE $ 300,000
TOTAL ASSETS $ 998,988
LIABILITIES & STOCKHOLDERS EQUITY
CURRENT LIABILITIES
Accounts Payable $ 81,859
Commissions Payable $ 59,500
Advertising Expense Payable $ 121,250
Income Taxes Payable $ 22,000
Dividends Payable $ - 0
TOTAL CURRENT LIABILITIES $ 284,609
LONG TERM DEBT $ 220,000
STOCKHOLDERS EQUITY
Common Stock $ 89,500
Retained Earnings $ 404,879
TOTAL STOCKHOLDERS EQUITY $ 494,379
TOTAL LIABILITIES & STOCKHOLDERS EQUITY $ 998,988

Determining the Balances

September October
1 Total Cash Receipts
2 Inventory Purchases
3 Cash Payments for Inventory Purchases
4 Total Variable Selling & Administrative Costs
5 Total Fixed Selling & Administrative Costs
6 Total Cash Payments (S&A Budget)
7 Total Cash Surplus (Deficit) YOU MUST SHOW A DEFICIT AS A NEGATIVE NUMBER AND A SURPLUS AS A POSITIVE NUMBER!
8 Total New Borrowing (Repayments) SHOW REPAYMENTS AS A NEGATIVE NUMBER (DO NOT ROUND!!! Or you will get this WRONG!)
9 Contribution Margin
10 Interest Expense
11 Pre-tax income
12 Income tax expense
13 Accounts Receivable
14 Inventory
15 Prepaid Insurance
16 Accumulated Depreciation
17 Accounts Payable
18 Commissions Payable
19 Long-Term Debt
20 Retained Earnings
Percentage of this sheet that is correct 0.00%
Grade for entire assignment 0.00

Grade

You are using the template for the: Spring 2021 56-06439 Cash Receipts $ 763,000.0000 $ 724,000.0000 1 1 0 0 Fall 0 ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
Your Grade on this assignment is: 0 Inventory Purchases $ 224,352.0000 $ 290,880.0000 1 1 0 0
Percentage of Assignment Correct 0
Note that your grade needs to be between 0 and 25. Cash Payments - Inv Purch $ 256,853.5600 $ 276,243.8400 1 1 0 0
If you have an error in the grade space you have deleted
an important formula that evaluates the possibility of Variable S and A $ 273,000.0000 $ 312,000.0000 1 1 0 0
academic honesty. You must redo the assignment with a
blank template or you will receive a zero. Fixed S and A $ 132,500.0000 $ 132,500.0000 1 1 0 0
Total Cash Payments (cash budget) $ 345,750.0000 $ 408,000.0000 1 1 0 0
Surplus/Deficit $ 11,996.4400 $ 28,337.6000 1 1 0 0
New Borrowing (Repayments) $ 14,000.0000 $ (3,337.6000) 1 1 0 0
Contribution Margin $ 175,000.0000 $ 200,000.0000 1 1 0 0
Interest Expense $ 2,200.0000 $ 2,340.0000 1 1 0 0
Pretax Income $ 40,300.0000 $ 65,160.0000 1 1 0 0
Income Tax Expense $ 10,075.0000 $ 16,290.0000 1 1 0 0
Acct Rec $ 532,000.0000 $ 608,000.0000 1 1 0 0
Inventory $ 23,040.0000 $ 25,920.0000 1 1 0 0
Prepaid Insurance $ 25,000.0000 $ 22,500.0000 1 1 0 0
Accum Depreciation $ 110,000.0000 $ 120,000.0000 1 1 0 0
Accts Payable $ 49,357.4400 $ 63,993.6000 1 1 0 0
Commissions Payable $ 45,500.0000 $ 52,000.0000 1 1 0 0
Long-Term Debt $ 234,000.0000 $ 230,662.4000 1 1 0 0
Retained Earnings $ 410,104.0000 $ 458,974.0000 1 1 0 0
0 0
August 31, 2011 Raw 0
ASSETS Perc 0
CURRENT ASSETS Grade 0
Cash $ 30,800
Accounts Receivable $ 595,000
Inventory $ 50,688
Prepaid Insurance $ 33,000
TOTAL CURRENT ASSETS $ 709,488
PROPERTY PLANT & EQUIPMENT
Land $ 100,000
Equipment $ 400,000
Accumulated Depreciation $ (100,000)
TOTAL PPE $ 400,000
TOTAL ASSETS $ 1,109,488
LIABILITIES & STOCKHOLDERS EQUITY
CURRENT LIABILITIES
Accounts Payable $ 81,859
Commissions Payable $ 59,500
Advertising Expense Payable $ 121,250
Income Taxes Payable $ 22,000
Dividends Payable $ - 0
TOTAL CURRENT LIABILITIES $ 284,609
LONG TERM DEBT $ 320,000
STOCKHOLDERS EQUITY
Common Stock $ 100,000
Retained Earnings $ 404,879
TOTAL STOCKHOLDERS EQUITY $ 504,879
TOTAL LIABILITIES & STOCKHOLDERS EQUITY $ 1,109,488
September October November
Sales 700000 800000 900000
Collections this month 0.3
Collections last month 0.7
Cost of Goods Sold 0.36
Desired Ending Inventory 0.09
Payments this month 0.85
Payments last month 0.15
Commissions 0.07
Advertising variable 0.125
Advertising Fixed 15000
G and A Variable 0.1
G and A Fixed 50000
Depreciation 20000
Insurance 3000
Purchase land 150000
Tax rate 0.3
Interest Rate 0.01
Cash Dividend 40000
Sales $700,000 $800,000 $900,000
From Sales this month $210,000 $240,000 $270,000
From Sales last month $595,000 $490,000 $560,000
Total Cash Receipts $805,000 $730,000 $830,000
Cost of Goods Sold $252,000 $288,000 $324,000
Desired Ending Inventory $25,920 $29,160
Less:Beginning Inventory -$50,688 -$25,920
TOTAL PURCHASES $227,232 $291,240
For purchases this month $193,147 $247,554
For Purchases last month $81,859 $34,085 $43,686
Total Cash Payments $275,006 $281,639
Commissions $49,000 $56,000
Advertising $87,500 $100,000
G and A $70,000 $80,000
Total Variable Costs $206,500 $236,000
Advertising $15,000 $15,000
G and A $50,000 $50,000
Depreciation $20,000 $20,000
Insurance $3,000 $3,000
Total Fixed Costs $88,000 $88,000
Commissions $59,500 $49,000 $56,000
Advertising $121,250 $102,500 $115,000
G and A $120,000 $130,000
Depreciation $0 $0
Insurance $0 $0
Total Cash Payments $300,750 $281,500
Beginning Cash $30,800 $202,844
Cash Receipts $805,000 $730,000
Total Cash Available $835,800 $932,844
For Purchases $275,006 $281,639
For S and A $300,750 $281,500
For Land $150,000 $0
For Interest $3,200 $4,380
For Taxes $22,000 $45,090
For Dividends $0 $40,000
Total Cash Payments $750,956 $652,609
Surplus (Deficit) $84,844 $280,235
New Borrowing (repayments) $118,000 -$250,235
ENDING CASH $202,844 $30,000
Beginning LTD $320,000 $438,000
New Borrowing $118,000
Repayments -$250,235
Ending LTD' $438,000 $187,765
Sales $700,000 $800,000
Less: VC
COGS -$252,000 -$288,000
Variable S and A -$206,500 -$236,000
Contribution margin $241,500 $276,000
Less: FC
Fixed S and A -$88,000 -$88,000
Interest -$3,200 -$4,380
Pretax Income $150,300 $183,620
Less Income Taxes -$45,090 -$55,086
Net Income $105,210 $128,534
ASSETS
CURRENT ASSETS
Cash $202,844 $30,000
Accounts Receivable $490,000 $560,000
Inventory $25,920 $29,160
Prepaid Insurance $30,000 $27,000
TOTAL CURRENT ASSETS $748,764 $646,160
PROPERTY PLANT & EQUIPMENT
Land $250,000 $250,000
Equipment $400,000 $400,000
Accumulated Depreciation -$120,000 -$140,000
TOTAL PPE $530,000 $510,000
TOTAL ASSETS $1,278,764 $1,156,160
LIABILITIES & STOCKHOLDERS EQUITY
CURRENT LIABILITIES
Accounts Payable $34,085 $43,686
Commissions Payable $49,000 $56,000
Advertising Expense Payable $102,500 $115,000
Income Taxes Payable $45,090 $55,086
Dividends Payable $40,000 $0
TOTAL CURRENT LIABILITIES $270,675 $269,772
LONG TERM DEBT $438,000 $187,765
STOCKHOLDERS EQUITY
Common Stock $ 100,000 $ 100,000
Retained Earnings $ 470,089 $ 598,623
TOTAL STOCKHOLDERS EQUITY $ 570,089 $ 698,623
TOTAL LIABILITIES & STOCKHOLDERS EQUITY $1,278,764 $1,156,160