Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Jeff, 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

Jeff, 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.)

Jeff, 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. Jeff 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.
  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 isnt automatic.
  4. Ignore taxes.

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_2

Step: 3

blur-text-image_3

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

Accounting

Authors: Michael J. Jones

3rd Edition

1119977185, 9781119977186

More Books

Students also viewed these Accounting questions