2/28/20, 4:28 PM ched: Excel File Budget Project.xlsx eneral Instructions: Download the Excel file provided. Your responsibility is to do the following. 1. Use 2019 actual data to complete the 2019 Actual column incorporated with the budget and variance analysis te 2. Complete the 2020 Flexible Budget column incorporated with the budget and variance analysis tab. The flexible includes several changes to the data. The changes are listed in the Excel file / Instructions Tab. 3. Compute variances. 4. Explain the variances. 5. Compute the break even in units and dollars. 6. Compute the margin of safety. The format used is a variable costing income statement. Excel: In order to minimize errors, improve accuracy, and increase efficiency use formulas in all cells. If you need assistance with Excel review the week one questions thread. Included therein are several options to help you advance your Excel skills such as, in part, formula overview, basic math sklls, and Excel essential training (week one learning activities). Video: In order to gain experience and insight please review the following video. It is a simple presentation and will help everyone develop a basic understanding of flexible budgets. The video was created by a third party. There are additional videos included with the Bing search as well. Flexible Budget Example Reply A e AutoSave OH File Home - 3 Draw Insert Page Layout Formulas Data Review View i PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit, it's safe 34 C D E 2019 Actual Data 2019 Actual 38.75 Average animal fee Annual unit sales 2,640 0 Variable cost per animal Rewards Feed Veterinary Fees Labor Supplies Contractors 2 0.05 3.13 3.52 1.45 1.55 0.65 4 16 Fixed Costs 17 20 18 Lease 19 Depreciation Interest & Penalties 21 Insurance 22 Rent 23 Advertisement 24 Repairs & Maintenance 25 Entertainment 26 SG&A 27 Utilities 28 Taxes 250 770 235 4,885 4,690 5,025 3,460 4,075 2,150 3,250 6,660 S T U V W 4 ABCD 26 SG&A 27 Utilities 28 Taxes 2,150 3,250 6,660 31 Instructions: Use Excel formulas in all applicable cells. 32 2019 Actual 33 1. Compute results using the prior year actual accounting data listed above. Enter the information in the Budget and Variance Analysis tab, column F. 34 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 46-52. 37 2020 Flexible Budget 38 la. Prepare a flexible budget using estimated annual unit sales = 2,900. Enter volume in the Budget and Variance Analysis tab, column H, row 4. 39 Enter all other data and calculations in the appropriate cells (column H). 40 lb. Increase the average animal fee by 3.25%. 41 10. Increase the variable cost per unit (animal) by 2.75%. This applies to all variable cost categories (excluding advertising, bedding, and specialty food). 42 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. 43 le. The company plans to relocate the business. This may decrease rent by $700. 44 f. 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. 45 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. 46 The company expects 1,400 views and 225 scheduled appointments. 47 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 46-52. 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 H 1 2020 Annual Budget and Variance Analysis 4 Annual Sales Volume (units) Enter sales volume in columns Fand H, row 4. LOVA 2019 Actual 2020 Flexible Budget Variance Variance Explanation 10 Sales 12 Less: Variable Expenses 13 Rewards 14 Feed 15 Veterinary Fees 16 Labor 17 Supplies Contractors 19 Advertisement 20 Bedding 21 Specialty Food 18 23 Total Variable Expenses 25 Contribution Margin 27 Less: Fixed Expense 28 29 Lease 30 Depreciation 31 Interest & Penalties 32 Insurance 33 Rent 34 Advertisement 35 Repairs & Maintenance 36 Entertainment 37 SG&A 38 Utilities 39 Taxes 10 41 Total Fixed Expense Instructions Budget and Variance Analysis File Home insert Draw Page Layout Formulas Data Review View Help Search PROTECTED VIEW Be careful files from the internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing A9 4 F G H I J A B C D E 1 2020 Annual Budget and Variance Analysis 4 Annual Sales Volume (units) Enter sales volume in colums F and H, row 4. 2019 Actual 2020 Flexible Budget Variance Variance Explanation 41 Total Fixed Expense 43 Net Income (Loss) 44 46 Break Even (Units) 49 Break Even (Dollars) 52 Margin of Safety (Dollars) 2/28/20, 4:28 PM ched: Excel File Budget Project.xlsx eneral Instructions: Download the Excel file provided. Your responsibility is to do the following. 1. Use 2019 actual data to complete the 2019 Actual column incorporated with the budget and variance analysis te 2. Complete the 2020 Flexible Budget column incorporated with the budget and variance analysis tab. The flexible includes several changes to the data. The changes are listed in the Excel file / Instructions Tab. 3. Compute variances. 4. Explain the variances. 5. Compute the break even in units and dollars. 6. Compute the margin of safety. The format used is a variable costing income statement. Excel: In order to minimize errors, improve accuracy, and increase efficiency use formulas in all cells. If you need assistance with Excel review the week one questions thread. Included therein are several options to help you advance your Excel skills such as, in part, formula overview, basic math sklls, and Excel essential training (week one learning activities). Video: In order to gain experience and insight please review the following video. It is a simple presentation and will help everyone develop a basic understanding of flexible budgets. The video was created by a third party. There are additional videos included with the Bing search as well. Flexible Budget Example Reply A e AutoSave OH File Home - 3 Draw Insert Page Layout Formulas Data Review View i PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit, it's safe 34 C D E 2019 Actual Data 2019 Actual 38.75 Average animal fee Annual unit sales 2,640 0 Variable cost per animal Rewards Feed Veterinary Fees Labor Supplies Contractors 2 0.05 3.13 3.52 1.45 1.55 0.65 4 16 Fixed Costs 17 20 18 Lease 19 Depreciation Interest & Penalties 21 Insurance 22 Rent 23 Advertisement 24 Repairs & Maintenance 25 Entertainment 26 SG&A 27 Utilities 28 Taxes 250 770 235 4,885 4,690 5,025 3,460 4,075 2,150 3,250 6,660 S T U V W 4 ABCD 26 SG&A 27 Utilities 28 Taxes 2,150 3,250 6,660 31 Instructions: Use Excel formulas in all applicable cells. 32 2019 Actual 33 1. Compute results using the prior year actual accounting data listed above. Enter the information in the Budget and Variance Analysis tab, column F. 34 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 46-52. 37 2020 Flexible Budget 38 la. Prepare a flexible budget using estimated annual unit sales = 2,900. Enter volume in the Budget and Variance Analysis tab, column H, row 4. 39 Enter all other data and calculations in the appropriate cells (column H). 40 lb. Increase the average animal fee by 3.25%. 41 10. Increase the variable cost per unit (animal) by 2.75%. This applies to all variable cost categories (excluding advertising, bedding, and specialty food). 42 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. 43 le. The company plans to relocate the business. This may decrease rent by $700. 44 f. 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. 45 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. 46 The company expects 1,400 views and 225 scheduled appointments. 47 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 46-52. 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 H 1 2020 Annual Budget and Variance Analysis 4 Annual Sales Volume (units) Enter sales volume in columns Fand H, row 4. LOVA 2019 Actual 2020 Flexible Budget Variance Variance Explanation 10 Sales 12 Less: Variable Expenses 13 Rewards 14 Feed 15 Veterinary Fees 16 Labor 17 Supplies Contractors 19 Advertisement 20 Bedding 21 Specialty Food 18 23 Total Variable Expenses 25 Contribution Margin 27 Less: Fixed Expense 28 29 Lease 30 Depreciation 31 Interest & Penalties 32 Insurance 33 Rent 34 Advertisement 35 Repairs & Maintenance 36 Entertainment 37 SG&A 38 Utilities 39 Taxes 10 41 Total Fixed Expense Instructions Budget and Variance Analysis File Home insert Draw Page Layout Formulas Data Review View Help Search PROTECTED VIEW Be careful files from the internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing A9 4 F G H I J A B C D E 1 2020 Annual Budget and Variance Analysis 4 Annual Sales Volume (units) Enter sales volume in colums F and H, row 4. 2019 Actual 2020 Flexible Budget Variance Variance Explanation 41 Total Fixed Expense 43 Net Income (Loss) 44 46 Break Even (Units) 49 Break Even (Dollars) 52 Margin of Safety (Dollars)