I would like to know how to solve for each problem and how to achieve the component budgets that are included in the files, so that i can apply them to an excel spreadsheet. By the way, the numbers used only from the excel for the problems are used under -Huamani.

Last Name sample Abramson Avery Billerback Byrne Cayo Flaherty Gaiotti Germinaro Greene Guerrier Holden Huamani Koukos Massop Mikem Mirkhani Nunez Pacete Pascasio Ranger Sant'anna Saraguro Strain Tirella Trocolla Woellhof Zipparo (1) 4th Q sales Direct labor hours Units direct materials % next Q direct materials (2) (3) (4) (5) depreciation % 55,000 5 3 30% 55,010 4.5 3 35% 55,020 5 4 25% 55,030 4.5 3 35% 55,040 5 4 25% 55,050 4.5 3 35% 55,060 5 4 25% 55,070 4.5 3 35% 55,080 5 4 25% 55,090 4.5 3 35% 55,100 5 4 25% 55,110 4.5 3 35% 55,120 5 4 25% 55,140 4.5 3 35% 55,150 5 4 25% 55,160 4.5 3 35% 55,170 5 4 25% 55,180 4.5 3 35% 55,190 5 4 25% 55,200 4.5 3 35% 55,210 5 4 25% 55,220 4.5 3 35% 55,230 5 4 25% 55,240 4.5 3 35% 55,260 5 4 25% 55,270 4.5 3 35% 55,280 5 4 25% 55,290 4.5 3 35% 35% 25% 35% 25% 35% 25% 35% 25% 35% 25% 35% 25% 35% 25% 35% 25% 35% 25% 35% 25% 35% 25% 35% 25% 35% 25% 35% 25% Master Budget Spreadsheet Problem: Accounting 361 Electra Company is a high technology organization that produces a mass-storage system. The design of Electra's system is unique and represents a breakthrough in the industry. The units Electra produces combine positive features of dual technologies. The company is completing the fifth year of operations and is preparing to build its master budget for the coming year, 2014. The budget will detail each quarter's activity and the activity for the year in total. The master budget will be based on the following information: a. Fourth quarter sales for 2013 are (1) 55,000. The selling price is $1,200 per unit. All sales are credit sales. Electra collects 85% of all sales within the quarter in which they are made, the other 15% are collected in the following quarter. There are no bad debts. b. Unit sales by quarter for 2014 are projected as follows: First quarter 60,000 units Second quarter 65,000 units Third quarter 75,000 units Fourth quarter 90,000 units c. There is no beginning inventory of finished goods. Electra is planning the following ending finished goods inventories for each quarter: First quarter Second quarter Third quarter Fourth quarter 13,000 units 15,000 units 20,000 units 10,000 units d. Each mass storage unit uses (2) 5 hours of direct labor and (3) 3 units of direct materials. The direct labor rate is $15 per hour. e. There are 65,700 units of direct materials in beginning inventory as of January 1, 2013. At the end of each quarter, Electra plans to have (4) 30 % of the direct materials needed for the next quarter's unit sales. Electra will end the year with 100,000 units of direct materials. f. Electra buys direct materials on account. One-half of the purchases is paid for in the quarter of acquisition and the remaining half is paid for in the following quarter. Wages and salaries are paid on the fifteenth and thirtieth of each month. g. Fixed overhead totals $1 million each quarter. Of this total, (5) 3% represents depreciation. All other fixed expenses are paid for in cash in the quarter incurred. h. Variable overhead is budgeted at $6 per direct labor hour. All variable overhead expenses are paid for in the quarter incurred. i. Fixed selling and administrative expenses total $250,000 per quarter, including $50,000 depreciation. j. Variable selling and administrative expenses are budgeted at $10 per unit sold. All selling and administrative expenses are paid for in the quarter incurred. k. The balance sheet as of December 31, 2013 is as follows: ASSETS Cash $ 250,000 Inventory 5,256,000 Accounts receivable 3,300,000 Plant and equipment 33,500,000 Total assets $ 42,306,000 LIABILITIES AND EQUITY Accounts payable Capital stock Retained earnings Total liabilities and equity $ $ 7,248,000* 27,000,000 8,058,000 42,306,000 *for purchase of materials only l. Electra requires a minimum $250,000 cash balance for the end of each quarter. An open line of credit is available at a local bank. All borrowing is done at the beginning of a month and all repayments are made at the end of the month, borrowing must be in multiples of $20,000. The annual interest rate is 12%. Interest is paid only at the time of repayment of principal. m. Electra will pay quarterly dividends of $150,000 for each quarter of 2014. At the end of the fourth quarter, $500,000 equipment will be purchased. Required: NOTE: Each student has an individual assignment for numbers (1)-(5) as underlined above. See the accompanying spreadsheet for your assignment. All spreadsheets must be submitted on Blackboard by 1:40 Dec. 2. Make sure to use your last name when you save the document. Using an Excel spreadsheet prepare a master budget for Electra Company for each quarter of 2014 and for the year in total. Label each Tab. Tab 1 in your spreadsheet should be labeled [Your Last Name] Student Data for example, \"Ganon Student Data\". Tab 1 should be a copy of the Student Assignment Spreadsheet. The data in Tab 1 must be linked to the formulas and cells in all other Tabs. Each budget (a-i) should be presented on a separate Tab. For example: Tab 2 in your spreadsheet should be labeled Sales budget. Tab 3 in your spreadsheet should be labeled Production Budget. Etc. ALL formulas, references are required. Links between budgets are also required. The following component budgets must be included: a. Sales budget (including Cash Collections) b. Production budget c. d. e. f. g. h. i. Direct Materials Purchases budget (including Cash Disbursements) Direct labor budget Overhead budget Selling and administrative expense budget Cash budget Budgeted income statement Budgeted balance sheet