Question

In: Accounting

Admire is a retail company that sells specialized gardening products. The company is considering opening a...

Admire is a retail company that sells specialized gardening products. The company is considering opening a new store on October 1, Year1. As budget coordinator, you have been asked to prepare a master budget for the first 3 months of the company’s operation. You have gathered the following information:

October sales are estimated to be $300000 of which 45 percent will be cash and the remainder will be on credit. The company expects all sales to increase at the rate of 20 percent per month for November and December. Sales in January Year 2 are expected to be $250000.

The company expects to collect 100 percent of the accounts receivable generated by credit sales in the month following the sale.

Prepare a sales budget and a schedule of cash receipts using these facts and your excel template. Check your answers here before moving to the next part, by completing the cells requested in the chart below.

a. Sales Budget October November December Total-Qtr
Cash sales
Sales on account   
Total budgeted sales
b. Schedule of Cash Receipts October November December Total-Qtr
Current cash sales
Plus collections from A/R    
Total collections        

The cost of goods sold is 60 percent of sales. The company desires to maintain a minimum ending inventory equal to 10 percent of the next month’s cost of goods sold. (Ending inventory for December is based on budgeted January Year2 sales.)

Assume that all inventory purchases are made on account (on credit). The company pays 80 percent of accounts payable in the month of purchase and the remaining amount in the following month.

In excel, prepare an inventory purchases budget and a cash payments budget for inventory purchases. Use the check figures below before you continue.

c. Inventory Purchases Budget October November December Total-Qtr
Budgeted cost of goods sold
Plus desired ending inventory
Inventory needed
Less beginning inventory
Required purchases (on account)
d. Cash payments for inventory October November December Total-Qtr
Payment of current month's A/P    
Payment for prior month's A/P        
Total budgeted payments    

Budgeted selling and administrative expenses per month follow.

  • Salary expense (fixed): $ 28200
  • Sales commissions:  5 percent of Sales
  • Supplies expense:   2 percent of Sales
  • Utilities (fixed): $2600              
  • Depreciation on store equipment (fixed)*:   You compute    
  • Rent (fixed) $ 11000          
  • Miscellaneous (fixed): $ 3500      

*The capital expenditures budget indicates that the company will spend $182400 on October 1 for store fixtures, which are expected to have a $24000 residual value and a 36 month useful life.

Utilities and sales commissions are paid the month after they are incurred; all other expenses are paid in the month in which they are incurred.

In excel, prepare the selling and administrative expenses budget and the cash payments budget for selling and administrative expenses. Check the key figures below.

e. Selling and Admin.Expense Budget October November December Total-Qtr
Salary expense
Sales commissions    
Supplies expense
Utilities    
Depreciation on store fixtures    
Rent
Miscellaneous
Total S&A expenses    
f. Cash payments for S&A October November December Total-Qtr
Salary expense
Sales commissions    
Supplies expense
Utilities    
Depreciation on store fixtures   
Rent
Miscellaneous
Total payments for S&A expenses    

Admire issued common stock for $50000 on October 5.

A dividend of $28000 was paid on December 15.

The company borrows and repays funds in increments of $1,000 on the last day of the month. The company also pays its vendors on the last day of the month. It pays interest of 1 percent per month in cash on the last day of the month. To be prudent, the company desires to maintain a $16000 cash cushion.   

Prepare a cash budget on your excel template. Check key figure below.

g. Cash Budget October November December Total-Qtr
Beginning cash balance        
Issuance of stock
Collections from customers    
Cash available    
Less payments
   For inventory purchases
   For S&A expenses
   Purchase of store fixtures
    Pay dividend
   Interest expense   
Total budgeted payments    
Cash balance before borrow/repay
Financing activity
   Borrowing (repayment)   
Ending cash balance    

Income statement

Input expenses as negatives. Use a minus sign in front of the number.

Sales revenue
Cost of goods sold
Gross margin
S&A expenses
Operating income
Interest expense
Net income

Balance Sheet

Enter any contra-assets as negative numbers. Use a minus sign.

Assets  
   Cash    
   Accounts receivable    
   Inventory    
   Store fixtures    
   Accumulated depreciation    
Total assets    
Liabilities  
   Accounts payable    
   Utilities payable    
   Sales commissions payable    
   Line of credit liability    
    Total liabilities    
Equity  
   Common stock    
   Retained earnings    
    Total equity    
Total liabilities and equity    

Solutions

Expert Solution

Please give positive ratings so I can keep answering. Thanks!

Due to character limit I am attaching the image for Income Statement and Balance Sheet.

Sales Budget October November December Total January Note
Cash Sales      135,000.00 162,000.00 194,400.00       491,400.00 250,000.00 300000*45% for October, then 20% increase every month.
Sales on Account      165,000.00 198,000.00 237,600.00       600,600.00 300000*55% for October, then 20% increase every month.
Total budgeted sales      300,000.00 360,000.00 432,000.00 1,092,000.00
Schedule of Cash Receipts October November December Total
Current cash sales      135,000.00 162,000.00 194,400.00       491,400.00 Cash sales collected in the month of sales.
Plus collections from A/R                       -   165,000.00 198,000.00       363,000.00 The accounts receivable generated by credit sales is collected in the month following the sale.
Total collections      135,000.00 327,000.00 392,400.00       854,400.00
Inventory Purchases Budget October November December Total January
Budgeted cost of goods sold      180,000.00 216,000.00 259,200.00       655,200.00 150,000.00 60% of budgeted sales.
Plus desired ending inventory        21,600.00      25,920.00      15,000.00 10 percent of the next month’s cost of goods sold
Inventory needed      201,600.00 241,920.00 274,200.00       717,720.00
Less beginning inventory                       -        21,600.00      25,920.00
Required purchases (on account)      201,600.00 220,320.00 248,280.00       670,200.00
Cash payments for inventory October November December Total
Payment of current month's A/P      161,280.00 176,256.00 198,624.00       536,160.00 80 percent of accounts payable in the month of purchase.
Payment for prior month's A/P                       -        40,320.00      44,064.00         84,384.00 20 percent of accounts payable after 1 month of purchase.
Total budgeted payments      161,280.00 216,576.00 242,688.00       620,544.00
Selling and Admin.Expense Budget October November December Total Depreciation on store fixtures
Salary Expense        28,200.00      28,200.00      28,200.00         84,600.00 Cost 182,400.00 A
Sales commission at 5% of sales        15,000.00      18,000.00      21,600.00         54,600.00 Less: Salvage     24,000.00 B
Supplies expense at 2% of sales          6,000.00        7,200.00        8,640.00         21,840.00 Depreciable Value 158,400.00 C=A-B
Utilities          2,600.00        2,600.00        2,600.00           7,800.00 Life (months)             36.00 D
Depreciation on store fixtures          4,400.00        4,400.00        4,400.00         13,200.00 Depreciation       4,400.00 E=C/D
Rent        11,000.00      11,000.00      11,000.00         33,000.00
Miscellaneous          3,500.00        3,500.00        3,500.00         10,500.00
Total S&A expenses        70,700.00     74,900.00     79,940.00       225,540.00
Cash payments for S&A October November December Total
Salary Expense        28,200.00      28,200.00      28,200.00         84,600.00 Paid in the month incurred.
Sales commission at 5% of sales                       -        15,000.00      18,000.00         33,000.00 Paid after 1 month it is incurred.
Supplies expense at 2% of sales          6,000.00        7,200.00        8,640.00         21,840.00 Paid in the month incurred.
Utilities                       -          2,600.00        2,600.00           5,200.00 Paid after 1 month it is incurred.
Depreciation on store fixtures                       -                      -                      -                          -   Depreciation is a non cash item.
Rent        11,000.00      11,000.00      11,000.00         33,000.00 Paid in the month incurred.
Miscellaneous          3,500.00        3,500.00        3,500.00         10,500.00 Paid in the month incurred.
Total payments for S&A expenses        48,700.00     67,500.00     71,940.00       188,140.00
Cash budget October November December Total
Opening Cash Balance                       -        16,620.00      16,304.00
Issuance of stock        50,000.00                    -                      -           50,000.00
Collections from customers      135,000.00 327,000.00 392,400.00       854,400.00
Cash available      185,000.00 343,620.00 408,704.00       937,324.00
Less payments
For inventory purchases      161,280.00 216,576.00 242,688.00       620,544.00
For S&A expenses        48,700.00      67,500.00      71,940.00       188,140.00
Purchase of store fixtures      182,400.00                    -                      -         182,400.00
Pay dividend                       -                      -        28,000.00         28,000.00
Interest expense                       -          2,240.00        1,830.00           4,070.00 Loan Value* 1%
Total budgeted payments      392,380.00 286,316.00 344,458.00 1,023,154.00
Cash balance before borrow/repay    (207,380.00)      57,304.00      64,246.00
Financing activity
Borrowing (repayment)      224,000.00    (41,000.00)    (48,000.00)
Ending cash balance        16,620.00     16,304.00     16,246.00



Related Solutions

Admire is a retail company that sells specialized gardening products. The company is considering opening a...
Admire is a retail company that sells specialized gardening products. The company is considering opening a new store on October 1, Year1. As budget coordinator, you have been asked to prepare a master budget for the first 3 months of the company’s operation. You have gathered the following information: October sales are estimated to be $300000 of which 45 percent will be cash and the remainder will be on credit. The company expects all sales to increase at the rate...
abulous is a retail company that sells specialized gardening products. The company is considering opening a...
abulous is a retail company that sells specialized gardening products. The company is considering opening a new store on October 1, Year1. As budget coordinator, you have been asked to prepare a master budget for the first 3 months of the company’s operation. You have gathered the following information: October sales are estimated to be $500000 of which 45 percent will be cash and the remainder will be on credit. The company expects all sales to increase at the rate...
Choice is a retail company that sells specialized gardening products. The company is considering opening a...
Choice is a retail company that sells specialized gardening products. The company is considering opening a new store on October 1, Year1. As budget coordinator, you have been asked to prepare a master budget for the first 3 months of the company’s operation. You have gathered the following information: October sales are estimated to be $280000 of which 60 percent will be cash and the remainder will be on credit. The company expects all sales to increase at the rate...
Choice is a retail company that sells specialized gardening products. The company is considering opening a...
Choice is a retail company that sells specialized gardening products. The company is considering opening a new store on October 1, Year1. As budget coordinator, you have been asked to prepare a master budget for the first 3 months of the company’s operation. You have gathered the following information: October sales are estimated to be $280000 of which 60 percent will be cash and the remainder will be on credit. The company expects all sales to increase at the rate...
You are considering opening a retail establishment, ABC Company and need to make a number of...
You are considering opening a retail establishment, ABC Company and need to make a number of decisions regarding this new company and prepare a number of documents. Determine the type of organization you would like for your company (sole proprietor, partnership, corporation, etc.) and explain the advantages and disadvantages of your selection.
Easecom Company is a manufacturer of highly specialized products for networking video-conferencing equipment. Production of specialized...
Easecom Company is a manufacturer of highly specialized products for networking video-conferencing equipment. Production of specialized units is, to a large extent, performed under contract, with standard units manufactured to marketing projections. Maintenance of customer equipment is an important area of customer satisfaction. With the recent downturn in the computer industry, the video-conferencing equipment segment has suffered, causing a slide in Easecom’s performance. Easecom’s income statement for the fiscal year ended October 31, Year 1, is presented below. Easecom Company...
A retail company sells electronics products including mouse and keyboard. Mouse and keyboard are acquired from...
A retail company sells electronics products including mouse and keyboard. Mouse and keyboard are acquired from distinct suppliers. The monthly demand for a mouse is 2000, while the monthly demand for a keyboard is 1000 products. Mouse costs to company $12 and keyboard's cost are $18. The company has an annual holding cost of 20%, and the fixed shipment cost is $300. The procurement manager is not sure whether to order the mouse and keyboard separately or jointly. Assist with...
Exceptional Bouquets​ (EB) makes and sells flower bouquets. EB is considering opening a new store in...
Exceptional Bouquets​ (EB) makes and sells flower bouquets. EB is considering opening a new store in the local mall. The mall has several empty shops and EB is unsure of the demand for its product. The mall has offered EB two alternative rental agreements. The first is a standard​ fixed-rent agreement where EB will pay the mall $5,700 per month. The second is a royalty agreement where the mall receives $12 for each bouquet sold. EB estimates that a bouquet...
Raven Company sells a single specialized product. The company’s sales andexpenses for a recent month are...
Raven Company sells a single specialized product. The company’s sales andexpenses for a recent month are given below: TOTAL Sales $300,000 PER UNIT $60 45 $15 Less Variable Expenses Contribution Margin Less fixed expenses Net Operating Income Required: 180,000 120,000 70,000 $ 50,000 a) What is the monthly break-even point in units sold and in sales dollars? b) Without resorting to computations, what is the total contribution margin at the break-even point? c) How many units would have to be...
Rooney Manufacturing Co. produces and sells specialized equipment used in the petroleum industry. The company is...
Rooney Manufacturing Co. produces and sells specialized equipment used in the petroleum industry. The company is organized into three separate operating branches: Division A, which manufactures and sells heavy equipment; Division B, which manufactures and sells hand tools; and Division C, which makes and sells electric motors. Each division is housed in a separate manufacturing facility. Company headquarters is located in a separate building. In recent years, Division B has been operating at a net loss and is expected to...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT