| 2018 Actual Data |
| | | | | 2018 |
| | | | | Actual |
| Average animal fee | | | | 39.1 |
| Annual unit sales | | | | 2,775 |
| Variable cost per animal |
| Feed | | | | 3.23 |
| Veterinary Fees | | | | 3.72 |
| Labor | | | | 1.65 |
| Supplies | | | | 1.45 |
| Contractors | | | | 0.75 |
| |
| Fixed Costs |
| Depreciation | | | | 880 |
| Interest & Penalties | | | | 265 |
| Insurance | | | | 4,775 |
| Rent | | | | 5,690 |
| Advertisement | | | | 4,665 |
| Repairs & Maintenance | | | | 3,325 |
| Entertainment | | | | 3,790 |
| SG&A | | | | 1,895 |
| Utilities | | | | 3,500 |
| Taxes | | | | 6,430 |
| Instructions: Use Excel formulas in all applicable cells. |
| Fixed Budget |
| 1. Prepare a fixed budget using 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 learning team 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 42-48. |
| Flexible Budget |
| 1a. Prepare a flexible budget using estimated annual unit sales = 3,000. Enter your volume choice 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 variable cost per unit (animal) by 3%. This applies to all variable cost categories (excluding advertising, bedding, and specialty food). |
| 1c. The driver for bedding and specialty food is the number of non-traditional animals. The company expect 300 animals per year at an average cost of $.90 per animal for bedding and $1.63 per animal for specialty food. |
| 1d. The company plans to relocate the business. This may increase rent by $800. |
| 1e. 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 $900 fixed plus variable costs. The variable cost is equal to .014 per online view plus $3.23 for appointments scheduled online. |
| The company expects 1,600 views and 275 scheduled appointments. |
| 2. Use the skills you learned from the week five learning team project. Compute the break even point in units and dollars. Compute the margin of safety. Enter the information in the flexible budget tab, rows 42-48. |
| 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. |