The Scented Flame with their annual budgeting process. You need to create a master budget for the company’s 2022, first quarter’s operations (January, February, March). The Scented Flame sells decorative jar candles online.

1. The following is information from the November and December budgets:

	November	December
Credit Sales	$18,900	$12,600
Supplies	$540	$360
Payroll	$2,500	$2,500
Misc	$480	330
Inventory Purchases	$16,000	$10,000
Ending Inv	75 Candles	80 Candles

• The company has provided you three possible sales forecast for January. (Note these are three separate forecast, do not add them together)

Sales forecast for January (units)

Optimistic 1,200

Realistic 800

Pessimistic 650

Expectations are for sales to decrease 5% in February , but increase 8% in March(compared to February sales). Round to nearest ten units for sales units. The candles sell for $30 each.

• Typically, 30% of sales are made in cash and 70% are paid in credit. The Scented Flame expects to collect credit sales as follows: 10% in the month of the sale, 65% in the month following the sale, and 25% two months following the sale. Assume there will not be any bad debt.
• The Scented Flame would like to maintain an ending inventory balance of 10% of next month’s sales. Each candle cost $18.00 to buy wholesale. They purchase all inventory on account. They pay 70% of their purchases in the month of purchase and 30% the month after purchase. April 2022 sales are budgeted to be 900 units.
• They will have the following expenses
1. Insurance - $150 per month
2. Internet and Software fees - $1,600 per month
3. Supplies - 2% of total monthly sales
4. Advertising - $500 per month
5. Payroll expense - $2,500 per month
6. Miscellaneous expenses - 1.5% of total monthly sales

• The Scented Candle has decided that they need a new computer system and expects to buy it the beginning of January. The equipment will cost $8,000. The computer system has a useful life of 5 years and salvage value of $200. They will begin depreciating the computer system in January. (Straight line depreciation).

• They pay insurance, internet and software fees, and advertising in the month incurred; they pay supplies, payroll, and miscellaneous expense in the month after incurred.

• At the end of December, The Scented Candle expects to have debt of $10,000 outstanding. Currently the payment schedule is $200 a month in principle and $50 a month in interest.

• Ending cash balance for December is budgeted to be $25,000.
• December’s Retained earnings balance is $26,315

Required:

1.         Open’ BudgetProjectTemplate’ from the assignment tool on Canvas

2.         Input the information given above into the data worksheet

3.         Link the appropriate information from the input sheet to the appropriate cells in the various operating and financial budgets

• Sales budget
• Cash Receipts Budget
• Inventory Purchases Budget
• Cash Payments for Inventory Budget
• Selling and Administrative Expense Budget
• Cash Payments for Selling and Administrative Expense Budget
• Cash Budget
• Pro Forma Income Statement

Use the vlookup function to alternate between the three sales forecast.

1.            Formulas for the balance sheet have already been entered for you. If all of your formulas are correct, the balance sheet should balance.

2.            Save the file using your last name, first and middle initials, and the project name, for example murphyab-budget.xls. Turn this file in through Blackboard

Answer rating: 100% (QA)

To create the master budget for The Scented Flames first quarter operations in 2022 you need to follow these steps Open the Template Open the BudgetProjectTemplate Excel file from the assignment tool ... View the full answer