Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

The last two pictures are the assignment. Need help with a Pro Forma income statement and a Pro Forma cash budget? What do they look

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed

The last two pictures are the assignment. Need help with a Pro Forma income statement and a Pro Forma cash budget? What do they look like and how do they work with the given data below?

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed
A | B i c D Awesome, LLC Projected Balance Sheet at December 31, 2020 'Looowmmb'w'na'ak Assets Liabilities 8:. Stockholder's Equity Current Assets: Current Liabilities: Cash 20,000.00 Accounts Payable 80,000.00 Accounts Receivable 110,000.00 Inventory 150,000.00 Total Liabilities 80,000.00 10 11 Long Term Assets: Stockholder's Equity 12 Building 8; Equiptment, net 200,000.00 Common Stock 300,000.00 13 Retained Earnings 100,000.00 14 15 Total Stockholder's Equity 400,000.00 16 _17 Total Assets: ($80,000.00 Total Liabilities & Stockholder's Equity: S 480,000.00 3 E 20 i Beginning Balance Sheet Budgeting Assumptions Purchase Budget Cash Receipts Budget l t G) information: Awesome, LLC. is a wholesale electrical distributor. The following projections have been made for the 2021 budget: Sales Forecast $200,000 $220,000 $130,000 $210,000 CostDara: $200,000 Average Purchase Price of Product: $130,000 60% of selling price $175,000 $1?0,000 Commission to Sales People: $185,000 10% of sales $200,000 $210,000 Other Operating Expenses: $220,000 $42,000 per month, including $2,000 depreciation, not including interest expense (if any.) $230,000 Awesome, LLC Protected Balance Sheet at December 31, 2020 Assets Equities Cash 3: 20,000 Accounts Payable Accounts Receivable 110,000 (for merchandise) '3 30, 000 Inventory 150,000 Common Stock 300,000 Building 3: Equipment, net 200,000 Retained Earnings 100,000 Total $430,000 Total 430 000 Other Information: Awesome maintains inventory at 90% of the coming month's sales requirements. Sales are collected 40% in the month of sale, 53% in the following month. Bad debts are written off immediately, if not collected in the month following the sale. Purchases are paid 30% in the month of purchase, 70% in the followmg month All other expenses requiring cash are paid inthe month incurred. The board of directors plans to declare a $4,000 dividend on May 10, 2021, payable June 2, 202 1. The company wishes to maintain aminimum cash balance of $15,000. A line of credit has been negotiated, to be used only when necessary, at an interest rate of 12%. h. No capital purchases are planned. eeces 03 Required: In Excel, prepare a monthly budget for January to December, 202 1 , with totals for the year, to include: 1. A budgeted income statement 2. A purchase budget 3. A cash receipts budget 4. A cash disbursements budget 5. A cash budget and 6. A proforma balance sheet as of December 31, 2021. Notes 3: Hints... 1. If you feel that some critical piece of information is missing, make and highlight an appropriate assumption. If you wish, you can check with me about the need for and validity of a particular assumption. 2. I recommend constructing the statements for one month and then copying the formulas over to the remaining months. I will look at the copy of your spreadsheet on the disk to see what formulas you have used to develop your model. Make sure you provide instructions for other users, if the loan calculation isn't automatic. Ignore taxes. :P'E" A o | L 1.) Awesome, LLC. Pro Forma Balance Sheet at December 31, 2021 Assets Liabilities 8: Stockholder's Equityr Current Assets: Liabilities: Cash: 213,713.33 Accounts Payable: 96,180.00 Account's Receivable: 132,000.00 Inventory: 124,200.00 Total Liabilities: 96,180.00 Stockholder's Equity: 176,000.00 Common Stock: 300,000.00 Retained Earnings: 249,733.33 Total Stockholder's Equity: 549,733.33 4 5 6 7 Total Assets: 645,913.33 Total Liabilities & Stockholder's Equity: 645,913.33 1 i '0 4 b Cash Disbursement Budget Cash Budget Income Statement Balance Sheet Statement of Sto (-9 \fA A | a | L | L} t. r e 1 2 Budgeted Income Statement 3 Awesome, LLC 4 For the year ended December 31, 2021 5 6 Sales: . 2,350,000.00 _7 Cost of Good Sold: . {1,410,000.00} 8 Gross Profit: . 940,000.00 9 10 Operating Expenses: _11 Selling Expenses: . {236,000.00} 12 Depreciation Expense: . {24,000.00} 13 Other Operating Expenses: . {480,000.00} 14 Total Operating Expenses: . {740,0 _____ ]: is | _I 16 Total Operating Income: . 200,000.00 1? 18 Interest Expense: . _19 Bad Debt Expense: . {40.26667} 20 21 Net Income: 153,733.33 22 23 4 b ...| Cash Receipts Budget { Cash DisbursementBudget {Cash Budget Income Statement H I u :5. u 1 Awesome, LLC. 2 Statement of Stockholder's Equity 3 at December 31, 2021 1- 5 Common Stock Retained Earnings Total Stockholder's Equity 5 Beginning Balance: 300,000.00 5 100,000.00 5 400,000.00 7 Add: Issuance of Common Stock: , S 400,000.00 3 Add: Net Income: 5 153,733.33 5 553,733.33 3 Less: Dividends Paid 5 {4,000.00} 5 549,733.33 0 Ending Balance: 300,000.00 5 249,733.33 5 549,733.33 1_ 2_ 3_ i -. \"a :1 5_ 7_ 3_ 9_ LO 4 v Cash Budget Income Statement Balance Sheet Statement of Stockholder's Equi (-9

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Managerial Accounting

Authors: James Jiambalvo

7th Edition

1119577721, 978-1119577720

More Books

Students also viewed these Accounting questions

Question

Define Heideggers terms throwness, Mitwelt, and Umwelt.

Answered: 1 week ago

Question

Do not get married, wait until I come, etc.

Answered: 1 week ago

Question

Do not come to the conclusion too quickly

Answered: 1 week ago

Question

Engage everyone in the dialogue

Answered: 1 week ago