continued from prevous post

Tile Romeinsert Draw P age 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 H I KLMNO AI - X Flexible Budgets and Standard Cost Systems o E F G Flexibt 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 2018 Actual Results Static Budget Units 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 the Instructions tab you will be marked wrong. Possible points Requirements 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. 2 Calculate the Static Budget Variance for operating income, and label it as a F (favorable) or (unfavorable) variance. 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 Instructions ENTERANSWERS Auto Save H B O scales_Miller-Nobles_6e-12e-Using_Excel_Ch23_Start - Prot 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 AL X Flexible Budgets and Standard Cost Systems E F G H I 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 Nom 28 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 MyAccountingLab 2 Click on the Assignment you were working on Protcles vie scales Miller-Nobles,be-12e-Using,Excel_Ch23 Start AutoSave Search View Help Review Data Formulas Draw Page Layout Home Insert File Enable Editing Be careful-files from the Internet can contain vinuses. Unless you need to edit, it's safer to stay in Protected View. iPROTECTED VIEW Prepare a flexible budget performance report, including the heading. Use the ABS function when calculating variances, and use the de B2 M. D B A Prepare a flexible budget performance report, including the heading. Use the ABS function when calculating variances, and use the drop-down selections for F or 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 drop- down to identify the variance as either F or U blank.) (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab you will be marked 2 3 4 5 6 7 81 Column 5 Column 4 Column 3 Column 1 Column 2 Col (3)- Col (5) Col (1)-Col (3) 10 Budget Flexible Sales Volume Flexible Budget Actual For U Static Budget F or U Amounts Budget Variance Variance Results per Unit 11 Units 12 Sales Revenue Variable Costs Contribution Margin Fixed Costs 13 14 15 16 Operating Income 17 18 19 2 20 Calculate the Static Budget Variance for operating income, and label it as a F (favorable) or U (unfavorable) variance. 21 ENTERANSWERS Instructions a ROTECTED VIEW Be carefulles from the Internet can contain viruses. Unless you B2 X Prepare a flexible budget performance report, including the heading. Use the ABS function when calculating variances, and D 16 17 Fixed Costs Operating Income Calculate the Static Budget Variance for operating income, and label it as a F (favorable) or (unfavorable) variance. (Always use cell references and formulas where appropriate to receive full credit.) Static Budget Variance for Operating Income (1) - (5) Amount: For HINTS Cell Hint: 32 13:14, D12:017, 112:117 Use cell references to the values in the data table provided at the top of the instructions worksheet for calculations. 33 13:17, H13:H17, 025 Use the function ABS() to calculate the variances. 34 13:17, 113:117 Do not choose the descriptions and leave cells blank if corresponding variances equal zero. Instructions ENTERANSWERS Tichina Scales sales, Miller Noblesse 12e-Using Excel_C34Start () - Protected View - Excel The Homewarta w a ge Layout Formulas Data Review View Help Search 0 PRORICTED VIEW Be carefutes from the Internet can contain viruses. Unless you need to edit, it's safe to stay in Protected view Enable Editing 526 - 1 Responsibility Accounting and Performance Evaluation 2. Using Excel for key performance Indicators (KPIs) 4 The US Solar Company has data for the four divisions for the year, and wants the results for the three specified key performance indicators (KPIS). Return on investment (RON) Division Operating Income Average Total Assets Net Sales Profit Margin Ratio Asset Turnover Ratio rast $ 1.580,000.00 $ 12,000, 750.00 $ 25,900,000.00 North 650,000.00 4.875,000.00 6117,500.00 South 275,050.00 995,090.00 5,720.00 West 2,300,000.00 13.000.000.00 9.200,000.00 11 Use the blue shaded areas on the ENTERANSWERS tab for inputs 12 ALWAYS use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction tab you will be marked wrong Possible points 15 Requirements 16 1 Create an Excel table from the data. 4. Rename the table as Solar KPI D. Select another table style for the table. 2 Use table arithmetic for the formulas (rounded to three decimal places) for a. Profit Margin Ratio b. Asset Turnover Ratio c. Return on investment (RON) 3 Excel Skills Instructions ENTERANSWERS