PREPARATION OF A BUDGETED INCOME STATEMENT
Cold Mountain Ice Cream Shoppe is an ice cream shop company that sells a franchised brand of single serve ice cream.
( Need the excel formulas for each answer )
( Let me know if I need to send the ecxel sheet )
SECTION 1 PREPARATION OF A BUDGETED INCOME STATEMENT Cold Mountain Ice Cream Shoppe is an ice cream shop company that sells a franchised brand of single serve ice cream. Sales Budget: The sales department of Cold Mountain Ice Cream Shoppe has determined that sales for 2020 will be 8% higher than sales in 2019. Actual 2019 sales and 2019 Q4 projections are as follows: -Jan - Mar 15,000 units/month April-May 20,000 units/month June - August 47,000 units/month . Sept - Oct 25,000 units/month - Nov - Dec 15,000 units/month The average selling price for 2019 was $5.00/unit. The company expects to increase the selling price in 2020 by 5%. Cost of Goods Sold: Each unit of ice cream costs 60% of the retail price. Selling and Administrative Expenses: Cold Mountain has to pay a franchise fee equal to 4% of sales revenue Salaries are $6,000/month During peak season (June - August) Cold Mountain hires additional sales people. The additional sales people's salaries are $2,500/month During peak season (June - August) Cold Mountain advertises. Advertising expenses are $10,000 per month Rent Expense is fixed at $2,500 per month Utilities are $500 during the off season (Jan - May, Sept - Dec) and $750 per month during peak season (June - August) Supplies are $500/month Depreciation on the store equipment is $250/month Income taxes Cold Mountain pays income taxes at a rate of 30% of income before taxes Required: Using an Excel spreadsheet, prepare a Budgeted Income Statement by month for Cold Mountain Ice Cream Shoppe SECTION 2 PREPARATION OF A MERCHANDISE PURCHASE AND CASH BUDGET A. MERCHANDISE PURCHASE BUDGET Budgeted Ending Inventory +Budgeted Units Sold -Budgeted Beginning Inventory =Inventory (units) to be purchased - Projected beginning inventory on hand on January 1, 2020 is 15,000 units. - Cold Mountain maintains an ending inventory equal to 75% of the next month's budgeted sales. - Cold Mountain estimates January 2021 sales to be the same as December 2020 Sales Required: Using and Excel spreadsheet prepare a Merchandise Purchase Budget by month for Cold Mountain Ice Cream Shoppe B. CASH BUDGET Beginning Cash Balance +Budgeted Cash Receipts -Budgeted Cash Disbursements =Budgeted Ending Cash Balance Projected beginning cash balance on January 1, 2020 is B. CASH BUDGET Beginning Cash Balance +Budgeted Cash Receipts -Budgeted Cash Disbursements =Budgeted Ending Cash Balance Projected beginning cash balance on January 1, 2020 is $180,000 Assume 80% of sales are paid for in cash and 20% are paid with Cold Mountain credit cards. Credit cards are paid the following month. Projected beginning accounts receivable balance on January 2020 is $20,000. . Cold Mountain purchases its inventory entirely on account. Full payment is made the month following the purchase. Projected accounts payable balance as of January 1, 2020 is $45,000. Franchise fees are paid the month following the sales (for example franchise fees for January are paid in February) Franchise fee expense for December 2019 was $3,750. Salaries, Rent, Utilities and Supplies expense are paid for during the month in which they are incurred. Advertising expense is paid for in advance the month prior to the advertising campaign. Taxes are paid the following year in April) Prior year taxes were $120,000. Required: Using and Excel spreadsheet prepare a cash budget by month for Cold Mountain Ice Cream Shoppe
