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:
a. Salaries $55,000 Paid monthly
b. Advertising $20,000 Paid Monthly
c. Property Taxes $ 2,900 Paid Feb 28 and Aug 31
d. 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 sales budget, cash receipt budget, purchase budget, cash disbursement budget and cash budget.
2. Prepare the Budgeted Income Statement, Budgeted Statement of Equity and Budgeted Balance Sheet.
3. As the CFO, what can be a short memo to the owners recommending whether to purchase the equipment and an appropriate month to make the purchase.
Below is the format for writing answers.
Cash Accounts Receivable Inventory Buildings & Equipment Total Assets Projected Balance Sheet December 31, 2020 $50,000 Accounts Payable $270,000 Mortgage Payable $154,000 Common Stock $626,000 Retained Earnings $1,100,000 Total Liabilities & Equity $180,000 $300,000 $500,000 $120,000 $1,100,000 January 2021 February-21 March 2021 First Quarter Sales Cash Sales Credit Sales Total Sales December 2020 $100,000 $400,000 $500,000 Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Bu January 2021 February-21 March 2021 First Quarter Sales Cash Sales Cash collections from credit sales Dec Cash collections from credit sales Jan Cash collections from credit sales Feb Cash collections from credit sales Mar Total Cash Receipts Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursen A B D F E March 2021 December 2020 January 2021 February-21 First Quarter Sales Budgeted Cost of Goods Sold Plus: Desired Ending Inventory S Less: Expected beginning Inventory $ = Purchases Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Budget B D E F G January 2021 February-21 March 2021 First Quarter Sales Cash Payments from e Inventory Purchases - Deo Cash Payments from B Inventory Purchases - Jan Cash Payments from - Inventory Purchases - Feb Cash Payments from 5 Inventory Purchases - Mar U Other Cash Expenses: Salaries e Advertising 0 Sales Commissions 1 2 3 4 5 Total Cash Disbursements 6 8 9 0 1 2 3 4 5 6 Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Budget Cash Budget A B D E F January 2021 February-21 March 2021 First Quarter Sales 1 2 Beginning Cash Balance 3 Add: Cash Receipts 4 Less: Cash Disbursements 5 Plus or Minus Other Items: 6 7 9 10 11 12 13 14 15 16 17 18 Ending Cash Balance 19 20 21 22 23 24 25 26 27 28 29 30 Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Budget Cash Budget A D E F G H 1 - L K M N O P B Lunar Artistry Company Projected Income Statement March 31, 2021 1 2 3 4 5 Revenue 6 7 Expenses 8 9 10 11 12 13 10 14 15 16 17 18 19 Net Income 20 21 Net Income 22 23 24 25 26 27 28 29 30 Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Budget Cash Budget Income Statement D E F H 1 L N o P Q Lunar Artistry Company Projected Statement of Equity March 31, 2021 Beginning Balance of Retained Earnings Add: Net Income Less: Dividend Ending Balance of Retained Earnings Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Budget Cash Budget Income Statement Statement of Equity Lunar Artistry Company Projected Balance Sheet March 31, 2021 Accounts Payable Mortgage Payable Cash Accounts Receivable nventory Buildings & Equipment Common Stock Retained Earnings Total Assets Total Liabilities& Equity Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Budget Cash Budget Income Statement Statement of Equity Balance Sheet Cash Accounts Receivable Inventory Buildings & Equipment Total Assets Projected Balance Sheet December 31, 2020 $50,000 Accounts Payable $270,000 Mortgage Payable $154,000 Common Stock $626,000 Retained Earnings $1,100,000 Total Liabilities & Equity $180,000 $300,000 $500,000 $120,000 $1,100,000 January 2021 February-21 March 2021 First Quarter Sales Cash Sales Credit Sales Total Sales December 2020 $100,000 $400,000 $500,000 Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Bu January 2021 February-21 March 2021 First Quarter Sales Cash Sales Cash collections from credit sales Dec Cash collections from credit sales Jan Cash collections from credit sales Feb Cash collections from credit sales Mar Total Cash Receipts Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursen A B D F E March 2021 December 2020 January 2021 February-21 First Quarter Sales Budgeted Cost of Goods Sold Plus: Desired Ending Inventory S Less: Expected beginning Inventory $ = Purchases Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Budget B D E F G January 2021 February-21 March 2021 First Quarter Sales Cash Payments from e Inventory Purchases - Deo Cash Payments from B Inventory Purchases - Jan Cash Payments from - Inventory Purchases - Feb Cash Payments from 5 Inventory Purchases - Mar U Other Cash Expenses: Salaries e Advertising 0 Sales Commissions 1 2 3 4 5 Total Cash Disbursements 6 8 9 0 1 2 3 4 5 6 Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Budget Cash Budget A B D E F January 2021 February-21 March 2021 First Quarter Sales 1 2 Beginning Cash Balance 3 Add: Cash Receipts 4 Less: Cash Disbursements 5 Plus or Minus Other Items: 6 7 9 10 11 12 13 14 15 16 17 18 Ending Cash Balance 19 20 21 22 23 24 25 26 27 28 29 30 Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Budget Cash Budget A D E F G H 1 - L K M N O P B Lunar Artistry Company Projected Income Statement March 31, 2021 1 2 3 4 5 Revenue 6 7 Expenses 8 9 10 11 12 13 10 14 15 16 17 18 19 Net Income 20 21 Net Income 22 23 24 25 26 27 28 29 30 Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Budget Cash Budget Income Statement D E F H 1 L N o P Q Lunar Artistry Company Projected Statement of Equity March 31, 2021 Beginning Balance of Retained Earnings Add: Net Income Less: Dividend Ending Balance of Retained Earnings Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Budget Cash Budget Income Statement Statement of Equity Lunar Artistry Company Projected Balance Sheet March 31, 2021 Accounts Payable Mortgage Payable Cash Accounts Receivable nventory Buildings & Equipment Common Stock Retained Earnings Total Assets Total Liabilities& Equity Projected BS Sales Budget Cash Receipt Budget Purchase Budget Cash Disbursement Budget Cash Budget Income Statement Statement of Equity Balance Sheet
