Question

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:

  1. 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.
  2. 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.
  3. The controller has determined additional monthly expenses to be as follows:
    1. Salaries                       $55,000 Paid monthly
    2. Advertising                 $20,000 Paid Monthly
    3. Property Taxes            $ 2,900 Paid Feb 28 and Aug 31
    4. Sales Commissions    1.2% of monthly sales
  4. 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.
  5. Interest is paid each March 31 and September 30 on the Mortgage Payable. The interest rate on the mortgage is 4%
  6. The board of directors intends to declare a $40,000 dividend at the end of the first quarter.

REQUIRED:

  1. 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

Solutions

Expert Solution


Related Solutions

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...
The ICan’tBelieveWhat’sHappening Company needs to decide whether or not to purchase a new piece of equipment....
The ICan’tBelieveWhat’sHappening Company needs to decide whether or not to purchase a new piece of equipment. The equipment costs $5.2 million (payable now). The equipment will provide before-tax cash inflows of $2.0 million a year at the end of each of the next four years. The equipment would be categorized as a 3-year tax class asset according to the MACRS system. Therefore use the following depreciation rates: 33%, 45%, 15%, and 7%, for each year respectively from year 1 through...
The ICan’tBelieveWhat’sHappening Company needs to decide whether or not to purchase a new piece of equipment....
The ICan’tBelieveWhat’sHappening Company needs to decide whether or not to purchase a new piece of equipment. The equipment costs $5.2 million (payable now). The equipment will provide before-tax cash inflows of $2.0 million a year at the end of each of the next four years. The equipment would be categorized as a 3-year tax class asset according to the MACRS system. Therefore use the following depreciation rates: 33%, 45%, 15%, and 7%, for each year respectively from year 1 through...
A company is considering purchasing new equipment. The purchase of the equipment is       expected to...
A company is considering purchasing new equipment. The purchase of the equipment is       expected to generate after tax savings of $12,600 each year for 8 years. The company can       borrow money at 6%. Assume annual compounding.         Determine the present value of the future cash inflows. Hint: the $12,600 are your annuity payments
To finance the purchase a new piece of equipment for his business Allan borrowed money from...
To finance the purchase a new piece of equipment for his business Allan borrowed money from his bank paying 5% interest. Before he has paid off the loan he sells the business and disposes of the equipment. There is insufficient sales proceeds to completely pay off the loan and he still has to pay the interest on the unpaid balance. He cannot claim the interest payments a deduction because his business has ceased. True False
Sinclair Company* A. EQUIPMENT REPLACEMENT Sinclair Company is considering the purchase of new equipment to perform...
Sinclair Company* A. EQUIPMENT REPLACEMENT Sinclair Company is considering the purchase of new equipment to perform operations currently being performed on different, less efficient equipment. The purchase price is $250,000, delivered and installed. A Sinclair production engineer estimates that the new equipment will produce savings of $72,000 in labor and other direct costs annually, as compared with the present equipment. She estimates the proposed equipment’s economic life at five years, with zero salvage value. The present equipment is in good...
A U.S. company needs to pay Japanese Yen for the equipment purchase in the next six...
A U.S. company needs to pay Japanese Yen for the equipment purchase in the next six months and would like to hedge its foreign exchange rate risk. Please construct a range forward strategy using foreign currency options. How should this U.S. company hedge by using the standard forward contract? How would you compare the hedge using the range forward strategy with the hedge using the standard forward contracts?
A company needs to purchase a new machine to maintain its level of production. The company...
A company needs to purchase a new machine to maintain its level of production. The company is considering three different machines. The costs, savings and service life related to each machine are listed in the table below. Machine A Machine B Machine C First cost $37500 $31000 $35000 Annual Savings $13500 $12000 $12750 Annual Maintenance $3,000 the first year and increasing by $600 every year thereafter 2500 2000 salvage value 5000 11000 13000 Service life 6 years 3 years 3...
LuLuLime (LLL) is a company that sells modern equipment. To purchase a new equipment for your...
LuLuLime (LLL) is a company that sells modern equipment. To purchase a new equipment for your company from LLL, you as a financial manager have narrowed down two equipment models which meet your performance requirements equally. However, the schedule of payments of two models are different. Model A requires yearly payment of $15,000 for 5 years with the first payment to be made today. Model B requires yearly payment of $15,500 for 5 years with the first payment to be...
A company is considering the purchase of new equipment for its production area. The equipment has...
A company is considering the purchase of new equipment for its production area. The equipment has an initial cost of $ 3,000 with operation and maintenance costs, as well as the market liquidation value as shown in the following table: Year Costs of operation Rescue value 1 $1,000 $1,500 2 $1,700 $1,000 3 $2,400 $500 4 $3,100 $0 Determine the Optimal Economic Life of this investment, if the MARR of the company is 12%
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT