Hello,
I really need some help here. The instructions are on the bottom page. Please start reading from top to bottom. I have 24 hours to submitted.
AutoSave . OFF 1 Flexible budget project_Student Version_ACCU 325(3) Q Q v Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) 11 A" A " ab Wrap Text v General Insert v x Delete v Paste Merge & Center v $ ~ % 9 68 98 Conditional Format Cell Format X V F Sort & Find & Ideas Sensitivity Formatting as Table Styles Filter Select BT 4 X V fx B C D E F G H I J K L M N 0 P Q R S Flexible Budget Performance Report Project Kelsey's Frozen Confectionaries buys and distributes single-serve ice cream treats to convenience stores, ballparks, and amusement parks. In this project, you will create 1) a master budget performance report, and 2) a flexible budget performance report for Kelsey's Frozen Confectionaries. Your performance reports should be developed in such a way that any changes to the original assumptions will correctly ripple through the entire spreadsheet. After developing the performance reports, you will answer questions about the variances and determine whether the variances are consistent with management's explanation about operational changes that took place during the period. 10 11 12 16 Part 1) DIRECTIONS for Master Budget Performance Report; 18 19 1) Use the budget assumptions, along with Excel formulas, to populate the Master Budget column. Note: Your formulas must work such that if 20 ANY of the budget assumptions change, the new assumptions ripple through the entire budget. Part of your grade will be based on whether you 21 correctly formulated the cells. Do NOT TYPE A NUMBER IN ANY CELL!!! 22 2) Use a formula to calculate the "variance" in cell H7: (Actual - Budget). Copy and paste (or drag) the formula to the rest of the cells in the 23 column. Leave as positive or negative, rather than absolute values. 24 25 3) Use a formula to calculate the "Variance percentage". NOTE: The percentage is the variance as a percent of the Master Budget. Copy and 26 paste (or drag) the formula to the rest of the cells in the column 27 28 4) Format cells appropriately. Attention to detail makes a report look more professional. (For example, percentages shown as %, dollar signs using the accounting or currency format, underlines and double underlines where appropriate, zero decimal places for dollar amounts, etc.,). 29 30 5) Use the "If" statement function to show the variances as U or F. The "If" statement can be found under "Formulas, Logical". Example: 31 =IF(H7>=0,"F","U"). This formula means: If cell H7>0 or H7=0, then mark as "F"; If not greater than or equal to 0, mark as "U". Be careful with 32 revenues and expense variances since they should be opposite of one another. ALSO- The formula you use should mark any variance of "0" as 33 an "F" since a zero variance means that budget expectations have been met. After using the function, check each line to make sure it is going 34 in the direction you believe it should go. 35 6) Check your answers using the following check figures: Budgeted CM= $301,000; Budgeted Op Inc. = $211,000; Variance for Commissions = 36 $8,685; Variance percentage for Commissions = 17.4 %, Unfavorable. 37 38 7)Use management by exception to determine which variances to investigate. HINT: In column K, use the "If function", along with "Absolute value function" to find the variances that are larger than the decision rule. Also, since the decision rule is in B21 and you want Excel to always Directions Performance Report Analysis- page 1 A Analysis- page 2 Grading Rubric + - + 110%AutoSave . OFF 1 Flexible budget project_Student Version_ACCU 325(3) Q Q v Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) 11 " A" A " ab Wrap Text General Insert v x Delete v Paste Merge & Center v $ ~ % 9 68 98 Conditional Format Cell Format X V F Sort & Find & Ideas Sensitivity Formatting as Table Styles Filter Select + X V fx B C D E F G H I J K L M N 0 P Q R S 25 3) Use a formula to calculate the "Variance percentage". NOTE: The percentage is the variance as a percent of the Master Budget. Copy and 26 paste (or drag) the formula to the rest of the cells in the column 27 28 4) Format cells appropriately. Attention to detail makes a report look more professional. (For example, percentages shown as %, dollar signs using the accounting or currency format, underlines and double underlines where appropriate, zero decimal places for dollar amounts, etc.,). 29 30 5) Use the "If" statement function to show the variances as U or F. The "If" statement can be found under "Formulas, Logical". Example: 31 =IF(H7>=0,"F","U"). This formula means: If cell H7>0 or H7=0, then mark as "F"; If not greater than or equal to 0, mark as "U". Be careful with 32 revenues and expense variances since they should be opposite of one another. ALSO- The formula you use should mark any variance of "0" as 33 an "F" since a zero variance means that budget expectations have been met. After using the function, check each line to make sure it is going 34 in the direction you believe it should go. 35 6) Check your answers using the following check figures: Budgeted CM= $301,000; Budgeted Op Inc. = $211,000; Variance for Commissions = 36 $8,685; Variance percentage for Commissions = 17.4 %, Unfavorable. 37 38 7)Use management by exception to determine which variances to investigate. HINT: In column K, use the "If function", along with "Absolute 39 value function" to find the variances that are larger than the decision rule. Also, since the decision rule is in B21 and you want Excel to always 40 compare to B21, you need to make it an absolute reference: $8$21. 41 For example, =IF(ABS(17)>($B$21),"yes","no"). After using the function, check each line to see whether your formula worked properly. 42 8) Center the U/F and Yes/No in the middle of the columns for easier readability. 43 9) Now check to see if everything ripples through the budget if you change an assumption. Try changing the assumed sales volume to 20,500 45 and Shipping expense to $2.10 per case. You should have a new total variance for operating income of $21,287, 9.8% variance, F, No. 46 47 10) Change the sales volume assumption back to $20,000 and the shipping expense assumption back to $2.00. 48 49 50 51 52 Part 21 DIRECTIONS for Flexible Budget Performance Report: 55 56 1) Copy and Paste all of the line items (account names) from the master budget performance report into the Flexible Budget Performance 57 report. 58 2) Copy and Paste all of the actual figures to the actual column of the Flexible Budget Performance report. 59 60 3) Formulate the Master Budget figures by referencing the appropriate cells in the master budget performance report (e.g.=B7). By doing so, if 61 any assumptions change, they will ripple through BOTH performance reports. 62 4) Build the Flexible Budget by using the ACTUAL SALES VOLUME achieved along with the original budget assumptions. This is very similar to Directions Performance Report A Analysis- page 1 A Analysis- page 2 Grading Rubric + - + 110%AutoSave . OFF 1 Flexible budget project_Student Version_ACCU 325(3) Q Q v Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) 11 A" A" Wrap Text v General Insert v Paste B I U . | [ ~ |DAY x Delete v Merge & Center v $ ~ % 9 68 48 Conditional Format Cell Sort & Find & Ideas Sensitivity Formatting as Table Styles Format X VF Filter Select 18 4 X V fx A B C D E F G H J K L M N 0 P Q R S 52 53 54 Part 2) DIRECTIONS for Flexible Budget Performance Report; 55 56 1) Copy and Paste all of the line items (account names) from the master budget performance report into the Flexible Budget Performance 57 report 58 2) Copy and Paste all of the actual figures to the actual column of the Flexible Budget Performance report. 59 60 3) Formulate the Master Budget figures by referencing the appropriate cells in the master budget performance report (e.g.=B7). By doing so, if any assumptions change, they will ripple through BOTH performance reports. 62 63 4) Build the Flexible Budget by using the ACTUAL SALES VOLUME achieved along with the original budget assumptions. This is very similar to 64 building a master budget, except you are using actual sales volume. Assume the actual sales volume achieved is in the same relevant range as the original budget assumptions. 65 66 5) Try using the "accounting" or "currency" settings to get dollar signs, commas, and brackets on negative numbers. Add underlines and 67 double underlines as needed. Remove any decimals. 68 6) Head the appropriate column for the Volume Variance and then use Excel formulas to populate the cells in that column (e.g., +H33-133). 70 Once you get the first cell done, copy and paste the formula to the other cells. 7) Head the appropriate column for the Flexible Budget Variance and then use Excel formulas to populate the cells in that column (e.g. +F33- H33). Again, copy and paste the formulas to the other cells. B) Use the following check figures to check your formulas so far: 75 Shipping Expense: $44,213 $1,293 $42,920 $2,920 $40,000 76 9) Format cells appropriately. Attention to detail makes a report look more professional. (For example, dollar signs using the accounting or currency format, underlines and double underlines where appropriate, zero decimal places for dollar amounts, etc.,). 78 10)To save time, we won't add the U and F designations to the flexible budget performance report. However, you'll need to mentally figure 80 out which way they go. The interpretation of the volume variance is a little problematic because it compares two budgets, rather than budget 81 us. actual. HINT: Think of the MASTER BUDGET as the company's target when determining whether the volume variance should be 82 designated as a U or F. 83 11) Complete analysis- page 1. 84 85 12) Complete analysis- page 2. 86 87 13) Check report against grading rubric. 88 89 Directions Performance Report Analysis- page 1 Analysis- page 2 Grading Rubric + - + 110%AutoSave OFF 1 Flexible budget project_Student Version_ACCU 325(3) Q Q v Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) 11 = 2 Wrap Text v General Insert v x Delete v 2 x AY- O. Paste BIU V V LA- E Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Ideas Sensitivity Formatting as Table Styles Format X VF Filter Select D25 4 fx B C D E G H K M N 0 P Q 2 Budget assumptions: Kelsey's Frozen Confectionaries Master Budget Performance Report Volume 20,000 cases For the month ended June 30 Are you using Sales Revenue 50.00 per case formulas/references? MASTER MASTER 6 Percent sold on credit 90% percent of sales ACTUAL UDGET VARIANCE VAR. % For U Investigate? BUDGET VARIANCE VAR. % For U Investigate Volume (in cases) 21,460 FALSE FALSE FALSE FALSE FALSE 8 Variable Expenses: 9 CGS 30.00 per case Sales Revenue S 1,131,020 FALSE FALSE FALSE FALSE FALSE 10 Sales Commission 10 per case Less Variable Expenses: Shipping Expense 2.00 per case Cost of Goods Solo 682,880 FALSE FALSE FALSE FALSE FALSE 12 Bad Debt Expense 1.00% percent of credit sales Sales Commissions 58,685 FALSE FALSE FALSE FALSE FALSE 13 Shipping Expense 44,213 FALSE FALSE FALSE FALSE FALSE 14 Fixed Monthly Expenses: Bad debt expense 16,930 FALSE FALSE FALSE FALSE FALSE 15 Salaries 40,000 Contribution Margin 328,312 FALSE FALSE FALSE FALSE FALSE 16 Lease on Distribution center Less Fixed Expenses 17 Depreciation on fleet & Equip 12,000 Salaries 43,000 FALSE FALSE FALSE FALSE FALSE 18 Advertising 10,00 Lease on Distn center 15,500 FALSE FALSE FALSE FALSE FALSE 19 Office rent, phone, internet 11,000 Deprec. on fleet/equip 0DO'ZT FALSE FALSE FALSE FALSE FALSE 20 Advertising 7,750 FALSE FALSE FALSE FALSE FALSE 21 Management by exception 15% Investigation rule Office rent, phone, net 12,300 FALSE FALSE FALSE FALSE FALSE 22 Operating Income 237,762 FALSE FALSE FALSE FALSE FALSE 23 HINTS on EXCEL 24 1) If you get a series of hashmarks (######) it just means your column width CHECK FIGURES: MASTER BUDGET is not wide enough to accomodate the number. Increase column width MASTER 25 and/or delete extra decimal places. ACTUAL BUDGET VARIANCE VAR. % 26 CM CORRECT INCORRECT INCORRECT INCORRECT 2. To add numbers, put your cursor on the cell where you want the answer 27 to be. Then keystroke the addition sign (+) and then click on the cell you Operating want add, then keystroke the addition sign again (+) and click on the second Should follow the same format above in terms of account titles/subtitles. Income CORRECT INCORRECT INCORRECT INCORRECT 28 29 cell you want to add. Continue in the same fashion for multiple cells. Use Kelsey's Frozen Confectionaries the subtraction sign (-) for subtracting cells. If you have an array of cells to Fexible Budget Performance Report 30 add, you can use the AutoSum button For the month ended June 30 31 3. To multiply, use the * symbol. To divide, use the / symbol. Are you using formulas/references? Flexible different cells. 4. Make use of copy/paste commands when you want similar forumulas in FLEXIBLE budget FLEXIBLE Volume MASTER 32 ACTUAL BUDGET MASTER BUDGET variance BUDGET Variance BUDGET 33 Enter text FALSE FALSE FALSE FALSE 34 5. Always check the logic of your results after entering formulas to make 35 sure the spreadsheet is doing what you really want it to do. Enter text FALSE FALSE FALSE FALSE 36 Enter text 37 Enter text FALSE FALSE FALSE FALSE Directions Performance Report Analysis- page 1 A Analysis- page 2 Grading Rubric + + 100%AutoSave OFF 1 Flexible budget project_Student Version_ACCU 325(3) Q Q v Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) 11 2 Wrap Text General Insert v x Delete v 2 x AY- O. Paste BIU V V LA- E = Merge & Center v $ ~ % " Conditional Format Cell Ideas Styles Format X VF Sort & Find & Sensitivity Formatting as Table Filter Select 124 41 M N w Z AA AB AC AD AE AF Kelsey's Frozen Confectionaries Master Budget Performance Report 20,000 cases For the month ended June 30 Are you using $ 50.00 per case formulas/references? MASTER BUDGET VARIANCE VAR. * For U Investigate? MASTER 9016 percent of sales ACTUAL BUDGET VARIANCE VAR. % For U Investigate? Volume (in cases] 21,460 FALSE FALSE FALSE FALSE FALSE 30.00 per case 1,131,020 FALSE FALSE FALSE FALSE FALSE 2.50 per case ales Revenue 200 per case Less Variable Expenses: Cost of Goods Sold FALSE FALSE FALSE FALSE FALSE 10016 percent of credit sales Sales Commissions 58,685 FALSE Shipping Expense FARE FALSE Bad debt expense 44.213 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 40,000 Contribution Manin 328,312 FALSE FALSE FALSE FALSE FALSE 17,000 ess Fixed Expense 12,000 Salaries 43,000 FALSE FALSE FALSE FALSE FALSE 10,200 15,50 FALSE 11,000 Lease on Distn center FALSE FALSE Deprec. on fleet/equip 12,000 FALSE FALSE FALSE EPI'S Advertising 7,750 FALSE FALSE FALSE FALSE FALSE FALSE Office rent, phone, net 12,300 FALSE FALSE FALSE FALSE FALSE perating Income 237.762 FALSE FALSE FALSE FALSE FALSE 4 |marks (erases) it just means your column width CHECK FIGURES: MASTER BUDGET adate the number. Increase column width MASTER 25 pl ACTUAL BUDGET VARIANCE V 26 CM CORRECT INCORRECT INCORRECT INCORRECT cursor on the cell where you want the answer Edition sign (+] and then click on the cell you Operating accition sign again (*] and click on the second Should follow the same format above in terms of account titles/subtitles. Income CORRECT INCORRECT INCORRECT INCORRECT 28 be in the same fashion for multiple cells. Use Kelsey's Frozen Confectionaries 29 btracting cells. If you have an array of cell to exible Budget Performance Report 30 m bution. For the month ended June 30 31 Inbol. To divide, use the / symbol Are you using formulas/references? Flexible ommands w milar for umulas in budact ACTUAL FLEXIBLE BUDGET FLEXIBLE Volume MASTER MASTER BUDGET BUDGET Variance BUDGET 34 you Enter tex FALSE FALSE FALSE FALSE Your results after entering formulas to make 35 3 what you realy want it to do. Enter text FALSE FALSE FALSE FALSE Enter text FALSE FALSE FALSE FALSE FALSE FALSE FALSE Enter text FALSE FALSE FALSE FALSE Enter text FALSE FALSE FALSE FALSE Contribution Margin FALSE FALSE FALSE FALSE Enter text Enter text FALSE FACE Enter text FALSE FALSE FALSE FALSE Enter text FALSE FALSE Enter text FALSE FALSE FALSE Operating Income FALSE FALSE FALSE FALSE CHECK FIGURES: FLEXIBLE BUDGET FLEXIBLE ACTUAL VAR. BUDGET FLEXIBLE VOLUME MASTER BUDGET VAR. BUDGET INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT Make sure the CM numbers are entered on row 41 Operating and the Operating Income numbers on row 48 Income INCORRECT INCORRECT INCORRECT INCORRECT INCORRECT Directions A Performance Report Analysis- page 1 A Analysis- page 2 Grading Rubric + + 68%AutoSave . OFF 1 Flexible budget project_Student Version_ACCU 325(3) Q Q v Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) 11 Wrap Text General Insert v x Delete v 2 x AY- O. Paste BIU V V DAY E Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Ideas Sensitivity Formatting as Table Styles Format XVF Filter Select A16 X V fx | How much of the master budget variance for CGS was caused by some factor OTHER THAN volume? D G Answer the following questions (dollar amount; For U?; Reason, when asked) Dollar amount F or U Possible reaso Dollar Amt F or U Yes/No What is the flex budget variance for operating income INCORRECT INCORRECT What is the volume variance for operating income? INCORRECT INCORRECT What do the flex budget variance and volume variance for operating income sum to? INCORRECT INCORRECT Does this equal the total master budget variance Yes or No? INCORRECT Should it always sum (or net) to the total master budget variance, even if one is U and one is F? Yes or No? INCORRECT 10 How much of the master budget variance for Sales was caused by an UNANTICIPATED increase in volume? INCORRECT INCORRECT 11 12 How much of the master budget variance for Commission Expense was caused by an UNANTICIPATED increase in volume? INCORRECT INCORRECT 13 14 How much of the master budget variance for Advertising Expense was caused by an UNANTICIPATED increase in volume? INCORRECT INCORRECT 15 How much of the master budget variance for CGS was caused by some factor OTHER THAN volume? INCORRECT INCORRECT What could account for this variance? Enter reason here 17 18 9 How much of the master budget variance for Sales Revenue was caused by some factor OTHER THAN volume? INCORRECT INCORRECT What could account for this variance? Enter reason here 20 21 22 How much of the master budget variance for Advertising Expense was caused by some factor OTHER THAN volume? INCORRECT INCORRECT What could account for this variance? Enter reason here 23 Directions A Performance Report A Analysis- page 1 A Analysis- page 2 Grading Rubric + - + 93%AutoSave OFF 1 Flexible budget project_Student Version_ACCU 325(3) Q Q v Home Insert Draw Page Layout Formulas Data Review View Share Comments Calibri (Body) 11 "A" A" Wrap Text General Insert v x Delete v 2 x AY- O. Paste BI U V V LA~ E E Merge & Center v $ ~ % " Conditional Format Cell Format XVF Sort & Find & Ideas Sensitivity Formatting as Table Styles Filter Select C3 fx E F G H L M N 0 a R S T U W Managers and auditors always analyze the variances between actual and budget. Say you were auditing this client and they gave you the following story (below). Is the story consistent with the overall pattern of variances? Which elements of the story are consistent with the explanations? Which variances are inconsistent, or left unexplained? Be sure to analyze every line item on the budget. Client Story: In order to motivate our sales force to increase sales, we decided to increase our commissions and salaries and increase marketing. At the same time, our supplier increased its prices, and we felt we could pass that cost increase on to our customers in the form of price increase. However, with the additional pressure to make sales, coupled with the increased sales price, we had to loosen credit terms on sales (extend more credit to more customers). We also had to lease a little more distribution space and acquire another truck to handle the volume increase. Our shipping expense relates to gasoline used for deliveries. Luckily, gas prices went down from what we originally expected this year. 12 In the table below, classify each account on the budget according to whether the variances in the performance report are consistent, inconsistent, or left unexplained by 13 the client's story. Place an "X" in the appropriate column. 14 15 16 CHECK FIGURES: 21 Income Statement line item 22 Consistent Inconsistent Unexplained 23 All sales volume variances INCORRECT 24 Flexible budget variances: 26 Sales Revenue INCORRECT 27 Cost of Goods sold INCORRECT 28 Commission expense INCORRECT 29 Shipping Expenses INCORRECT 30 Bad debt expense INCORRECT 31 Salaries expense INCORRECT 32 Lease of distribution center INCORRECT 33 Depreciation of fleet & Equip INCORRECT 34 Advertising expense INCORRECT 35 Ofice rent, phone, internet INCORRECT 36 37 Directions Performance Report A Analysis- page 1 A Analysis- page 2 Grading Rubric + - + 95%
