Please answer the case study with ALL formulas. You do not have to answer #6
2) Helping Hand's cost of goods sold generally runs at 65% of sales. Inventory is purchased on account and 15% of each month's purchases are paid during the month of purchase. The remainder is paid during the following month. In order to have adequate stocks of inventory on hand, the company attempts to have inventory on hand at the end of each month equal to 30% of the next month's projected cost of goods sold. The controller has estimated that Helping Hand's other monthly expenses will be as follows: 3) $35,000 5,000 12,000 7,500 2,000 1,000 Sales salaries Advertising and promotion Administrative salaries Interest on bonds Property taxes In addition, sales commissions run at the rate of 2.0 percent of sales. Sales commissions are paid in the month following the month of sale. 4) The company president has indicated that the company should invest $225,000 in an automated inventory-handling system to control the movement of inventory in the company's warehouse just after the new year begins. The president would like to purchase the equipment primarily from the company's cash and marketable securities. However, the president believes the company should have a minimum cash balance of $30,000 at the end of each month. If necessary, the remainder of the equipment purchase may be financed using short-term credit from a local bank. The minimum lending period for such a loan is three months. The current short-term interest rates are 6 percent per year and are expected to remain at this rate through the time the equipment is purchased. If a loan is necessary, the president has decided it should be paid off at the end of the first quarter if possible. Any partial payment will be paid at the end of the first quarter and in a $1,000 increment. 5) Helping Hand's board of directors has indicated an intention to declare and pay dividends of 150,000 on the last day of each quarter 6) The interest on any short-term borrowing will be paid when the loan is repaid. Interest on Helping Hand's bonds is paid semiannually on February 28 and August 31 for the preceding six- month period. 7) Property taxes are paid quarterly on March 31, June 30, September 30, and December 31 for the preceding three-month period. Required: Build a model to forecast Helping Hand Corp's cash balance at March 31, 2016. Your model must contain the following master budget schedules. Round all amounts to the nearest dollar. Your model should allow you to change any of the assumptions provided above and easily recalculate the ending cash balance at March 31, 2016. The assumptions may be on a separate worksheet but all of the schedules below must be on one worksheet. 1) Sales budget: 2015 2016 1st November December aa Februaac Quarter Total sales Cash sales Sales on account 2) Cash receipts budget: 2016 lanuary Febry March 1st Quarter Cash sales Cash collections from credit sales made during current month Cash collections from credit sales made during preceding month Cash collections from credit sales made during 2nd preceding month Total cash receipts 3) Purchases budget: 2015 December 2016 Jaary Februar March 1st Quarter Budgeted cost of goods sold Add: Desired ending inventory Total goods needed Less: Expected beginning inventory Purchases 4) Cash disbursements budget: 2016 lanuary February March stQuarter Inventory purchases: current month preceding month purchases Cash payments for purchases during the Cash payments for purchases during the Total cash payments for inventory Other expenses: Sales salaries Advertising and promotion Administrative salaries Interest on bonds Property taxes Sales commissions Total cash payments for other expenses Total cash disbursements 5) Summary cash budget: 2016 lanuary Februanarch 1st Quarter Cash receipts (sch 2) Less: Cash disbursements (sch 4) Change in cash balance during period due Sale of marketable securities (1/2/16) Proceeds from bank loan (1/2/16) Purchase of equipment Repayment of bank loan (3/31/16) Interest on bank loan Payment of dividends Change in cash balance during the month Beginning cash balance Ending cash balance 6) Prepare a memo to the president of Helping Hands Corp with at least two recommendations on how the company can ensure it completes the first quarter of 2016 with the minimum required cash balance. You should provide a plan to support your recommendation. For example, if you recommend an increase in sales, how can this be attained. Be specific. You should provide specific financial information for your recommendations utilizing your model (include a model for each of your recommendations). For example, if the company does X, the change in ending cash will be Y. Your model will become the property of Helping Hands Corp. and should be easy to use
