90.2K

Verified Solution

Link Copied!

This is all the imformation given. I would really appriciate the help. Thx in advance :)

Background Apple Inc. manufacturers I-phones. You have been hired as a cost accountant and your assignment to create a master budget using EXCEL.. You have been working with various departments to gather financial data to assemble a master budget. One reason you have chosen this format is that upper management will probably make some lastminute changes to sales and possible changes to some other cost forecasts. So, to simplify this problem you MUST create separate sheets for each budget and all budgets should link together so changes can be easily made. For example, if you change the sales forecast, then all the remaining numbers in all other budgets would change without having to change numbers manually in each budget. Some of the information has been input for you but you need to calculate the remaining data points to produce an income statement. Here are the steps: 1. You must setup all eight budgets using Excel. Starting with the sales budget, calculate the sales budget forecast for all four quarters and calculate the total column 2. Each budget should be prepared on individual Excel spreadsheets (on separate tabs) with pages that include links from previous budgets to avoid copying and pasting the same numbers from page to page. Formulas must also be included in the appropriate cells within each spreadsheet. (Demonstrate to the CEO that you have a good working knowledge of EXCEL).. 3. Each budget should professionally represent all the budget information; must be easy to understand so it can be presented to the CEO and the owners of the business. 4. Briefly discuss your assumptions and any challenges you encountered while preparing the budget. (Add this information to the research portion of your paper. (NOT on EXCEL) Budgot Project Note: increase each quarter sibes by 5% Note: Ending imentory is estmated based on 20% of the next quater's volumes. Note Beginning imentory is estimalod based on 10% of the cument quater's volumes Direct Materials SPurchases Note: Beginning inventory is 20% based on the curent quatters budget Ending imentory is 10\% of the next quatter's budget Deect Labor Unts per unit $ Total DL Overhead Production units Floed \begin{tabular}{|l|r|r|r|r|r|} \hline Depreciation Mach & 50000 & 50000 & 50000 & 50000 & 200000 \\ \hline Dep. Bldg & 70000 & 70000 & 70000 & 70000 & 280000 \\ \hline Supervision & 1,80000 & 1,80000 & 1,80000 & 1.30000 & 720000 \\ \hline MYA & 40000 & 40000 & 40000 & 40000 & 1,90000 \\ \hline Oither & 60000 & 60000 & 60000 & 60000 & 240000 \\ \hline Total fixed & 4,00000 & 4,00000 & 4,00000 & 400000 & 1600000 \\ \hline Grand tolal & 4,00000 & 4,00000 & 4,00000 & 400000 & 1600000 \\ \hline \end{tabular} S\&Admin budge sales Delvery 0.1031500 \begin{tabular}{ll|l|l|l|l} \hline S\&Admin budge sales & & 3,150 & - & - & - \\ \hline Delivery & 0.10 & 315,00 & - & - & - \\ \hline Sales & 0.15 & 47250 & - & - & - \\ \hline Accounting & 0.05 & 157.50 & - & - & - \\ \hline Other & 0.20 & 630.00 & - & - & - \\ \hline Total Variable S\&A & & 1,575,00 & - & - & - \\ \hline \end{tabular} Fuod S\&A Direct material used DM imventory 12/31 Purchases Cost of direct materlals avalable for use less= direct materials imventory, 12/31 Cost of direct materials used direct labor Manut OH Total manulacturing cost WP 1220 less WIP 12/20 Cost of goods manulactured Budgeted lncome Statement Seles ocs Finished goods inventory 1231 Cost of good manulactured Less Finished goods (beginning) 0CC Gross Margin SRA Income from operations Income tares (30\%) Net income Background Apple Inc. manufacturers I-phones. You have been hired as a cost accountant and your assignment to create a master budget using EXCEL.. You have been working with various departments to gather financial data to assemble a master budget. One reason you have chosen this format is that upper management will probably make some lastminute changes to sales and possible changes to some other cost forecasts. So, to simplify this problem you MUST create separate sheets for each budget and all budgets should link together so changes can be easily made. For example, if you change the sales forecast, then all the remaining numbers in all other budgets would change without having to change numbers manually in each budget. Some of the information has been input for you but you need to calculate the remaining data points to produce an income statement. Here are the steps: 1. You must setup all eight budgets using Excel. Starting with the sales budget, calculate the sales budget forecast for all four quarters and calculate the total column 2. Each budget should be prepared on individual Excel spreadsheets (on separate tabs) with pages that include links from previous budgets to avoid copying and pasting the same numbers from page to page. Formulas must also be included in the appropriate cells within each spreadsheet. (Demonstrate to the CEO that you have a good working knowledge of EXCEL).. 3. Each budget should professionally represent all the budget information; must be easy to understand so it can be presented to the CEO and the owners of the business. 4. Briefly discuss your assumptions and any challenges you encountered while preparing the budget. (Add this information to the research portion of your paper. (NOT on EXCEL) Budgot Project Note: increase each quarter sibes by 5% Note: Ending imentory is estmated based on 20% of the next quater's volumes. Note Beginning imentory is estimalod based on 10% of the cument quater's volumes Direct Materials SPurchases Note: Beginning inventory is 20% based on the curent quatters budget Ending imentory is 10\% of the next quatter's budget Deect Labor Unts per unit $ Total DL Overhead Production units Floed \begin{tabular}{|l|r|r|r|r|r|} \hline Depreciation Mach & 50000 & 50000 & 50000 & 50000 & 200000 \\ \hline Dep. Bldg & 70000 & 70000 & 70000 & 70000 & 280000 \\ \hline Supervision & 1,80000 & 1,80000 & 1,80000 & 1.30000 & 720000 \\ \hline MYA & 40000 & 40000 & 40000 & 40000 & 1,90000 \\ \hline Oither & 60000 & 60000 & 60000 & 60000 & 240000 \\ \hline Total fixed & 4,00000 & 4,00000 & 4,00000 & 400000 & 1600000 \\ \hline Grand tolal & 4,00000 & 4,00000 & 4,00000 & 400000 & 1600000 \\ \hline \end{tabular} S\&Admin budge sales Delvery 0.1031500 \begin{tabular}{ll|l|l|l|l} \hline S\&Admin budge sales & & 3,150 & - & - & - \\ \hline Delivery & 0.10 & 315,00 & - & - & - \\ \hline Sales & 0.15 & 47250 & - & - & - \\ \hline Accounting & 0.05 & 157.50 & - & - & - \\ \hline Other & 0.20 & 630.00 & - & - & - \\ \hline Total Variable S\&A & & 1,575,00 & - & - & - \\ \hline \end{tabular} Fuod S\&A Direct material used DM imventory 12/31 Purchases Cost of direct materlals avalable for use less= direct materials imventory, 12/31 Cost of direct materials used direct labor Manut OH Total manulacturing cost WP 1220 less WIP 12/20 Cost of goods manulactured Budgeted lncome Statement Seles ocs Finished goods inventory 1231 Cost of good manulactured Less Finished goods (beginning) 0CC Gross Margin SRA Income from operations Income tares (30\%) Net income

Answer & Explanation Solved by verified expert

See Answer