Question

Be professional. Proofread your work and make it understandable to a non-accountant reader.

You are the VP of Planning at Awesome, LLC. You are required to prepare a 2022 budget for the company president.

Be sure to include all relevant budget schedules, including pro forma income statements and a cash budget. Clearly identify if any loans are required, and why.

Present an Excel file with all spreadsheets and a Microsoft Word Document file explaining any assumptions you make.  

Information:

Awesome, LLC. is a wholesale electrical distributor. The following projections have been made for the 2021 budget:

Sales Forecast

January, 2021	$200,000
February	$220,000
March	$180,000
April	$210,000
May	$200,000
June	$180,000
July	$175,000
August	$170,000
September	$185,000
October	$200,000
November	$210,000
December	$220,000
January, 2022	$230,000

Cost Data:

Average Purchase Price of Product:  

60% of selling price

Commission to Sales People:             

10% of sales

Other Operating Expenses:                

$42,000 per month, including $2,000 depreciation,

not including interest expense (if any.)

             Awesome, LLC Projected Balance Sheet at December 31, 2020

Assets		Equities
Cash	$ 20,000	Accounts Payable
Accounts Receivable	110,000	(for merchandise)	$ 80,000
Inventory	150,000	Common Stock	300,000
Building & Equipment, net	200,000	Retained Earnings	100,000
Total	$480,000	Total	$480,000

Other Information:

1. Awesome maintains inventory at 90% of the coming month's sales requirements.

2. Sales are collected 40% in the month of sale, 58% in the following month.

3. Bad debts are written off immediately, if not collected in the month following the sale.

4. Purchases are paid 30% in the month of purchase, 70% in the following month.

5. All other expenses requiring cash are paid in the month incurred.

6. The board of directors plans to declare a $4,000 dividend on May 10, 2021, payable June 2, 2021.

7. The company wishes to maintain a minimum cash balance of $15,000. A line of credit has been negotiated, to be used only when necessary, at an interest rate of 12%.

8. No capital purchases are planned.

Required:

In Excel, prepare a monthly budget for January to December, 2021, 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 pro forma balance sheet as of December 31, 2021.

Notes & 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.

3. Make sure you provide instructions for other users, if the loan calculation isn’t automatic.

4. Ignore taxes.

Answer 1

Schedule for Budgeted Cash Disbursements and collection, (Cash Budget)

Jan Feb March April $20,000.00 $34,000.00 $52,400.00 $69,200.00 May June Nov Dec July Aug Sep Oct $83,200.00 $100,800.00 $110,400.00 $120,200.00 $128,600.00 $138,000.00 $151,600.00 $168,800.00 $80,000.00 $88,000.00 $72,000.00 $84,000.00 $80,000.00 $72,000.00 $70,000.00 $68,000.00 $74,000.00 $80,000.00 $84,000.00 $88,000.00 $110,000.00 $116,000.00 $127,600.00 $104,400.00 $121,800.00 $116,000.00 $104,400.00 $101,500.00 $98,600.00 $107,300.00 $116,000.00 $121,800.00 $190,000.00 $204,000.00 $199,600.00 $188,400.00 $201,800.00 $188,000.00 $174,400.00 $169,500.00 $172,600.00 $187,300.00 $200,000.00 $209,800.00 Particulars Opening Cash Balance (A) Add: Receipts Sales collection in same month Sales collection in next month Total receipts (B) Less: Payments Purchases in same month Purchases next month Commission to sales Dividend Declared & Paid Other Operating Expenses Total Payments (C) Closing Cash balance (A+B-C) Shortage/ Excess Closing cash Balance with Credit $36,000.00 $39,600.00 $32,400.00 $37,800.00 $36,000.00 $32,400.00 $31,500.00 $30,600.00 $33,300.00 $36,000.00 $37,800.00 $39,600.00 $80,000.00 $84,000.00 $92,400.00 $75,600.00 $88,200.00 $84,000.00 $75,600.00 $73,500.00 $71,400.00 $77,700.00 $84,000.00 $88,200.00 $20,000.00 $22,000.00 $18,000.00 $21,000.00 $20,000.00 $18,000.00 $17,500.00 $17,000.00 $18,500.00 $20,000.00 $21,000.00 $22,000.00 $4,000.00 $40,000.00 $40,000.00 $40,000.00 $40,000.00 $40,000.00 $40,000.00 $40,000.00 $40,000.00 $40,000.00 $40,000.00 $40,000.00 $40,000.00 $176,000.00 $185,600.00 $182,800.00 $174,400.00 $184,200.00 $178,400.00 $164,600.00 $161,100.00 $163,200.00 $173,700.00 $182,800.00 $189,800.00 $34,000.00 $52,400.00 $69,200.00 $83,200.00 $100,800.00 $110,400.00 $120,200.00 $128,600.00 $138,000.00 $151,600.00 $168,800.00 $188,800.00 -$19,000.00 -$37,400.00 $54,200.00 $68,200.00 -$85,800.00 $95,400.00 -$105,200.00 -$113,600.00 -$123,000.00-$136,600.00 -$153,800.00 -$173,800.00 $34,000.00 $52,400.00 $69,200.00 $83,200.00 $100,800.00 $110,400.00 $120,200.00 $128,600.00 $138,000.00 $151,600.00 $168,800.00 $188,800.00

Budgeted Income Statement:

Budgeted Income Statement Budgeted Revenue $2,350,000.00 Less: COGS Opening Stock $150,000.00 Budgeted Purchases $1,410,000.00 Closing stock -$207,000.00 $1,353,000.00 Budgeted Gross Profit $997,000.00 Less: operating expenses Depreciation $24,000.00 Other Operating Expenses $480,000.00 Baddebt expenses $47,000.00 Commission $235,000.00 Budgeted net Income $211,000.00

Budgeted Balance Sheet:

Liabilities Common Stock Retained Earnings Account Payable Budgeted Balance Sheet Amount Assets $300,000.00 Building & Equipments, net $307,000.00 Inventory $92,400.00 Account Receivable Cash Amount $176,000.0C $207,000.00 $127,600.00 $188,800.00 $699,400.00 $699,400.00

Working Note:

Particulars Nov Dec Jan Sales Jan Feb March April May $200,000.00 $220,000.00 $180,000.00 $210,000.00 $200,000.00 $198,000.00 $162,000.00 $189,000.00 $180,000.00 $162,000.00 $150,000.00 $198,000.00 $162,000.00 $189,000.00 $180,000.00 $120,000.00 $132,000.00 $108,000.00 $126,000.00 $120,000.00 Closing Stock (90%) Opening Stock Purchases (60%) June July Aug Sep Oct $180,000.00 $175,000.00 $170,000.00 $185,000.00 $200,000.00 $157,500.00 $153,000.00 $166,500.00 $180,000.00 $189,000.00 $162,000.00 $157,500.00 $153,000.00 $166,500.00 $180,000.00 $108,000.00 $105,000.00 $102,000.00 $111,000.00 $120,000.00 0 $210,000.00 $198,000.00 $189,000.00 $126,000.00 $220,000.00 $230,000.00 $207,000.00 $198,000.00 $207,000.00 $132,000.00 $138,000.00 Sales collection in same month (40%) Sales collection next month (58%) Baddebt (2%) - written off $80,000.00 $88,000.00 $72,000.00 $116,000.00 $127,600.00 $104,400.00 $4,000.00 $4,400.00 $3,600.00 $84,000.00 $80,000.00 $121,800.00 $116,000.00 $4,200.00 $4,000.00 $72,000.00 $70,000.00 $104,400.00 $101,500.00 $3,600.00 $3,500.00 $68,000.00 $74,000.00 $80,000.00 $98,600.00 $107,300.00 $116,000.00 $3,400.00 $3,700.00 $4,000.00 $84,000.00 $121,800.00 $4,200.00 $88,000.00 $92,000.00 $127,600.00 $133,400.00 $4,400.00 $4,600.00 Purchases payment same month(30%) Purchases payment next month(70%) $36,000.00 $84,000.00 $39,600.00 $92,400.00 $32,400.00 $75,600.00 $37,800.00 $88,200.00 $36,000.00 $84,000.00 $32,400.00 $75,600.00 $31,500.00 $73,500.00 $30,600.00 $71,400.00 $33,300.00 $77,700.00 $36,000.00 $84,000.00 $37,800.00 $88,200.00 $39,600.00 $41,400.00 $92,400.00 $96,600.00 Commission to sales (10%) $20,000.00 $22,000.00 $18,000.00 $21,000.00 $20,000.00 $18,000.00 $17,500.00 $17,000.00 $18,500.00 $20,000.00 $21,000.00 $22,000.00 $23,000.00

Purchases are calculated as: 60% of total sales

COGS = Opening inventory + Purchases - Closing stock