In: Accounting
Lunar Artistry Company needs to purchase new etching and
finishing equipment. The owners hope to finance...
Lunar Artistry Company needs to purchase new etching and
finishing equipment. The owners hope to finance the costly
equipment with cash on hand and a short term loan from Erie Bank.
The CFO of Lunar Artistry Company has recently completed the sales
forecast. She projects sales to increase by 10% each month over the
previous month sales for the first quarter with the remaining
months remaining constant.
The controller has been asked to prepare the master monthly
budget for the first quarter 2021. In the process, the controller
has accumulated the following information:
- Projected Sales for December 2020 are $500,000. Credit sales
are 80% of total sales with immediate cash sales as the other 20%.
Of the credit sales, cash is collected 20% in the month of the sale
and the remainder in the month following the sale.
- Lunar’s cost of goods sold is generally 60% of the current
month sales. All inventory is purchased on account. 40% of
inventory purchases are paid for in the month of purchase with the
remaining 60% paid the month following the purchase.
- The controller has determined additional monthly expenses to be
as follows:
-
Salaries
$55,000 Paid monthly
-
Advertising
$20,000 Paid Monthly
- Property
Taxes
$ 2,900 Paid Feb 28 and Aug 31
- Sales Commissions 1.2% of monthly sales
- The owners of Lunar Artistry Company have selected etching and
finishing equipment costing $175,000. They plan to pay cash for the
equipment. If they do not have enough cash, assuming the company
can maintain a $25,000 balance, the owners will take a short term
loan from Erie Bank. The CFO has stated the current interest rate
on short term loans is 6% and she anticipates the need for a
six-month loan. Interest on short-term loans is payable
monthly.
- Interest is paid each March 31 and September 30 on the Mortgage
Payable. The interest rate on the mortgage is 4%
- The board of directors intends to declare a $40,000 dividend at
the end of the first quarter.
REQUIRED:
- Using the Excel template, complete the purchase budget,
cash disbursement budget and cash budget.
*PLEASE SHOW EXCEL FORMULAS*
Additional Information:
Projected BS Tab from Excel Template:
|
|
|
|
|
Projected Balance Sheet |
December 31, 2020 |
Cash |
$50,000 |
|
Accounts Payable |
$180,000 |
Accounts Receivable |
$270,000 |
|
Mortgage Payable |
$300,000 |
Inventory |
$154,000 |
|
Common Stock |
$500,000 |
Buildings & Equipment |
$626,000 |
|
Retained Earnings |
$120,000 |
Total Assets |
$1,100,000 |
|
Total Liabilities&Equity |
$1,100,000 |
Sales Budget Tab from Excel Template:
|
December 2020 |
January 2021 |
February-21 |
March 2021 |
First Quarter Sales |
Cash Sales |
$100,000 |
|
|
|
|
Credit Sales |
$400,000 |
|
|
|
|
Total Sales |
$500,000 |
|
|
|
|