Master budgets using excel to prepare an operating budget (manufacturing company) thunder creek company is preparing budgets for the first quarter of 2018. all relevant information is presented on the excel template. use the blue shaded areas on the enter-answers tab for inputs. always use cell references and formulas where appropriate to receive full credit. enter all amounts as positive values. do not use a minus sign or parentheses for any values. requirements possible points 1. prepare a sales budget. 18 2. prepare a production budget. 26 3. prepare a direct materials budget. 39 4. prepare a direct labor budget. 20 5. prepare a manufacturing overhead budget. 33 6. prepare a cost of goods sold budget. 20 7. prepare a selling and administrative expense budget. 20 excel skills             1. create formulas with cell references.  

Variable rate cd’s = $90 treasury bills = $150 discount loans = $20 treasury notes = $100 fixed rate cds = $160 money market deposit accts. = $140 savings deposits = $90 fed funds borrowing = $40 variable rate mortgage loans $140 demand deposits = $40 primary reserves = $50 fixed rate loans = $210 fed funds lending = $50 equity capital = $120 a. develop a balance sheet from the above data. be sure to divide your balance sheet into rate-sensitive assets and liabilities as we did in class and in the examples. b. perform a standard gap analysis and a duration analysis using the above data if you have a 1.15% decrease in interest rates and an average duration of assets of 5.4 years and an average duration of liabilities of 3.8 years. c. indicate if this bank will remain solvent after the valuation changes. if so, indicate the new level of equity capital after the valuation changes. if not, indicate the amount of the shortage in equity capital.