Excel Class Exercise - Solution 2021 Budget Bicycles Sold Average Price Total Sales Revenue $ 16,000 2,500 40,000,000 Labor Materials Variable Overhead Fixed Manufacturing Overhead Gross Margin 8,000,000 20.00% 4,000,000 10.00% 2,400,000 6.00% 4,000,000 21,600,000 54.00% Office Salaries Office Lease Expense Office Depreciation Expense Advertising Total Operating Expenses 5,000,000 2,000,000 500,000 1,000,000 8,500,000 21.25% Operating Income Income Tax Net Income 13,100,000 3,930,000 9,170,000 32.75% 30.00% 22.93% Bicycles to sell to Breakeven 7,813 Phase 11 Adapt your Budget Model so that most educated business professionals can use it. The idea is that you can give this budgeting tool to someone in Marketing, Operations, Human Resources, etc. and they could easily use it. . Some Guidelines 1. Create separate section for all inputs and data entry item. Have users enter Number of Bicycles sold, Average Price, Cost of Labor is X% of Sales, etc. It should be fairly easy for the user to make changes. For example, the use can change the average sales price of a bicycle from $2,500 to $3,000 od $2,000. Change Labor from 20% of Sales to 25% or 18%. 2. Create separate section for all output. . There should only be formulas in this section, referring to your data input section. For example, if you want to report Total Sales Revenue in cell K2, do NOT write =16000*2500. The reason NOT to do this is the user may never see what is in cell K2. If the user wants to change the sales price to $3,000, they won't know they need to change cell K2. Additionally, if you are using your own Excel and you want to make that change, you may forget to change cell K2 to =16000 3000. Excel Class Exercise - Solution 2021 Budget Bicycles Sold Average Price Total Sales Revenue $ 16,000 2,500 40,000,000 Labor Materials Variable Overhead Fixed Manufacturing Overhead Gross Margin 8,000,000 20.00% 4,000,000 10.00% 2,400,000 6.00% 4,000,000 21,600,000 54.00% Office Salaries Office Lease Expense Office Depreciation Expense Advertising Total Operating Expenses 5,000,000 2,000,000 500,000 1,000,000 8,500,000 21.25% Operating Income Income Tax Net Income 13,100,000 3,930,000 9,170,000 32.75% 30.00% 22.93% Bicycles to sell to Breakeven 7,813 Phase 11 Adapt your Budget Model so that most educated business professionals can use it. The idea is that you can give this budgeting tool to someone in Marketing, Operations, Human Resources, etc. and they could easily use it. . Some Guidelines 1. Create separate section for all inputs and data entry item. Have users enter Number of Bicycles sold, Average Price, Cost of Labor is X% of Sales, etc. It should be fairly easy for the user to make changes. For example, the use can change the average sales price of a bicycle from $2,500 to $3,000 od $2,000. Change Labor from 20% of Sales to 25% or 18%. 2. Create separate section for all output. . There should only be formulas in this section, referring to your data input section. For example, if you want to report Total Sales Revenue in cell K2, do NOT write =16000*2500. The reason NOT to do this is the user may never see what is in cell K2. If the user wants to change the sales price to $3,000, they won't know they need to change cell K2. Additionally, if you are using your own Excel and you want to make that change, you may forget to change cell K2 to =16000 3000
