Flexible Budgets and Standard Cost Systems
Using Excel to prepare a flexible budget perdormance report
Flexible Budgets and Standard Cost Systems Using Excel to prepare a flexible budget performance report Pilchuck Company manufactures tote bags and has provided the following information for September 2011 Units Actual Results Static Budget 11,000 12,000 Sales Revenue 368,000 384,000 Variable Costs 183,000 198,000 Contribution Margin 185,000 186,000 Fixed Costs 76,000 77,184 Operating Income 109,000 108,816 Use the blue shaded areas on the ENTERANSWERS tab for inputs. Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from Requirements Prepare a flexible budget performance report, including the heading. Use the ABS function when 1 the drop-down selections for For U when describing the variances. 2 Calculate the Static Budget Variance for operating income, and label it as a F (favorable) or U (unfi Excel Skills 1 ABS function (Absolute Value) to calculate the variances. 2 Data validation to select a F (favorable) or U (unfavorable) variance 3 Cell references - both absolute and relative Saving & Submitting Solution 1 Save file to desktop. a. Create folder on desktop, and label COMPLETED EXCEL PROJECTS 2 Upload and submit your file to be graded. a. Navigate back to the activity window - screen where you downloaded the initial spreadsheet b. Click Choose button under step 3; locate the file you just saved and click Open C. Click Upload button under step 3 d. Click Submit button under step 4 Viewing Results 1 Click on Results tab in My AccountingLab 2 Click on the Assignment you were working on 3 Click on Project link; this will bring up your Score Card 4 Within Score Card window, click on Live Comments Report (lower right) to download spreadsheet Data Formulas Review View Help Home Insert Page Layout Custom 2 Wrap Text Calibri 11 - AA == a. A- Merge & Center $ %) Conditiona Formatting ite BIU Number Alignment Font -pboard X =Instructions!D8 13 K M H F D E B 1 Prepare a flexible budget performance report, including the heading. Use the ABS function when calculating variances, and use the drop-down selections for For U when describing the variances. Note: start with calculating the budget amounts, then calculate variances and choose appropriate descriptions of the variances. (For variances with a zero amount, make sure to include the result of O in the appropriate cell and leave the dropdown to identify the variance as either For U blank.) (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the instructions tab you will 2 3 4 5 6 Pilchuck Company Flexible Budget Performance Report Month Ended September 30, 2018 Column 1 Column Column 5 9 10 Column 2 COL 1 Col Flexible Budget For U Variance Column 4 Col(3) - Col (5) Sales Volume For U Variance Budget Amounts Actual Results Flexible Budget 11.000 Static Budget 12.000 per Unit 11 12 13 14 15 16 17 F U Units Sales Revenue Variable Costs Contribution Margin Fixed Costs Operating Income 11,000 5 368,000 183 000 185.000 F F 19 20 2 Calculate the State Budget Variance for operating income, and label it as a Ffavorable) or U (unfavorable variance. (Always use cell references and formulas where appropriate to receive full credit.) 22 Static Budget Variance for Operating Income (1) (5) Instructions ENTERANSWERS 19 20 2 21 Calculate the Static Budget Variance for operating income, and label it as a F (favorable) or U (unfavorable) variance. 22 (Always use cell references and formulas where appropriate to receive full credit.) 23 24 Static Budget Variance for Operating Income (1) - (5) 25 Amount: 26 For U 27 28 29 30 HINTS 31 Cell Hint: 32 C13.014, 12:017. 112:17 Use cell references to the values in the data table provided at the top of the instructions worksheet for calculations 33 E13:17, H13:H17.025 Use the function -ABS) to calculate the variances. 34 F13:F17, 113117 Do not choose the descriptions and leave cells blank if corresponding variances equal zero. 35 36 37 38 39
