Actual Data 2018: Animal Fee Average

Instructions2018 Actual Data2018actualaverage Animal Fee3875annual Un

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 expects 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 0.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 columns 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)

Paper For Above instruction

The task involves performing detailed financial analysis and planning based on the provided actual data for 2018, with a focus on preparing budgets, calculating break-even points, margins of safety, and analyzing variances. This analysis is pivotal for understanding the company's financial health and making informed decisions about future operations, especially considering potential adjustments such as fee increases, cost variability, relocation, and advertising strategies.

Firstly, the analysis begins with calculating the actual results from the previous year's data, which includes revenue from unit sales, various variable costs, and fixed costs. Utilizing Excel formulas, these elements must be computed accurately within the designated "Budget and Variance Analysis" tab, specifically in column F, to establish a baseline of actual performance.

Next, the task requires computing the break-even points both in units and dollar terms. This calculation involves understanding the contribution margin per unit, which is revenue per unit minus variable costs per unit, and then dividing fixed costs by the contribution margin to find the units needed to break even. Multiplying the break-even units by the unit price gives the break-even dollar amount. Additionally, the margin of safety—indicating how much sales can drop before the business reaches its break-even point—is to be calculated as the difference between actual or projected sales and the break-even sales.

The exercise extends to the development of a flexible budget assuming estimated annual unit sales increase to 2,900 units. Adjustments include increasing the average animal fee by 3.25%, variable costs per animal by 2.75%, and accounting for additional variable costs associated with bedding and specialty food for non-traditional animals. Moreover, a relocation plan entails a reduction in rent costs by $700.

An important component involves reassessing the company's advertising expenses. Moving from traditional yellow pages and billboard advertising to a cost-effective online campaign introduces a mixed cost structure comprising a fixed component ($800) and variable costs based on online views and scheduled appointments. The projected online views and appointments are included to determine the total advertising expenses under this new strategy.

The analysis ultimately aims to compute the revised break-even point under the new budget structure, measure the margin of safety, and assess variances—all of which must be performed using formulas. For variance analysis, differences between budgeted and actual figures are calculated, and explanations for the variance direction (positive or negative) are documented.

This comprehensive financial planning and analysis ensures management has a clear understanding of the operational leverage, profitability, and risk points, facilitating strategic decisions, especially considering upcoming relocations and advertising shifts. It is essential that all calculations are correctly formulated in Excel, reflecting dynamic changes, and that explanations in the variance section provide insights into the reasons behind cost overruns or savings.

References

• Garrison, R. H., Noreen, E. W., & Brewer, P. C. (2021). Managerial Accounting (16th ed.). McGraw-Hill Education.
• Horngren, C. T., Sundem, G. L., Stratton, W. O., Burgstahler, D., & Schatzberg, J. (2019). Introduction to Management Accounting (16th ed.). Pearson.
• Hilton, R. W., & Platt, D. (2017). Managerial Accounting: Creating Value in a Dynamic Business Environment (11th ed.). McGraw-Hill Education.
• Drury, C. (2018). Management and Cost Accounting (10th ed.). Cengage Learning.
• Weygandt, J. J., Kimmel, P. D., & Kieso, D. E. (2019). Financial & Managerial Accounting (10th ed.). Wiley.
• Simons, R. (2014). Levers of Control: How Managers Use Innovative Control Systems to Drive Strategic Renewal. Harvard Business Review Press.
• Shank, J. K., & Govindarajan, V. (2019). Strategic Cost Management: The New Tool for Competitive Advantage. McGraw-Hill Education.
• Anthony, R. N., & Govindarajan, V. (2018). Management Control Systems (13th ed.). McGraw-Hill Education.
• Caplan, D. (2017). Accounting for Managers: Interpreting Financial Statements. Routledge.
• Langfield-Smith, K., Thorne, H., & Hilton, R. (2018). Management Accounting: Information for Creating and Managing Value (8th ed.). McGraw-Hill Education.