Budget Preparation and Variance Analysis

profileterrel02
ExcelFile1.xlsx

Instructions

2018 Actual Data
2018
Actual
Average animal fee 38.75
Annual unit sales 2,640
Variable cost per animal
Rewards 0.05
Feed 3.13
Veterinary Fees 3.52
Labor 1.45
Supplies 1.55
Contractors 0.65
Fixed Costs
Lease 250
Depreciation 770
Interest & Penalties 235
Insurance 4,885
Rent 4,690
Advertisement 5,025
Repairs & Maintenance 3,460
Entertainment 4,075
SG&A 2,150
Utilities 3,250
Taxes 6,660
Instructions: Use Excel formulas in all applicable cells.
Actual Results
1. Compute results using the prior year actual accounting data listed above. Enter the information in the Budget and Variance Analysis tab, column F.
2. Use the skills you learned from the week five project. Compute the break even point in units and dollars. Compute the margin of safety. Enter the information in the Budget and Variance Analysis tab, rows 44-50.
Flexible Budget
1a. Prepare a flexible budget using estimated annual unit sales = 2,900. Enter volume in the Budget and Variance Analysis tab, column H, row 4.
Enter all other data and calculations in the appropriate cells (column H).
1b. Increase the average animal fee by 3.25%.
1c. Increase the variable cost per unit (animal) by 2.75%. This applies to all variable cost categories (excluding advertising, bedding, and specialty food).
1d. The driver for bedding and specialty food is the number of non-traditional animals. The company expect 250 animals per year at an average cost of $1.15 per animal for bedding and $1.32 per animal for specialty food.
1e. The company plans to relocate the business. This may decrease rent by $700.
1f. The company uses a dated advertising program including the yellow pages and billboard signs. The company plans to reduce costs and increase effectiveness by investing in an online campaign.
The cost structure changes to a mixed cost and includes $800 fixed plus variable costs. The variable cost is equal to .01 per online view plus $2.75 for appointments scheduled online.
The company expects 1,400 views and 225 scheduled appointments.
2. Use the skills you learned from the week five project. Compute the break even point in units and dollars. Compute the margin of safety. Enter the information in the Budget and Variance Analysis tab, rows 44-50.
3. Use formulas to compute variances and explain why the variances are positive or negative. Enter formulas in the Budget and Variance Analysis tab column J. Write your explanations in column L.

Budget and Variance Analysis

2019 Annual Budget and Variance Analysis
Annual Sales Volume (units) Enter sales volume in colums F and H, row 4.
Flexible
Actual Budget Variance Variance Explanation
Sales
Less: Variable Expenses
Rewards
Feed
Veterinary Fees
Labor
Supplies
Contractors
Advertisement
Bedding
Specialty Food
Total Variable Expenses
Contribution Margin
Less: Fixed Expense
Lease
Depreciation
Interest & Penalties
Insurance
Rent
Advertisement
Repairs & Maintenance
Entertainment
SG&A
Utilities
Taxes
Total Fixed Expense
Net Income (Loss)
Break Even (Units)
Break Even (Dollars)
Margin of Safety (Dollars)