I am dealing with a problem in an online textbook (cramster does not have it) wh
ID: 2370628 • Letter: I
Question
I am dealing with a problem in an online textbook (cramster does not have it) which has given me this XL spreadsheet that i copied into the text box. It is required that i use the given information to "fill in the blanks" thus preparing a budget worksheet and corresponding pro forma income stmnet and pro forma balance sheet.given is a set of assumptions and costs as well as the balance sheet as of the beginning of the period, monthly sales and i ,must basically find the cash recipts and expenditures. from their i use the information calculated to re state the finacial documents.
if this is too hard to understand just note that you cannot understand the problem and I will not give the answer a poor rank. i am just very confused on making a budget worksheet so i cannot finish the corresponding financial statements.
Assumptions
Cost of Sales as a Percent 45%
Sales collected in month of sale 30%
Sales collected in month after sale 70%
Variable Cost (% of sales) 9%
Sales Commission (% of sales) 6%
Total Fixed Costs (per month) 120,000
Depreciation (per month) 70,000
Balance Sheet, December 31, 2008
Cash 260,000
Account Payable 100,000
A/R 275,000
Accrued Commissions 60,000
Inventory 75,000
Common Stock 200,000
Fixed Assets 350,000
Retained Earnings 600,000
960,000 960,000
January February March April May
Sales 370,000 430,000 525,000 540,000 390,000
Monthly Sales for the Period Ended March 31, 2009
January February March Total Apil May
Total Sales ($) 370,000 430,000 525,000 1,325,000 540,000 390,000
Inventory Budget for Quarter Ended March 31, 2009
Cost of Monthly Sales
Add: Desired End Inv.
Feb (430,000*40%)
Mar (525,000*40%)
Apr (540,000*40%)
May (390,000*40%)
Required Ending Inventroy
Total Needs
Less: Beginning Inventory
Purchase Requirements 521,250 243,000 175,500 939,750
Cash Budget for Quarter Ended March 31, 2009
January February March Total
Beginning Balance
Cash Recepts
Total Cash Available 646,000 790,700 617,050 1,492,500
Cash Expenditures
Purchases
Variable Costs
Sales Commissions
Fixed Expenses
Total Cash Expenses 243,300 632,150 366,060 1,241,500
Ending Cash Balance 402,700 158,550 251,000 251,000
Cash Receipts for Quarter Ended March 31, 2009
January February March Total
Account Receivable
January Sales
February Sales
March Sales
Total Collections 386,000 388,000 458,500 1,232,500
Pro Forma Income Statement
January February March Total
Sales
Cost of Goods Sold
Gross Margin
Less Expenses
Variable Costs Sales Commission
Fixed Expense
Total Expenses
Net Income
Pro Forma Balance Sheet
Cash Accounts Payable
Accounts Receivable Commissions Payable
Inventory Common Stock
Fixed Assets Retained Earnings
Total Assets- Total Liabilities and Net Worth
Explanation / Answer
Inventory Budget
Cash Budget
Income Statement
Balance Sheet:
Inventory Budget
Quarter January February March Total April May Total Sales $370,000 $430,000 $525,000 $1,325,000 $540,000 $390,000 $2,255,000 Cost of sales (45% of sales) 166500 193500 236250 $596,250 243000 175500 $1,014,750 Add: Desired Ending Inventory 172000 210000 216000 156000 Less: Beginning Inventory 75000 172000 210000 216000 156000 Purchase requirements 263500 231500 242250 $737,250 183000 19500 $939,750Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.