Please include the formulas used. Thank you!

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 last-minute 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) Notes for Budget schedules: - Desired ounces of ending direct materials inventory =20% of next quarter's budgeted production needs in ounces. - Desired ounces of beginning direct materials inventory =10% of current quarter's budgeted production needs in ounces. - For CGM Schedule - It is the company's policy to have no units in process at the end of the year. Quarter Sales Units SP Total Sales Production + Ending -Beginning 500.0 250.0 400600 4th quarter is given Production Direct Materials \begin{tabular}{l} DM \\ 3 ounces \\ Total ounces \\ less beginning \\ + Ending (10% \\ Total purchas \\ Cost \\ \hline Purchases \end{tabular} Direct Labor Units per unit is \begin{tabular}{|c|crr} 2.5 & 2.5 & 2.5 & 2.5 \\ 2,200 & & & \\ 1,100 & & & \\ \hline & & & 2,800 \\ \hline \end{tabular} Total DL 0.30.30.30.3 Ovemead Production units Fixed Grand total 4,000.00 4,000.004,000.004,000.00 16,000.00 S\&Admin budge sales \begin{tabular}{ll} Delivery & 0.10 \\ Sales & 0.15 \\ \hline \end{tabular} S\&Admin budge sales \begin{tabular}{|l|l|} \hline Delivery & 0.10 \\ \hline Sales & 0.15 \\ \hline Accounting & 0.05 \\ \hline Other & 0.20 \end{tabular} Total Variable S\&A Fixed S\&.A Salaries Depre Taxes \& insurance Total fixed CGM Cost of Good Manufactured Budget CGM Cost of Good Manufactured Budget Direct material used DM inventory 12/31 Purchases cost of direct materials available for use less= direct materials inventory, 12/31 cost of direct materials used dired labor Manuf OH Total manufaduring cost WIP 12/31 less WIP 12/31 cost of goods manufactured Budgeted Income Statement Sales cos Finished goods inventory 12/31 cost of good manufactured Less Finished goods (beginning) cos Gross Margin S\&A Income from operations Income taxes (30%) Net Income Apple Inc. would like to have an estimate of the company's net income for 2021. Calculate Apple's net income in 2021 by completing the operating budgets and budgeted income statement that follow. 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 last-minute 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) Notes for Budget schedules: - Desired ounces of ending direct materials inventory =20% of next quarter's budgeted production needs in ounces. - Desired ounces of beginning direct materials inventory =10% of current quarter's budgeted production needs in ounces. - For CGM Schedule - It is the company's policy to have no units in process at the end of the year. Quarter Sales Units SP Total Sales Production + Ending -Beginning 500.0 250.0 400600 4th quarter is given Production Direct Materials \begin{tabular}{l} DM \\ 3 ounces \\ Total ounces \\ less beginning \\ + Ending (10% \\ Total purchas \\ Cost \\ \hline Purchases \end{tabular} Direct Labor Units per unit is \begin{tabular}{|c|crr} 2.5 & 2.5 & 2.5 & 2.5 \\ 2,200 & & & \\ 1,100 & & & \\ \hline & & & 2,800 \\ \hline \end{tabular} Total DL 0.30.30.30.3 Ovemead Production units Fixed Grand total 4,000.00 4,000.004,000.004,000.00 16,000.00 S\&Admin budge sales \begin{tabular}{ll} Delivery & 0.10 \\ Sales & 0.15 \\ \hline \end{tabular} S\&Admin budge sales \begin{tabular}{|l|l|} \hline Delivery & 0.10 \\ \hline Sales & 0.15 \\ \hline Accounting & 0.05 \\ \hline Other & 0.20 \end{tabular} Total Variable S\&A Fixed S\&.A Salaries Depre Taxes \& insurance Total fixed CGM Cost of Good Manufactured Budget CGM Cost of Good Manufactured Budget Direct material used DM inventory 12/31 Purchases cost of direct materials available for use less= direct materials inventory, 12/31 cost of direct materials used dired labor Manuf OH Total manufaduring cost WIP 12/31 less WIP 12/31 cost of goods manufactured Budgeted Income Statement Sales cos Finished goods inventory 12/31 cost of good manufactured Less Finished goods (beginning) cos Gross Margin S\&A Income from operations Income taxes (30%) Net Income Apple Inc. would like to have an estimate of the company's net income for 2021. Calculate Apple's net income in 2021 by completing the operating budgets and budgeted income statement that follow