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 Mar15,000 units/month

April May20,000 units/month

June August 47,000 units/month

Sept Oct25,000 units/month

Nov Dec15,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 peoples 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 monthUtilities 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

Need the Excel Sheet to Include numbers as well as a version that shows the formulas.