Module 6 Excel Workbook Assignment

A relatively new company, Dream Ice Cream, Inc., has determined they need to engage in a budgeting process as part of their new strategic plan. The Company has asked you to provide the following items for the months of January, February, and March of 2020: A) a Purchases Budget; B) a Cash Budget; C) a Budgeted Income Statement; and D) a Budgeted Balance Sheet. They have provided you with the planning document below and also, with their December 31, 2019 Balance Sheet. Please complete the four budget documents requested on Worksheet Tabs A, B, C, and D of this Workbook. Check Figures are provided for you on each Worksheet.

Dream Ice Cream, Inc. PLANNING DATA FOR 2020

Actual November 2019 Sales $254,000

Actual December 2019 Sales $263,000

Expected 2020 Monthly Sales

January $197,000

February $148,000

March $136,000

April $167,000

All Sales are on Credit

Collections of Accounts Receivable:

Month of Service 42.00%

Month following Service 58.00%

Cost of Goods Sold as a percentage of selling price. 60.00%

Planned ending inventory as a percentage of next month's required inventory. 65.00%

Purchases are are all on credit.

Actual Purchases made in December 2019 $132,060

Purchases paid in current month 55.00%

Purchases paid in next month 45.00%

Marketing expenses have a fixed and variable portion:

Fixed portion $3,450 The fixed portion of the marketing is paid in the current month.

Variable portion as a percentage of sales 1.10% The variable portion is paid one month after the month incurred.

Total marketing for month.

Sales commission as a percent of sales 2.00% .Sales commission are paid two months after the month incurred.

Selling and Administrative expenses have a fixed and variable portion:

Fixed portion is paid in the month incurred $23,100

Variable portion as a percentage of Cost of Goods Sold 1.50%

Variable S&A expenses are paid one month after the month incurred

Insurance policy:

Months of insurance coverage paid for 12

The policy period is from February 1 to January 31 each year

The premium to be paid on February 1, 2020 is $15,660

The premium will be charged to prepaid insurance

Insurance premium paid on February 1, 2019 $15,000

Depreciation expense per month $5,320

The annual interest rate on the long term debt is 4.15%

Interest is incurred on the principal balance outstanding each month.

Interest is paid on a quarterly basis.Interest for December to February will be paid March 1.

No principal payments are scheduled to be made on the long-term debt until September of 2021.

The Company's Board of Directors plans to declare a dividend on February 20. $12,000

It will be paid on March 5.

Income Tax rate 21.00%

Accrued taxes for the three previous months are paid on the 15th of January, April, July and October.

MBA 6315

Use the information provided on the December 31, 2019 Balance Sheet shown below to help you create the budgeting documents requested.

Dream Ice Cream, Inc.

BALANCE SHEET

December 31, 2019

ASSETS

Current Assets

Cash $303,200

Accounts Receivable 152,540

Inventory 76,830

Prepaid Insurance 1,250

Total Current Assets 533,820

Property Plant and Equipment

Equipment 319,200

Accumulated Depreciation (63,840)

Total Property Plant and Equipment 255,360

Total Assets $789,180

LIABILITIES AND SHAREHOLDERS EQUITY

LIABILITIES

Current Liabilities

Accounts Payable - Purchases $59,427

Sales Commission Payable 10,340

Selling and Administrative Payable 2,367

Interest Payable 415

Marketing Payable 2,893

Income Taxes Payable 15,320

Dividends Payable -

Total Current Liabilities 90,762

Long Term Debt 120,000

Total Liabilities 210,762

SHAREHOLDERS' EQUITY

Common Stock 104,000

Retained Earnings 474,418

Total Shareholders' Equity 578,418

Total Liabilities and Shareholders Equity $789,180